ビニールハウスの環境モニタリング装置を自作した話 SwitchBotでGoogleスプレッドシートにモニタリング結果を記録してみた編
前回の記事でリアルタイムの数値はSwitchBotのアプリで確認できる環境が出来上がった。SwitchBotアプリで現在値をリアルタイム確認と過去のデータをグラフを確認することはできるようになりました。
ネットの海にはSwitchBotのデータを記録し活用している方の情報も見られるので、ちょっと私も進めてみようと思います。
なお、私の実例の紹介なので、試してみたい方はご自分の責任でお試しください。
使用するもの
SwitchBotAPI v1.1
Googleスプレッドシート
Google Apps Script（GAS）
準備
注意
ここで取得するトークン、シークレットキー、スプレッドシートIDは他人に共有しないでください。これを知ることでSwitchBot、スプレッドシートにアクセスできますので取り扱いにはご注意。
SwitchBotAPI v1.1
SwitchBotオフィシャルサイト サポートページ トークンの取得方法
こちらを参考にトークンとシークレットキーを取得してください。
SwitchBot センサー類のID
SwitchBotアプリからデバイスIDを確認します。
SwitchBotアプリを開き、対象のセンサーを選択します。
画面右上の設定アイコンをタップします。
メニュー内の「デバイス情報」を選択します。
表示される「BLE MAC」の値がデバイスIDです。この値からコロン（:）を除いたものがデバイスIDとなります。例えば、BLE MACが「AA:BB:CC:DD:EE:FF」の場合、デバイスIDは「AABBCCDDEEFF」となります。
全てのセンサーに関して確認してください
Googleスプレッドシート
スプレッドシートを新規作成します。
新たに作成したスプレッドシートのURLの太文字の部分の文字列
https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit?gid=SHEET_ID#gid=SHEET_ID
この文字列がスプレッドシートのIDとなります。
Google Apps Script（GAS）
Googleスプレッドシートの対象ファイルを開く。
メニュー「拡張機能」→「Apps Script」を選択。
スクリプトエディタが開きます（これがGASの編集画面です）
今回使用するコード
/**
* SwitchBotデータ記録および管理スクリプト
* ======================================
* このスクリプトは、SwitchBotのデバイスからデータを取得し、Googleスプレッドシートに記録・管理するためのものです。
*
* 必要な事前準備:
* ----------------
* 1. スクリプトプロパティの設定:
* - 以下のプロパティを設定してください（[左: プロパティ名, 右: 値]）。
* API_TOKEN : SwitchBot APIトークン
* SECRET_KEY : SwitchBot APIシークレットキー
* SPREADSHEET_ID : スプレッドシートのID
* HUB2_ID : ハブ2のデバイスID
* SENSOR1_ID : 温湿度計1のデバイスID
* SENSOR2_ID : 温湿度計2のデバイスID
* SENSOR3_ID : 温湿度計3のデバイスID
* SENSOR4_ID : 温湿度計4のデバイスID
* SENSOR5_ID : 温湿度計5のデバイスID
* CO2_SENSOR_ID : CO2センサーのデバイスID
*
* 2. スプレッドシートの初期準備:
* - 新しいスプレッドシートを作成し、そのIDをスクリプトプロパティに設定。
* - ヘッダーはスクリプトによって自動的に設定されます。
*
* 3. トリガーの設定:
* - 定期的にデータを記録するためのトリガー:
* - `recordData`: デフォルトでは5分ごとに実行。
* - 注意: 1分周期で設定した場合、デバイス数に応じてAPIの制限に達する可能性があります。
* 例: 7台のデバイスで1分周期の場合、7台 × 60分 × 24時間 = 10,080回/日
* → SwitchBot APIの1日1万回制限を超えるリスクがあります。
* - 推奨: 制限を避けるため、デバイス数や周期を調整してください。
*
* - 月次データ移動処理のトリガー:
* - `manageData`: 毎日深夜（例: 02:00）に実行。
*
* 使用方法:
* --------
* 1. デバイスデータの記録:
* - `recordData` 関数を実行すると、SwitchBot APIからデータを取得してスプレッドシートに記録します。
*
* 2. 古いデータの管理:
* - `manageData` 関数を実行すると、24時間以上前のデータを別タブ（当月タブ）に移動します。
* - 月初には新しいタブ（`yyyy_MM`形式）を作成し、そこに古いデータを記録します。
*/
function getConfig() {
// デバイス名、プロパティキー名、計測項目を一元管理
const deviceInfo = [
{ name: "ハブ2", key: "HUB2_ID", metrics: ["温度", "湿度"] },
{ name: "温湿度計1", key: "SENSOR1_ID", metrics: ["温度", "湿度", "バッテリー"] },
{ name: "温湿度計2", key: "SENSOR2_ID", metrics: ["温度", "湿度", "バッテリー"] },
{ name: "温湿度計3", key: "SENSOR3_ID", metrics: ["温度", "湿度", "バッテリー"] },
{ name: "温湿度計4", key: "SENSOR4_ID", metrics: ["温度", "湿度", "バッテリー"] },
{ name: "温湿度計5", key: "SENSOR5_ID", metrics: ["温度", "湿度", "バッテリー"] },
{ name: "CO2センサー", key: "CO2_SENSOR_ID", metrics: ["温度", "湿度", "CO2"] }
];
// デバイスキー名のマッピング
const deviceKeys = Object.fromEntries(deviceInfo.map(device => [device.name, device.key]));
// デバイス名と計測項目のマッピング
const devices = Object.fromEntries(deviceInfo.map(device => [device.name, device.metrics]));
// ヘッダーの生成
const headers = ["日時"];
deviceInfo.forEach(device => {
device.metrics.forEach(metric => headers.push(`${device.name}${metric}`));
});
return { deviceKeys, devices, headers };
}
function manageData() {
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const currentSheet = spreadsheet.getActiveSheet();
const now = new Date();
const oneDayAgo = new Date(now.getTime() - 24 * 60 * 60 * 1000);
const currentMonth = Utilities.formatDate(now, Session.getScriptTimeZone(), "yyyy_MM");
const config = getConfig();
let monthlySheet = spreadsheet.getSheetByName(currentMonth);
if (!monthlySheet) {
monthlySheet = spreadsheet.insertSheet(currentMonth);
monthlySheet.appendRow(config.headers); // ヘッダーを設定
}
const sourceRange = currentSheet.getDataRange();
const sourceData = sourceRange.getValues();
const headers = sourceData[0];
const data = sourceData.slice(1);
const rowsToMove = [];
const rowsToKeep = [];
data.forEach(row => {
const rowDate = new Date(row[0]);
if (rowDate < oneDayAgo) {
rowsToMove.push(row);
} else {
rowsToKeep.push(row);
}
});
if (rowsToMove.length > 0) {
const targetRange = monthlySheet.getRange(monthlySheet.getLastRow() + 1, 1, rowsToMove.length, headers.length);
targetRange.setValues(rowsToMove);
}
currentSheet.clear();
currentSheet.getRange(1, 1, 1, headers.length).setValues([headers]);
if (rowsToKeep.length > 0) {
currentSheet.getRange(2, 1, rowsToKeep.length, headers.length).setValues(rowsToKeep);
}
}
function recordData() {
const scriptProperties = PropertiesService.getScriptProperties();
const API_TOKEN = scriptProperties.getProperty('API_TOKEN');
const SECRET_KEY = scriptProperties.getProperty('SECRET_KEY');
const SPREADSHEET_ID = scriptProperties.getProperty('SPREADSHEET_ID');
const config = getConfig();
const sheet = SpreadsheetApp.openById(SPREADSHEET_ID).getActiveSheet();
const firstRow = sheet.getLastRow() > 0
? sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0]
: [];
if (!firstRow.length || firstRow[0] !== "日時") {
sheet.appendRow(config.headers); // ヘッダーを設定
}
const timestamp = new Date();
const rowData = [timestamp];
for (const [deviceName, metrics] of Object.entries(config.devices)) {
const deviceId = scriptProperties.getProperty(config.deviceKeys[deviceName]);
const data = fetchSwitchBotData(API_TOKEN, SECRET_KEY, deviceId);
if (data) {
metrics.forEach(metric => {
switch (metric) {
case "温度":
rowData.push(data.temperature || "");
break;
case "湿度":
rowData.push(data.humidity || "");
break;
case "バッテリー":
rowData.push(data.battery || "");
break;
case "CO2":
rowData.push(data.CO2 || "");
break;
default:
rowData.push("");
}
});
} else {
metrics.forEach(() => rowData.push("")); // データがない場合は空欄
}
}
sheet.appendRow(rowData);
}
function fetchSwitchBotData(API_TOKEN, SECRET_KEY, DEVICE_ID) {
const url = `https://api.switch-bot.com/v1.1/devices/${DEVICE_ID}/status`;
const t = Date.now().toString();
const nonce = Utilities.getUuid();
const stringToSign = `${API_TOKEN}${t}${nonce}`;
const signature = Utilities.computeHmacSha256Signature(stringToSign, SECRET_KEY);
const signBase64 = Utilities.base64Encode(signature);
const headers = {
'Authorization': API_TOKEN,
'sign': signBase64,
't': t,
'nonce': nonce,
'Content-Type': 'application/json; charset=utf8',
};
const options = { method: 'get', headers: headers };
try {
const response = UrlFetchApp.fetch(url, options);
const result = JSON.parse(response.getContentText());
return result.body;
} catch (e) {
Logger.log('エラー: ' + e);
return null;
}
}
GASのスクリプトエディタにこのコードをコピーしてください。
コード先頭のマニュアルの確認部分
スクリプトプロパティの設定
GASのプロジェクトダッシュボードから設定する方法
Google Apps Script エディタを開く。
メニュー（画面左側）から「プロジェクトの設定」を選択。
下にスクロールし、スクリプトプロパティの項目へ。
「新しいプロパティを追加」ボタンをクリック。
キー（プロパティ名）と値を入力。
保存ボタンをクリック。
トリガーの設定
GASのプロジェクトダッシュボードから設定する方法
Google Apps Script エディタを開く。
メニュー（画面左側）から「トリガー」を選択。
右下の「+トリガーの追加」を選択。
マニュアルに従って関数と間隔を選択。（イベントソースを時間主導型、それぞれ分ベース、日付ベースで設定）
保存ボタンをクリック。
承認
トリガーの設定の画面か初回実行時だと思うのですが、承認が必要になるのでここのページを参照して承認してください。
コードの作成に関して
今回のコードの作成に関しては全てChatGPTで作成しています。全くコードが書けない人間が指示だけで動作するコードが作れる時代になったのはすごいと思います。
ただ、いくつかハマったポイントがあるので書いておきたいと思います。
初心者はプロンプトにこだわるよりも動くものをブラッシュアップした方が早い。これは一日くらい無駄にした感じでした。あきらめて、少しづつ改善させたら2～3時間で想定したものが出来上がりました。恐らく、コードが書ける方がプロンプトに精通するとできることは段違いなのでしょうが、私にはそんな技量がないので、一つづつ修正した方が間違いがありませんでした。
やはりコードがわからないと、問題点に気づけない。CO2センサーのCO2濃度だけがデータを読めなかったのですが、co2では無く、CO2としなくてはいけなかったなどは初心者には分かりません。
今後に関して
理想としてはダッシュボードにリアルタイムでセンサーの状態を見れるようにしたいと思います。また、他のセンサーも併せて使いたいので、スプレッドシートに書き出すのは必要でした。今回は以上です。
SwitchBot公式ページ
クーポン「AF-4903」で5％引き（アフィリエイトクーポンになりますのでご協力お願いします）
割引の詳しい仕組みに関してはこちらのページを参照ください。