IOT2040 data to Google spreadsheet in Google drive

Hello,

I had a request from a customer to send data from IOT2040 to a Google spreadsheet.

I made a couple research and found the following link.

[Showcase] Raspberry Pi to node red to Google Spreadsheet

How to send data from Arduino to Google Docs Spreadsheet

Push data to Google Spreadsheet

In the following post, I will show you how I did it.

Best regards,

HugoD

First, you have to create a "Google form".

Here is how you do it.

Now you have to give a name to the "Google form" and create a question for each variable you want.

Use the "short answer" format.

Here is the result:

Once the form is done, just click on the "SEND" button in the top right corner and copy and paste the link into a notepad.

Go back to you Google drive and you should see you new form in the document section.

This part is tricky.

Use the link you just pasted in the notepad and paste it in the URL section of your web browser.

You need to right-click on the answer section and select "Inspect".

In the inspector window, use "ctrl+f" to look for "entry".

You should get the same number of result as your questions.

Copy and paste every entries in your note pad.

Here is an example:

entry.1170618222

entry.344152671

entry.900206308

entry.205079081

Here is the exported flow you need to import in Node-RED.

https://flows.nodered.org/flow/b7bba30a006e1cc7729e

[{"id":"d1b3decf.05bd18","type":"inject","name":"CSV data in","topic":"","payload":"nid=2,count=10,speed=20,rssi=-10","payloadType":"string","repeat":"","crontab":"","once":false,"x":199.00001525878906,"y":239,"z":"818edd79.97e3d8","wires":[["21346762.6eb0d"]]},{"id":"21346762.6eb0d","type":"function","name":"Parser","func":"//splits the data\n//Code snippet from\n//http://flows.nodered.org/flow/6572686\n\nvar data = msg.payload.split(\",\");\n\nmsg.payload = {}; //null msg obj and create new properties\n//may need to remove the parseint function \nmsg.payload.nodeid = parseInt(data[0].split(\"=\")[1]);\nmsg.payload.counter = parseInt(data[1].split(\"=\")[1]);\nmsg.payload.speed = parseFloat(data[2].split(\"=\")[1]);\nmsg.payload.rx_rssi = parseInt(data[3].split(\"=\")[1]);\n\nreturn msg;","outputs":1,"x":362,"y":239,"z":"818edd79.97e3d8","wires":[["1dc343e8.8950e4"]]},{"id":"4e61a55b.b13824","type":"comment","name":"POST URL","info":"https://docs.google.com/forms/d/YourformID/formResponse?entry.1077669989=NODEID&entry_10292411=COUNTER&entry_1862110215=SPEED&entry_1744026500=RXRSSI\n\nReplace YourformID in the URL","x":549,"y":196.00000762939453,"z":"818edd79.97e3d8","wires":[]},{"id":"1092fe22.4414f2","type":"debug","name":"","active":true,"console":false,"complete":false,"x":732.0833129882812,"y":239.08331298828125,"z":"818edd79.97e3d8","wires":[]},{"id":"1dc343e8.8950e4","type":"http request","name":"","method":"GET","url":"https://docs.google.com/forms/d/YouFormID/formResponse?entry.1077669989={{payload.nodeid}}&entry_10292411={{payload.counter}}&entry_1862110215={{payload.speed}}&entry_1744026500={{payload.rx_rssi}}","x":550.0833129882812,"y":239.08331298828125,"z":"818edd79.97e3d8","wires":[["1092fe22.4414f2"]]},{"id":"539b3cdf.bf55ac","type":"comment","name":"Source","info":"Be it serial or any other data source. ","x":167.0833282470703,"y":187.0833282470703,"z":"818edd79.97e3d8","wires":[]}]

Here is the result.

Double-click on the "http request" node, copy the content of the "URL" section and paste it in the notepad.

Here is all you should have in your note pad.

URL from "SEND" button:
https://docs.google.com/forms/d/e/1FAIpQLSc2L_qJTvRPp01CiOPyDWi4nMpVq-qdj7iHKHG1GfyxuK3cKw/viewform?usp=sf_link

Entries:
entry.1170618222

entry.344152671

entry.900206308

entry.205079081


URL template:
https://docs.google.com/forms/d/YouFormID/formResponse?entry.1077669989={{payload.nodeid}}&entry_10292411={{payload.counter}}&entry_1862110215={{payload.speed}}&entry_1744026500={{payload.rx_rssi}}

Result:
https://docs.google.com/forms/d/e/1FAIpQLSc2L_qJTvRPp01CiOPyDWi4nMpVq-qdj7iHKHG1GfyxuK3cKw/formResponse?entry.1170618222={{payload.nodeid}}&entry.344152671={{payload.counter}}&entry.900206308={{payload.speed}}&entry.205079081={{payload.rx_rssi}}

Now copy the result in the "http request" node.

You need to use a Google account username and password in the "Basic authentication" section".

Deploy the flow using the "Deploy" button in the top right corner.

Now, go to the "Response" section of the Google form and select the "triple dot" button.

Select "Select response destination"

Create a new spreadsheet.

Go in the "Google sheets" section and you should now see the newly create sheet.

Open it.

You now see an empty spreadsheet with the name of the variables you create at the beginning.

Now go back to Node-RED and click on the button on the right of the "Inject" node.

Go back to the spreadsheet and you now see the new data with the timestamp.