Node-RED 連結MQTT 資料上傳至 Google Sheet

 Node-RED 連結MQTT 資料上傳至 Google Sheet 

1) PC日期時間與雲端日期時間 同步

2) 注意工作表名稱

3) 設定 連結google服務 (Google Drive API , Google Sheet API) 並取得 憑證 與 Google Sheet ID

4)將Google Sheet 共享給 Google Cloud 裡面設定的帳號

     例如 nodered@nodered-12345.iam.gserviceaccount.com

參考來源 https://hackmd.io/@LHB-0222/NodeRED-GSeet

Node-Red 程式

[{"id":"bb9215b2.f64bc8","type":"mqtt in","z":"86531bb5.4a6e48","name":"","topic":"alex9ufo/inTopic/led/led_event","qos":"0","datatype":"auto","broker":"bf6ac300b7c58534","nl":false,"rap":false,"x":240,"y":160,"wires":[["63820adec0e5810a","b3302ec331847f4f"]]},{"id":"820c6bfe08029529","type":"ui_button","z":"86531bb5.4a6e48","name":"","group":"ac0f1141.eb50e","order":0,"width":"4","height":"2","passthru":false,"label":"LED 開","tooltip":"","color":"white","bgcolor":"","icon":"fa-circle","payload":"ON","payloadType":"str","topic":"","x":180,"y":280,"wires":[["f41551b6938d98e6","82bb15e145c89df0","a2b076a7da2e925b"]]},{"id":"5e881875b6a035d5","type":"ui_button","z":"86531bb5.4a6e48","name":"","group":"ac0f1141.eb50e","order":0,"width":"4","height":"2","passthru":false,"label":"LED 關","tooltip":"","color":"black","bgcolor":"","icon":"fa-circle-o","payload":"OFF","payloadType":"str","topic":"","x":180,"y":320,"wires":[["f41551b6938d98e6","82bb15e145c89df0","a2b076a7da2e925b"]]},{"id":"f41551b6938d98e6","type":"mqtt out","z":"86531bb5.4a6e48","name":"","topic":"alex9ufo/inTopic/led/led_event","qos":"1","retain":"true","respTopic":"","contentType":"","userProps":"","correl":"","expiry":"","broker":"841df58d.ee5e98","x":650,"y":360,"wires":[]},{"id":"8954c90b26d9c2fd","type":"ui_button","z":"86531bb5.4a6e48","name":"","group":"ac0f1141.eb50e","order":0,"width":"4","height":"2","passthru":false,"label":"LED 開關反向","tooltip":"","color":"blue","bgcolor":"","icon":"fa-circle-o","payload":"TOGGLE","payloadType":"str","topic":"","x":200,"y":360,"wires":[["f41551b6938d98e6","82bb15e145c89df0","a2b076a7da2e925b"]]},{"id":"996349129428ebe5","type":"ui_button","z":"86531bb5.4a6e48","name":"","group":"ac0f1141.eb50e","order":0,"width":"4","height":"2","passthru":false,"label":"LED 開5秒鐘","tooltip":"","color":"purple","bgcolor":"","icon":"fa-circle-o","payload":"TIMER","payloadType":"str","topic":"","x":190,"y":440,"wires":[["f41551b6938d98e6","82bb15e145c89df0","a2b076a7da2e925b"]]},{"id":"c6377ac5e1e0b3be","type":"comment","z":"86531bb5.4a6e48","name":"Node-Red publish 到 HiveMQ Broker  , ESP32訂閱","info":"將 alex9ufo/inTopic 到(publish)HiveMQ Broker \n給 Arduino 訂閱(Subscribe)","x":310,"y":520,"wires":[]},{"id":"456fc06adf6e59fa","type":"ui_button","z":"86531bb5.4a6e48","name":"","group":"ac0f1141.eb50e","order":0,"width":"4","height":"2","passthru":false,"label":"LED 閃爍","tooltip":"","color":"yellow","bgcolor":"","icon":"fa-circle-o","payload":"FLASH","payloadType":"str","topic":"","x":180,"y":400,"wires":[["f41551b6938d98e6","82bb15e145c89df0","a2b076a7da2e925b"]]},{"id":"82bb15e145c89df0","type":"ui_audio","z":"86531bb5.4a6e48","name":"","group":"ac0f1141.eb50e","voice":"zh-TW","always":true,"x":420,"y":440,"wires":[]},{"id":"a2b076a7da2e925b","type":"ui_text","z":"86531bb5.4a6e48","group":"ac0f1141.eb50e","order":0,"width":0,"height":0,"name":"","label":"Node-RED發行到MQTT的資料","format":"{{msg.payload}}","layout":"row-center","x":490,"y":280,"wires":[]},{"id":"63820adec0e5810a","type":"debug","z":"86531bb5.4a6e48","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":630,"y":100,"wires":[]},{"id":"b3302ec331847f4f","type":"GSheet","z":"86531bb5.4a6e48","creds":"4873121660499de4","method":"append","action":"","sheet":"1F2UjZXrTM12o831R1fNv6XX1cvpXKS9XCYLD4CT50Nk1lo","cells":"工作表1!A1:A1","flatten":false,"name":"","x":550,"y":180,"wires":[["a6276ac99fdeb685"]]},{"id":"a6276ac99fdeb685","type":"debug","z":"86531bb5.4a6e48","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"false","statusVal":"","statusType":"auto","x":760,"y":180,"wires":[]},{"id":"ab82291c80c0269c","type":"inject","z":"86531bb5.4a6e48","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":240,"y":100,"wires":[["b3302ec331847f4f","63820adec0e5810a"]]},{"id":"bf6ac300b7c58534","type":"mqtt-broker","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"protocolVersion":"4","keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","birthMsg":{},"closeTopic":"","closeQos":"0","closePayload":"","closeMsg":{},"willTopic":"","willQos":"0","willPayload":"","willMsg":{},"sessionExpiry":""},{"id":"ac0f1141.eb50e","type":"ui_group","name":"LED讀取","tab":"e03ab9d0.1a8f08","order":1,"disp":true,"width":"15","collapse":false},{"id":"841df58d.ee5e98","type":"mqtt-broker","name":"","broker":"broker.mqtt-dashboard.com","port":"1883","clientid":"","usetls":false,"compatmode":false,"keepalive":"15","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"4873121660499de4","type":"gauth"},{"id":"e03ab9d0.1a8f08","type":"ui_tab","name":"LED","icon":"dashboard","order":1,"disabled":false,"hidden":false}]