Node-REDのメモ 応用編 BME280＋Google spreadsheet
Node-REDでBME280で測定したデータをGoogle spreadsheet に記録するときの設定手順のメモ。
鶏頭で忘れっぽいのでメモ。
それぞれの基本は、Node-REDのHowTo(その1) ～ (その4)参照
具体的な手順はこちらを参考にしてくだされ。 (コードの実装の手前まで)
ただし、有効にするAPIは「Google Drive API」ではなく、「Google Sheets API」なので、注意！！
var date = new Date();
msg.payload.epoch = date.getTime();
// msg.payload.date = date.toString();
msg.payload.date = '=indirect("R[0]C[-1]", false) / (1000*60*60*24) + (9/24) + DATE(1970, 1, 1)';
msg.payload = [msg.payload];
return msg;
トリガとなるノードからトリガが入力されれば、温度等を測定、スプレッドシートに送信される。
スプレッドシートを確認すれば、そのデータが記録されているハズである。
このとき、Google Sheetsノードが"Missing VISEO Bot Maker key - Read the documentation."というメッセージを出力するが、これは単なるワーニングなので、気にしなくて良い(らしい)。
秘密鍵の内容は入っていないので、別途入力すること
スプレッドシートのIDも削除してあるので、作成したスプレッドシートのIDを入力すること
[
{
"id": "1a4f21c6.53e09e",
"type": "tab",
"label": "BME280+spreadsheet",
"disabled": false,
"info": ""
},
{
"id": "b4e73f33.99aec8",
"type": "Bme280",
"z": "1a4f21c6.53e09e",
"name": "",
"bus": "1",
"address": "0x76",
"topic": "bme280",
"extra": false,
"x": 220,
"y": 180,
"wires": [
[
"a54ed898.2a114"
]
]
},
{
"id": "b74c37d3.63a48",
"type": "inject",
"z": "1a4f21c6.53e09e",
"name": "",
"topic": "",
"payload": "true",
"payloadType": "bool",
"repeat": "",
"crontab": "",
"once": false,
"onceDelay": 0.1,
"x": 90,
"y": 180,
"wires": [
[
"b4e73f33.99aec8"
]
]
},
{
"id": "a54ed898.2a114",
"type": "function",
"z": "1a4f21c6.53e09e",
"name": "データ整形",
"func": "var date = new Date();\nmsg.payload.epoch = date.getTime();\n// msg.payload.date = date.toString();\n// msg.payload.date = '=indirect(\"R[0]C[-1]\",false)/1000/60/60/24 + 9/24 + DATE(1970,1,1)';\nmsg.payload.date = '=indirect(\"R[0]C[-1]\", false) / (1000*60*60*24) + (9 / 24) + DATE(1970, 1, 1)';\n\nmsg.payload = [msg.payload];\n\nreturn msg;",
"outputs": 1,
"noerr": 0,
"x": 390,
"y": 180,
"wires": [
[
"6a4f8cc4.882e54",
"adad9a.88697a68"
]
]
},
{
"id": "6a4f8cc4.882e54",
"type": "google-spreadsheet",
"z": "1a4f21c6.53e09e",
"name": "",
"auth": "9550e71e.d49b88",
"sheet": "ナイショ 自分のシートのIDを書いてね",
"range": "BME280!a:z",
"method": "append",
"direction": "line",
"action": "set",
"clear": false,
"line": false,
"column": false,
"fields": "select",
"save": "_sheet",
"selfields": [
"epoch",
"date",
"temperature_C",
"humidity",
"pressure_hPa"
],
"cell_l": "",
"cell_c": "",
"input": "payload",
"output": "__output",
"saveType": "msg",
"inputType": "msg",
"outputType": "msg",
"sheetType": "str",
"rangeType": "str",
"cell_lType": "str",
"cell_cType": "str",
"x": 620,
"y": 180,
"wires": [
[
"93209cb1.cd86"
],
[
"9a8717a8.88bae8"
]
]
},
{
"id": "93209cb1.cd86",
"type": "debug",
"z": "1a4f21c6.53e09e",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"x": 830,
"y": 180,
"wires": []
},
{
"id": "9a8717a8.88bae8",
"type": "debug",
"z": "1a4f21c6.53e09e",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "true",
"targetType": "full",
"x": 830,
"y": 220,
"wires": []
},
{
"id": "adad9a.88697a68",
"type": "debug",
"z": "1a4f21c6.53e09e",
"name": "",
"active": true,
"tosidebar": true,
"console": false,
"tostatus": false,
"complete": "false",
"x": 850,
"y": 80,
"wires": []
},
{
"id": "9550e71e.d49b88",
"type": "google-service-account",
"z": "",
"name": "edior1@myproject3",
"scope": [
"https://www.googleapis.com/auth/spreadsheets"
],
"way": "json",
"check_dialogflow": "",
"check_speech": ""
}
]