Quick Guide to Send data to Google Drive/Google Sheets. (Might make someones life easy!!)

[John O Connor]

Hi Folks, A quick guide to upload your data to google drive, might help someone out!!!! And just to say thanks for all the help I have recieved here over the last while.

Just a quick tutorial to add data to google sheets,(same as a csv). It is a quick and easy way to store and log data, with google products mobile apps, dashboards and analytics can be formed quickly.

Assuming you have a gmail/google account.

Go into ‘my Drive’ Open www.google.com, select the squares up in the corner. Go to ‘my Drive’.

While in ‘my  Drive’, Click on ‘new’, then select ‘forms’., a new sheet will open.

In this create a new field, name it as required. Make sure to select the answer as ‘Text’.

Do this again, so we have two fields, Temp 1 and Temp 2.

Now for the link part, this will connect our node-red to google sheets, which we can look at on ‘my Drive’, which can be shared printed or download.

Click, Responses, then ‘Choose Response Destination’ (This is where you create a spreadsheet on Google Drive)

After this, press create.

After this click ‘Responses’ again then ‘Get pre-filled URL’. (This will give us a link to the web page)

Our url will look like this;

https://docs.google.com/forms/d/1GYxYrONESHz-SM9nSSmQjlZi_M-1gN6GknU22S1jdj4/viewform?entry.632411608=25&entry.861197105=10

Three things to change here  ‘viewform’  changes to ‘formResponse’

And the two values can be changed to a payload of your choice, i.e. payload.temp1

The blue part will be different for everyone, unique to your drive.

The values in green are references to the cells.

Your new line should look like;

https://docs.google.com/forms/d/1GYxYrONESHz-SM9nSSmQjlZi_M-1gN6GknU22S1jdj4/formResponse?entry.632411608={{payload.temp1}}&entry.861197105={{payload.temp2}}

Post this into a http node.

Insert the URL. Your payload will start streaming to the file you created on ‘my Drive’.  Or else on the form tab you were working with, will have a button called ‘view responses’.

Any Questions dont be afraid to ask.

This might help someone out!!!!!

Regards

John.

[Scott Chapman]

Yea, I am not getting this to work for me. I'm clearly using a payload object with data:

msg.payload : Object{ "humidity": "17.7", "temp": "55.5", "ambientTemp": "63.7" }

And I am adding the values to my URL using mustache syntax:

https://docs.google.com/forms/d/<myid>/formResponse?entry.1775274334={{payload.temp}}&entry.581921689={{payload.humidity}}

I know the URL is right since I can past it into my browser, replace the payload statements with values and I see data end up in my spreadsheet.But from Node-Red, the error I get back from google just says "Your client has issues a malformed or illegal request..."
What am I missing??

[John O Connor]

You need to change the payload, so it is output as a number, so the payload would only be (for example, Humidity) 17.7. so msg.payload=msg.payload.humidity. In google sheets then you can put the column heading as Humidity.

Hope this helps. Im online for the rest of the evening, if you get stuck.

John

--
http://nodered.org
---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/IO3I0PI_u2g/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+u...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Scott Chapman]

Thanks for the reply, but I'm not getting it. In my form the column header is "Humidity". I can convert the string "17.7" to a number with msg.payload.humidity = Number(msg.payload.humidity).

Is that what you mean? Perhaps you can give me an example to get from where I am now to what you have? Maybe a super simple sample flow?

[Mark Setrem]

It might be easier to see where you are going wrong if you posted some code...

Personally if you're happy with function nodes which it appears you are. Use + to join rather than a separate node using moustache.

Eg msg.url="https://docs.google.com/forms/d/<myid>/formResponse?entry.1775274334="+msg.payload.temp+"&entry.581921689="+msg.payload.humidity;

Then join this function node to a Dubug node that you can configure to display msg.url and it should be fairly easy to see where it is going wrong.

[John O Connor]

Here is my flow

[{"id":"35042097.cafbe","type":"mqtt-broker","broker":"m20.cloudmqtt.com","port":"16026","clientid":""},{"id":"7ae3ea89.fe3e7c","type":"json","name":"","x":710.3333129882812,"y":307,"z":"cc0ea7dd.50ad6","wires":[["9d339faa.52e8b"]]},{"id":"9d339faa.52e8b","type":"switch","name":"Lab Temp","property":"payload.tempint","rules":[{"t":"nnull"}],"checkall":"true","outputs":1,"x":861.7619323730469,"y":304.6190586090088,"z":"cc0ea7dd.50ad6","wires":[["d33c2d50.2e5638"]]},{"id":"a6479f09.7494a8","type":"mqtt in","name":"Moxy 2","topic":"BB/001343190045/#","broker":"35042097.cafbe","x":542.3333320617676,"y":304.99999046325684,"z":"cc0ea7dd.50ad6","wires":[["7ae3ea89.fe3e7c"]]},{"id":"d33c2d50.2e5638","type":"function","name":"payload","func":"\nmsg.payload = msg.payload.tempint;\nreturn msg;\n","outputs":"1","noerr":0,"x":1040.2379455566406,"y":312.14284324645996,"z":"cc0ea7dd.50ad6","wires":[["64a9af5.70bf75","448d88c1.2c771"]]},{"id":"64a9af5.70bf75","type":"template","name":"Format","field":"payload","format":"handlebars","template":"Batcher Temp {{payload}} Degrees","x":1197.5712814331055,"y":318.0476360321045,"z":"cc0ea7dd.50ad6","wires":[["75e26823.7fc8d"]]},{"id":"448d88c1.2c771","type":"http request","name":"Single Log","method":"GET","ret":"txt","url":"https://docs.google.com/forms/d/1r4UEiChdoKVq5kHbbHSmUXICTY8suMG4c0JJYDKnrYc/formResponse?entry.53942331={{payload}}","x":1180.333251953125,"y":407,"z":"cc0ea7dd.50ad6","wires":[[]]},{"id":"75e26823.7fc8d","type":"debug","name":"","active":true,"console":"false","complete":"false","x":1397.5712432861328,"y":316.38097953796387,"z":"cc0ea7dd.50ad6","wires":[]}]

[John O Connor]

And this is my debug.

[Scott Chapman]

Here is my flow (I've hidden my form ID)

[{"id":"f09fa129.0f606","type":"function","z":"e52918a2.1ad6e8","name":"Construct URL","func":"msg.url = \"https://docs.google.com/forms/d/<MYID>/formResponse?entry.1775274334=\"+msg.payload.temp+\"&entry.581921689=\"+msg.payload.humidity;\nreturn msg;\n","outputs":1,"noerr":0,"x":470,"y":374,"wires":[["e3e2740c.1c1d88","ee29b041.11d65"]]}]

[John O Connor]

I used a http request node to send the data through to google sheets. Your flow is not importing correctly for me for some reason.

[Scott Chapman]

Sorry, try this: [{”id”:”f09fa129.0f606”,”type”:”function”,”z”:”e52918a2.1ad6e8”,”name”:”Construct URL”,”func”:”msg.url = \”https://docs.google.com/forms/d/<MYID>/formResponse?entry.1775274334=\”+msg.payload.temp+\”&entry.581921689=\”+msg.payload.humidity;\nreturn msg;\n”,”outputs”:1,”noerr”:0,”x”:470,”y”:374,”wires”:[[”e3e2740c.1c1d88”,”ee29b041.11d65”]]},{”id”:”ee29b041.11d65”,”type”:”http request”,”z”:”e52918a2.1ad6e8”,”name”:””,”method”:”GET”,”ret”:”txt”,”url”:””,”x”:661,”y”:258,”wires”:[[”e3e2740c.1c1d88”]]},{”id”:”e3e2740c.1c1d88”,”type”:”debug”,”z”:”e52918a2.1ad6e8”,”name”:””,”active”:true,”console”:”false”,”complete”:”true”,”x”:735,”y”:400,”wires”:[]},{”id”:”7499832b.8b667c”,”type”:”inject”,”z”:”e52918a2.1ad6e8”,”name”:””,”topic”:””,”payload”:”{'temp': 65, 'humidity'=35}”,”payloadType”:”none”,”repeat”:””,”crontab”:””,”once”:false,”x”:134,”y”:155,”wires”:[[”a7061764.58f9e8”]]},{”id”:”a7061764.58f9e8”,”type”:”function”,”z”:”e52918a2.1ad6e8”,”name”:”Generate JSON”,”func”:”msg.payload = {temp: 65, humidity: 40};\nreturn msg;”,”outputs”:1,”noerr”:0,”x”:346,”y”:279,”wires”:[[”f09fa129.0f606”]]}]

On Mon, Feb 8, 2016 at 1:20 PM John O Connor <dc.j...@gmail.com> wrote:

I used a http request node to send the data through to google sheets. Your flow is not importing correctly for me for some reason.

To unsubscribe from this group and all its topics, send an email to node-red+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
http://nodered.org
---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/IO3I0PI_u2g/unsubscribe.

To unsubscribe from this group and all its topics, send an email to node-red+unsubscribe@googlegroups.com.

[Scott Chapman]

I finally got it. My error was that I was passing an object to the HTTP request which already had some headers and statuscode, I cleared those and I appear to be posting!

On Wednesday, October 7, 2015 at 3:01:47 PM UTC-4, John O Connor wrote:

[Mazdia Mollah]

Hi! 

Could you post your updated flow? I am also trying to store temperature values. I can store the date and time, but the column for temperatures remain empty.

I would very much appreciate it! 

[muiz rosli]

hello, it is still working? because im trying but it now working.

[John O Connor]

There has been some changes to google sheets, so I'll check it out and get back to you later on it.

--
http://nodered.org
 
** We're moving this mailing list over to the new Node-RED Forum: https://discourse.nodered.org **
 
Join us on Slack to continue the conversation: http://nodered.org/slack

---
You received this message because you are subscribed to a topic in the Google Groups "Node-RED" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/node-red/IO3I0PI_u2g/unsubscribe.
To unsubscribe from this group and all its topics, send an email to node-red+u...@googlegroups.com.

To post to this group, send email to node...@googlegroups.com.
Visit this group at https://groups.google.com/group/node-red.
To view this discussion on the web, visit https://groups.google.com/d/msgid/node-red/cfd98210-e8ca-4191-951e-c91a564db719%40googlegroups.com.

[kenk...@gmail.com]

Hi John,

Yes, seems to some big changes with form, I cannot work out how to get the "prefilled url" to compose the get request. I just want to send a simple timestamp to a google spreadsheet to check when my pi drops offline.

Thanks,

Ken  

[muiz rosli]

i hope you can fix it asap, because i want use it before this tomorrow friday