ビニールハウスの環境モニタリング装置を自作した話 SwitchBotで記録したデータを使ってAmzon Echo Showをダッシュボード的に使いたい編
前回の記事では、SwitchBotでデータを定期的にGoogleスプレッドシートに記録するところまで進めましたが、今回はそのデータを活用したいと思い、とりあえず走りだしてみました。
今回、Amzon Echo Showを表示デバイスに使用してみようと思ったのは、我が家ではEcho ShowがただのEchoデバイスとしてしか使われておらず、表示機能を何かに使えればと思ったからです。
Echo Showで表示させるデータ
一番に思いつくのは画像ファイル。と言うか、他の方法は簡単には思いつかない。と言うかアレクサスキルを作ってみようとか出来る訳が無い。そもそも、これをあまり使っていないのも、アレクサスキルがあっても使い道がないからです。
結局よくわからないのですが、あまり面倒くさく無さそうな画像ファイルを使うことにします。
雑なフローチャート
[画像生成(GAS)]
↓
[GoogleDriveで共有]
↓
↓ [画像削除][同名新しい画像生成]
↓
[ローカルドライブに保存]
↓
↓ [疑似上書き保存]
↓
[AmazonPhotoで共有]
↓
↓ [再度AmazonPhotoに同期]
↓
↓ [フォトフレーム再読み込み]
↓
[Echo Show フォトフレームに表示]
ChatGPTの力で作った今回のコード
/**
* SwitchBotデータ記録および管理スクリプト
* ======================================
* このスクリプトは、SwitchBotのデバイスからデータを取得し、Googleスプレッドシートに記録・管理し、
* 必要に応じて特定範囲を画像化してGoogle Driveに保存するものです。
*
* 必要な事前準備:
* ----------------
* 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
* LEAK_SENSOR_ID : 水漏れセンサーのデバイスID
* WATERPROOF_THERMO_ID: 防水温度計のデバイスID
* SHEET_NAME : 画像化する元シートの名前
* FOLDER_ID : 画像を保存するGoogle DriveフォルダのID
*
* 2. スプレッドシートの初期準備:
* - 新しいスプレッドシートを作成し、そのIDをスクリプトプロパティに設定。
* - ヘッダーはスクリプトによって自動的に設定されます。
*
* 3. トリガーの設定:
* - 定期的にデータを記録するためのトリガー:
* - `recordData`: デフォルトでは5分ごとに実行。
* - 注意: デバイス数に応じてSwitchBot APIの制限（1日1万回）を超えないよう調整してください。
*
* - 月次データ移動処理のトリガー:
* - `manageData`: 毎日深夜（例: 02:00）に実行。
*
* - データ範囲の画像化処理:
* - `sheetToImage`: 必要に応じて手動で実行。
*
* 使用方法:
* --------
* 1. デバイスデータの記録:
* - `recordData` 関数を実行すると、SwitchBot APIからデータを取得してスプレッドシートに記録します。
*
* 2. 古いデータの管理:
* - `manageData` 関数を実行すると、24時間以上前のデータを別タブ（当月タブ）に移動します。
* - 月初には新しいタブ（`yyyy_MM`形式）を作成し、そこに古いデータを記録します。
*
* 3. データ範囲の画像化:
* - `sheetToImage` 関数を実行すると、指定した範囲（例: "A1:E10"）を画像化してGoogle Driveに保存します。
* - 同名ファイルが既に存在する場合、上書き保存されます。
*
* 注意事項:
* --------
* 1. **APIトークンとシークレットキーの管理**
* - セキュリティ上の理由から、APIトークンやシークレットキーは第三者に共有しないでください。
*
* 2. **画像化処理に必要なプロパティ**
* - `SHEET_NAME` と `FOLDER_ID` が正しく設定されていない場合、画像化処理は失敗します。
*
* 3. **デバイスの追加や変更**
* - 新しいデバイスを追加する場合は、`getConfig`関数内でデバイス情報を設定してください。
*/
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"] },
{ name: "水漏れセンサー", key: "LEAK_SENSOR_ID", metrics: ["ステータス", "バッテリー"] },
{ name: "防水温度計", key: "WATERPROOF_THERMO_ID", metrics: ["温度", "湿度", "バッテリー"] }
];
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 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 SHEET_NAME = scriptProperties.getProperty('SHEET_NAME'); // 画像化元のシート名
const FOLDER_ID = scriptProperties.getProperty('FOLDER_ID'); // 保存先フォルダID
if (!SHEET_NAME || !FOLDER_ID) {
throw new Error('スクリプトプロパティに "SHEET_NAME" および "FOLDER_ID" を設定してください。');
}
const spreadsheet = SpreadsheetApp.openById(SPREADSHEET_ID);
const targetSheet = spreadsheet.getSheets()[0]; // 一番左のシート
const sourceSheet = spreadsheet.getSheetByName(SHEET_NAME);
if (!sourceSheet) {
throw new Error(`スクリプトプロパティで指定されたシート "${SHEET_NAME}" が見つかりません。`);
}
const config = getConfig();
// ヘッダーがない場合、ヘッダーを追加
const firstRow = targetSheet.getLastRow() > 0
? targetSheet.getRange(1, 1, 1, targetSheet.getLastColumn()).getValues()[0]
: [];
if (!firstRow.length || firstRow[0] !== "日時") {
targetSheet.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;
case "ステータス":
rowData.push(data.status || "");
break;
default:
rowData.push("");
}
});
} else {
metrics.forEach(() => rowData.push(""));
}
}
targetSheet.appendRow(rowData);
// 画像化処理（元データはスクリプトプロパティ指定のシート）
sheetToImage(SPREADSHEET_ID, SHEET_NAME, "A1:E10", FOLDER_ID);
}
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 sourceData = currentSheet.getDataRange().getValues();
const headers = sourceData[0]; // ヘッダー行
const data = sourceData.slice(1); // データ部分
// データを移動対象と保持対象に分割
const [rowsToMove, rowsToKeep] = data.reduce(
([move, keep], row) => {
const rowDate = new Date(row[0]);
if (rowDate < oneDayAgo) move.push(row);
else keep.push(row);
return [move, keep];
},
[[], []]
);
if (rowsToMove.length > 0) {
// 移動先シートにデータを追加
const targetRange = monthlySheet.getRange(
monthlySheet.getLastRow() + 1,
1,
rowsToMove.length,
headers.length
);
targetRange.setValues(rowsToMove);
}
// 現在のシートをクリアして残ったデータを再設定
currentSheet.clearContents();
currentSheet.getRange(1, 1, 1, headers.length).setValues([headers]); // ヘッダーを再設定
if (rowsToKeep.length > 0) {
currentSheet.getRange(2, 1, rowsToKeep.length, headers.length).setValues(rowsToKeep);
}
}
function sheetToImage(spreadsheetId, sheetName, range, folderId) {
const spreadsheet = SpreadsheetApp.openById(spreadsheetId);
const sheet = spreadsheet.getSheetByName(sheetName);
const rangeData = sheet.getRange(range);
// 表示形式のデータと背景色を取得
const values = rangeData.getDisplayValues();
const backgrounds = rangeData.getBackgrounds();
// 列幅設定
const colWidth = [260, 180, 120, 120, 120];
const rowHeight = 40;
const canvasWidth = colWidth.reduce((sum, width) => sum + width, 0);
const canvasHeight = rowHeight * values.length;
// HTMLテンプレートでキャンバスを作成し、画像を生成
const htmlContent = `
<html>
<body>
<div>
<h3>画像を生成中...</h3>
<canvas id="canvas" width="${canvasWidth}" height="${canvasHeight}" style="border:1px solid black;"></canvas>
</div>
<script>
var data = ${JSON.stringify(values)};
var backgrounds = ${JSON.stringify(backgrounds)};
var colWidth = ${JSON.stringify(colWidth)};
var rowHeight = ${rowHeight};
var canvas = document.getElementById("canvas");
var ctx = canvas.getContext("2d");
// 背景白
ctx.fillStyle = "white";
ctx.fillRect(0, 0, canvas.width, canvas.height);
// 描画処理
ctx.font = "bold 20px Arial"; // フォントサイズと太さを設定
ctx.textBaseline = "middle";
for (var i = 0; i < data.length; i++) {
for (var j = 0; j < data[i].length; j++) {
var x = colWidth.slice(0, j).reduce((a, b) => a + b, 0);
var y = i * rowHeight;
// 背景色
ctx.fillStyle = backgrounds[i][j] || "white";
ctx.fillRect(x, y, colWidth[j], rowHeight);
// 枠線
ctx.strokeStyle = "black";
ctx.strokeRect(x, y, colWidth[j], rowHeight);
// テキスト
ctx.fillStyle = "black";
ctx.fillText(data[i][j], x + 10, y + rowHeight / 2);
}
}
// 画像データを生成
var dataUrl = canvas.toDataURL();
console.log("生成されたデータURL: ", dataUrl);
if (dataUrl && dataUrl.startsWith("data:image/png;base64,")) {
google.script.run
.withSuccessHandler(function() {
console.log("画像が正常に保存されました。");
document.body.innerHTML = "<h3>画像保存が完了しました。</h3>";
})
.withFailureHandler(function(err) {
console.error("画像保存エラー: ", err);
document.body.innerHTML = "<h3 style='color:red;'>エラーが発生しました。</h3><p>" + err + "</p>";
})
.saveImage(dataUrl, "${folderId}");
} else {
console.error("データURLが無効です: ", dataUrl);
document.body.innerHTML = "<h3 style='color:red;'>画像データが無効です。</h3>";
}
</script>
</body>
</html>
`;
// サイドバーとして表示
const htmlOutput = HtmlService.createHtmlOutput(htmlContent)
.setWidth(400)
.setHeight(300);
SpreadsheetApp.getUi().showSidebar(htmlOutput);
}
function saveImage(dataUrl, folderId) {
const fileName = 'google to amazon.png';
try {
Logger.log("=== 画像保存処理開始 ===");
Logger.log(`フォルダID: ${folderId}`);
Logger.log(`保存するファイル名: ${fileName}`);
// フォルダ取得
const folder = DriveApp.getFolderById(folderId);
if (!folder) {
throw new Error(`フォルダが見つかりません: ${folderId}`);
}
Logger.log(`フォルダ取得成功: ${folder.getName()}`);
// 同名ファイルを検索して削除
const fileDeleted = deleteExistingFile(folder, fileName);
if (fileDeleted) {
Logger.log("同名ファイルを削除しました。削除の反映を待機中...");
waitForDeletion(folder, fileName);
} else {
Logger.log("同名ファイルは見つかりませんでした。");
}
// 削除確認後の追加待機時間
const additionalWaitTime = 20000; // 20秒待機
Logger.log(`削除確認後に追加で ${additionalWaitTime / 1000} 秒待機します...`);
Utilities.sleep(additionalWaitTime);
// データURLをデコードしてファイル作成
Logger.log("データURLをデコードしてファイル作成開始...");
const base64Data = dataUrl.replace(/^data:image\/png;base64,/, '');
const blob = Utilities.newBlob(Utilities.base64Decode(base64Data), 'image/png', fileName);
const newFile = folder.createFile(blob);
Logger.log(`新しいファイルが作成されました: ${newFile.getName()}`);
Logger.log("=== 画像保存処理完了 ===");
} catch (error) {
Logger.log(`エラー発生: ${error.message}`);
Logger.log(`スタックトレース: ${error.stack}`);
throw error;
}
}
/**
* 同名ファイルを削除
* @param {GoogleAppsScript.Drive.Folder} folder - 対象フォルダ
* @param {string} fileName - 削除するファイル名
* @return {boolean} - 削除した場合は true、それ以外は false
*/
function deleteExistingFile(folder, fileName) {
const existingFiles = folder.getFilesByName(fileName);
let fileDeleted = false;
while (existingFiles.hasNext()) {
const file = existingFiles.next();
Logger.log(`削除対象ファイル: ${file.getName()} (ID: ${file.getId()})`);
file.setTrashed(true); // ゴミ箱に移動
fileDeleted = true;
}
return fileDeleted;
}
/**
* 削除の反映を確認
* @param {GoogleAppsScript.Drive.Folder} folder - 対象フォルダ
* @param {string} fileName - 確認するファイル名
*/
function waitForDeletion(folder, fileName) {
const maxWaitTime = 60000; // 最大待機時間 60秒
const interval = 2000; // 確認間隔 2秒
let elapsedTime = 0;
while (elapsedTime < maxWaitTime) {
const remainingFiles = folder.getFilesByName(fileName);
if (!remainingFiles.hasNext()) {
Logger.log("削除が確認されました。次の処理に進みます。");
return;
}
Logger.log("削除待機中... 現在のファイルリスト:");
while (remainingFiles.hasNext()) {
const file = remainingFiles.next();
Logger.log(` - ${file.getName()} (ID: ${file.getId()})`);
}
Utilities.sleep(interval);
elapsedTime += interval;
}
Logger.log("警告: 削除の反映を確認できないまま処理を続行します。");
}
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',
};
try {
const response = UrlFetchApp.fetch(url, { method: 'get', headers: headers });
const result = JSON.parse(response.getContentText());
return result.body || null;
} catch (e) {
Logger.log(`Error fetching data for device ${DEVICE_ID}: ${e.message}`);
return null;
}
}
初期設定
フォルダの設定
GoogleDriveとAmazonPhotoのPCアプリをインストール。
画像ファイルを保存するフォルダをGoogleDriveとAmazonPhotoで共有フォルダに設定。
保存される画像は「google to amazon.png」です。
Googleスプレッドシート
スプレッドシートの中の、今回画像出力に使うデータのあるシートのURLの太文字の部分の文字列
https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit?gid=SHEET_ID#gid=SHEET_ID
この文字列がシートのIDとなります。そして、
このように画像の基となる表を作成。時間と数値に関しては「=INDEX('○○○○○○'!A:A, COUNTA('○○○○○○'!A:A))」（○○○○○○は直近のデータが保存されるシート名）。 この関数はA列の一番下のデータ（最新のデータ）を引っ張ってくるものです。各データはB、C、Dと列を合わせて引っ張ってきてください。水漏れセンサーは「=IF(INDEX('○○○○○○'!V:V, COUNTA('○○○○○○'!V:V))=0, "水位低下", "正常")」と言う関数ですが、戻り数が0ならセンサー乾燥、1ならセンサー水濡れです。なので、0なら水位低下、1なら正常としています。また条件付き書式で水位低下なら赤塗、正常なら青塗としています。
GoogleDriveのフォルダID
IDを知りたいフォルダを開く。URLをチェックし
https://drive.google.com/drive/folders/FOLDER_ID
太文字の部分の文字列がフォルダIDとなります。
スクリプトプロパティの登録
前回より増えております。対応に合わせて修正してください。
EchoShowとAmazonPhotoの設定
AmazonPhotoで新規アルバムを作成。google to amazon.pngをアルバムに登録する。スマホのアレクサアプリからデバイス>使用するEchoshowを選択>設定の歯車ボタン>写真の表示>その他のコレクション>アルバムを選択して下さい。
その他
その他ユーザーマニュアル確認ください。
問題発生
さて、ここまで書いてなんなんですけど、今回は半分ほどの完成度と言うか、一番大事な作業ができていません。
①画像の削除～新しい同名の画像の作成の流れがまれに、うまくいかないことがある（(1)付の別名保存になる）。そうなるとフォトフレームの更新がうまくいかなくなる。AmazonPhotoのクラウド側の画像を消して、アルバムをもう一度設定しなおさなくてはいけなくなる。今はウエイトを入れているので、大体問題ないが、まれに起きるかもしれない。
②エディタ側の実行では問題ないのだが、トリガーでの定期実行では画像が生成されない。これは大問題。何をしているのか意味が解らない。マジ無理。一応、フォルダの共有設定なんかも緩めてみてるけど、何をどうすればいいのか分からない。AIの力では今のところにっちもさっちもいかない。
最後に
理想としては、5分おきの画像データの更新。現在値の画像と、過去24時間のデータのグラフを交互表示と言うのをやってみたかったのだが、どうにも行き詰まってしまった。一旦別の簡単なコードで画像処理をやって、こちらのデータを取り込む形に戻る方がAIでやるには近道な気がする。こまったもんです。