Zigbee2mqtt Node-red google sheet

 

Excel sheet is an old old tradition of engineering.

I have my zig2mqtt runs correctly on Pi3B, as well as mqtt and node-red.

But the document on how to write data to google sheet seems very unclear to me.

I did a search in managing palette and there is 2:



With article on the  viseo one:
https://www.rodened.com/posts/how-to-read-and-write-data-to-a-google-sheet-in-node-red-1/
I tried the node-red-contrib-viseo-google-spreadsheet

after correctly set up credential, share the google sheet, I was able to access the google sheet, for get cell..etc. But when I try to append a row I could not figure out what is the data format to feed in the node. I gave up after a night of testing.

then I found this blog:
https://grassrootengineer.medium.com/apply-node-red-to-google-sheet-e0c7db9ada8
 This lead me to this node:
node-red-contrib-google-sheets
still no much documentation, but I have better luck on this. This node will log the payload into the cell. and I figure out how to append a row.

[["A1","B1","C1"],["A2","B2","C2"]] in JSON would work..
it would put A1 in colonA first row, B1 in colon B first row...etc like this...
A1 B1 C1
A2 B2 C2
data:
[["A1","B1","C1"],["A2","B2","C2"],["A3","B3"]]
would insert like this:
A1 B1 C1
A2 B2 C2
A3 B3

int will work as well like this [1,2,3]

my test flow is like this

[{"id":"f7e38b71.5811d8","type":"tab","label":"form input to google spread sheet","disabled":false,"info":""},{"id":"399e0e50.282692","type":"inject","z":"f7e38b71.5811d8","name":"","props":[{"p":"payload"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"","payloadType":"date","x":100,"y":220,"wires":[["1a733443.eb22bc","8db28bfd.c35858"]]},{"id":"1a733443.eb22bc","type":"debug","z":"f7e38b71.5811d8","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":210,"y":420,"wires":[]},{"id":"8db28bfd.c35858","type":"GSheet","z":"f7e38b71.5811d8","creds":"d7a4b448.1de9f8","method":"append","action":"","sheet":"1oqNIdVm6L30xTf05RRpVg4Lu0cx2L_zQkn9UdnRF1_8","cells":"Sheet1","flatten":true,"name":"","x":410,"y":240,"wires":[["d8fc4d24.56c05"]]},{"id":"d8fc4d24.56c05","type":"debug","z":"f7e38b71.5811d8","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":610,"y":240,"wires":[]},{"id":"64a8051c.69c26c","type":"inject","z":"f7e38b71.5811d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[[\"A1\",\"B1\",\"C1\"],[\"A2\",\"B2\",\"C2\"],[\"A3\",\"B3\"]]","payloadType":"jsonata","x":280,"y":160,"wires":[["8db28bfd.c35858"]]},{"id":"94090ad1.2fed18","type":"comment","z":"f7e38b71.5811d8","name":"data fromat for Gsheet","info":"[[\"A1\",\"B1\",\"C1\"],[\"A2\",\"B2\",\"C2\"]] in JSON would work..\nit would put A1 in colonA first row, B1 in colon B first row...etc like this...\nA1\tB1\tC1\nA2\tB2\tC2\n\ndata:\n[[\"A1\",\"B1\",\"C1\"],[\"A2\",\"B2\",\"C2\"],[\"A3\",\"B3\"]]\nwould insert like this:\nA1\tB1\tC1\nA2\tB2\tC2\nA3\tB3\t\n\nint would do as well:\n[[1,2,3],[4,5,6]]\n1\t2\t3\n4\t5\t6","x":710,"y":120,"wires":[]},{"id":"946f87ae.182858","type":"inject","z":"f7e38b71.5811d8","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"","payload":"[[1,2,3],[4,5,6]]","payloadType":"jsonata","x":390,"y":460,"wires":[["8db28bfd.c35858"]]},{"id":"d7a4b448.1de9f8","type":"gauth"}]


example with zigbee switch



Json node is used to make string payload in JSON format.
function node is to take data into the format of gsheet.
[{"id":"7bc77ba3.6f29e4","type":"tab","label":"Zigbee switch01","disabled":false,"info":""},{"id":"d32fc894.3bba18","type":"mqtt in","z":"7bc77ba3.6f29e4","name":"","topic":"zigbee2mqtt/0x00158d00036137e9","qos":"2","datatype":"auto","broker":"f2a8ec88.a4f7","x":180,"y":60,"wires":[["6ac987f6.63efb8","b6a75b4d.3a0a88"]]},{"id":"6ac987f6.63efb8","type":"debug","z":"7bc77ba3.6f29e4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"payload","targetType":"msg","statusVal":"","statusType":"auto","x":500,"y":100,"wires":[]},{"id":"b6a75b4d.3a0a88","type":"json","z":"7bc77ba3.6f29e4","name":"","property":"payload","action":"obj","pretty":false,"x":370,"y":160,"wires":[["5292df0b.68cfe"]]},{"id":"f8e7ae74.97ccf","type":"GSheet","z":"7bc77ba3.6f29e4","creds":"d7a4b448.1de9f8","method":"append","action":"","sheet":"1oqNIdVm6L30xTf05RRpVg4Lu0cx2L_zQkn9UdnRF1_8","cells":"Mi switch01","flatten":false,"name":"switch append sheet2","x":420,"y":300,"wires":[["f9caa6ed.5ae128"]]},{"id":"f9caa6ed.5ae128","type":"debug","z":"7bc77ba3.6f29e4","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":610,"y":300,"wires":[]},{"id":"5292df0b.68cfe","type":"function","z":"7bc77ba3.6f29e4","name":"row for switch: date, click,volt,link","func":"\nvar nmsg={payload:[Date(),\nmsg.payload.action,\nmsg.payload.click,\nmsg.payload.duration,\nmsg.payload.voltage,\nmsg.payload.linkquality\n]};\nreturn nmsg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":212,"y":230,"wires":[["f8e7ae74.97ccf"]]},{"id":"df15c679.ef2068","type":"comment","z":"7bc77ba3.6f29e4","name":"Mi click botton behavious","info":"every hour, device will issue a message, even not pressed.\n{\"battery\":100,\"linkquality\":117,\"voltage\":3022}\n\nWhen pressed there will be 2 messages:\n{\"action\":\"single\",\"battery\":100,\"linkquality\":117,\"voltage\":3022}\n{\"battery\":100,\"click\":\"single\",\"linkquality\":117,\"voltage\":3022}\naction and click is the same when single or double.\nwhern long pressed, Action will be hold and click is long, when release after long pressed, action will be release, click is long_release. Both message contain a duration","x":150,"y":20,"wires":[]},{"id":"f2a8ec88.a4f7","type":"mqtt-broker","name":"Zigbee2mqtt","broker":"192.168.0.35","port":"1883","clientid":"","usetls":false,"compatmode":true,"keepalive":"60","cleansession":true,"birthTopic":"","birthQos":"0","birthPayload":"","closeTopic":"","closeQos":"0","closePayload":"","willTopic":"","willQos":"0","willPayload":""},{"id":"d7a4b448.1de9f8","type":"gauth"}]




留言

張貼留言

這個網誌中的熱門文章

Heltec ESP32+OLED+Lora, hardware testing

圖片
you can easily find some good boards from china, but most of them lack of accurate document... Hrere is an example; https://hackaday.io/project/26991-esp32-board-wifi-lora-32 the board looks good with lora and a 128x64 OLED, but the IIC and reset connection is important, since pin can be muxing easily, so every board could be different. pinMode(16,OUTPUT);  digitalWrite(16, LOW); // set GPIO16 low to reset OLED  delay(50);  digitalWrite(16, HIGH); // while OLED is running, must set GPIO16 to high  and, if needed, edit your Wire.begin() to Wire.begin(4,15); (4 = SDA 15 SCL)  i tested it with the Adafruit SSD1306 and the Acrobotics SSD1306 Librarys  both work.  Display size is 128x64 There are discussions on these boards on TＴＮ as a LORA node..or maybee single ch gateway? Heltec has a git :  https://github.com/Heltec-Aaron-Lee And there is another copy call TTGO https://de.aliexpress.com/item/TTGO-LORA32-868-915-Mhz-SX1276-ESP32-Oled-display-Bluetooth-WIFI
閱讀完整內容

micro SD card for ESP32, on lolin32 with OLED and heltec 32 lora oled

圖片
I have purchased 2 micro SD card holder for my esp project. First I would like to test with heltec esp32 lora oled module. this module uses: sck=05 MISO=19 MOSI=27 SS(lora)=18, then I assigned 21 for sd SS and there is a lib in esp32-arduino, which use SS default at 5 I took the example and use SD.begin(21), but I alway get the error: entry 0x40078a9c Card Mount Failed But I do saw once that it read out the sd card type and volume. Not sure it's the issue of lora sd card swithing, or it just can't work. The other esp32 card I have is an wemos esp32 oled, it did not leave me too much pins, I need to find out how to connect sd to it. But I have wemos D1 mini esp8266 on hand, and connect it as:  * The WeMos Micro SD Shield uses:  * D5, D6, D7, D8, 3V3 and G  *  * The shield uses SPI bus pins:  * D5 = CLK  * D6 = MISO  * D7 = MOSI  * D8 = CS It's not working with the example in the arduino IDE 1.8.5, which marked SD(esp8266), but it w
閱讀完整內容

Install Network Time Protocol(NTP) on BeagleBone with Angstrom linux and set local time zone

Referenced from : http://cwraig.id.au/?p=513 1. Update your Package List opkg update 2. Get the ntp Package opkg install ntp ntpdate Update 2014/07/03 for the latest beaglebone image(2013-06-20) with kernel 3.8.12, after this installation,  system will add to service list, so only need to change time zone 3. Now we need to stop the ntpd service so we can request a manual update /etc/init.d/ntpd stop manual update ntpdate pool.ntp.org type in date, you should see the date is changing to current date and time, but the time zone is not corrected. The time zone info is contained in /etc/localtime , use this to remove it: rm /etc/localtime before link up our time zone, look into /usr/share/zoneinfo folder for your timezone. I did not find taiwan in asia, not plan to use hong_kong, but there is still: /usr/share/zoneinfo/Etc/GMT-8 now we make the link with ln command ln -s /usr/share/zoneinfo/Etc/GMT-8 /etc/localtime now type date again to check, should ha
閱讀完整內容