# Trying to log data to Google sheets
**URL:** https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382
**Category:** General
**Created:** [9 May 2021 07:28 UTC](https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382 "2021-05-09T07:28:06Z")
**Posts on this page:** 10
**Page:** 1
### Author:  [@Matt.L](https://discourse.nodered.org/u/Matt.L)
#### Post date: [9 May 2021 07:28 UTC](https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382/1 "2021-05-09T07:28:06Z")
I'll start saying I'm a Javascript novice which I think is the source of my troubles.
The method I'm trying to use is the HTTP request node that connects to a form created via google drive.
The basic idea is here: [Sending Data of Wireless Vibration and Temperature to Google Sheets Using Node-RED : 37 Steps - Instructables](https://www.instructables.com/Sending-Data-of-Wireless-Vibration-and-Temperature/)
In my case, I'm joining 3 nodes to get the following node.
test2/test2/humidity : msg : Object
{
"topic":"test2/test2/humidity", //topic
"payload":
{
"test2/test2/temp\_c":"24.90", //payload["test2/test2/temp\_c"] 24.90
"test2/test2/temp\_f":"76.82", //payload["test2/test2/temp\_f"]
"test2/test2/humidity":"34.00" //payload["test2/test2/humidity"]
}
"qos":1,
"retain":false,
"\_topic":"test2/test2/humidity",
"\_msgid":"4393f367.357e2c"
}
What I am trying to understand is why inserting this into the forms URL didn't work:
...formResponse?usp=pp\_url&entry.1464860579={{payload["test2/test2/temp\_c"]}}... (snipped for brevity).
What I ended up having to do is to use a function node to copy the data from the object in the payload to 3 separate entries in the payload:
msg.payload.temp\_c = msg.payload["test2/test2/temp\_c"];
msg.payload.temp\_f = msg.payload["test2/test2/temp\_f"];
msg.payload.humidity = msg.payload["test2/test2/humidity"];
return msg;
Then I could build the URL like so:
...formResponse?usp=pp\_url&entry.1464860579={{payload.temp\_c}}...
Then it all worked.
What I am trying to understand is if there is a way I can use payload["test2/test2/temp\_c"] (and the other fields) directly and if so, what is the correct syntax?
---
### Author:  [@rgerrans](https://discourse.nodered.org/u/rgerrans)
#### Post date: [9 May 2021 18:04 UTC](https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382/2 "2021-05-09T18:04:38Z")
I went this route and it's worked well - [node-red-contrib-google-sheets (node) - Node-RED](https://flows.nodered.org/node/node-red-contrib-google-sheets)
---
### Author:  [@Matt.L](https://discourse.nodered.org/u/Matt.L)
#### Post date: [9 May 2021 22:33 UTC](https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382/3 "2021-05-09T22:33:29Z")
Do you have any examples for using that node? That was one of the issues I was running into.
One that that isn't clear to me is what the input to the node needs to be, especially for the append row mode to work.
---
### Author:  [@rgerrans](https://discourse.nodered.org/u/rgerrans)
#### Post date: [9 May 2021 22:52 UTC](https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382/4 "2021-05-09T22:52:07Z")
No problem. I first added a function node to create an Excel timestamp format:
```auto
var date1 = new Date();
var dateexcel = 25569.0 + ((date1.getTime() - (date1.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));
msg.timestamp = new Date().toLocaleTimeString('en-US');
msg.datestamp = new Date().toLocaleDateString('en-US');
msg.dateexcel = dateexcel.toString().substr(0,20);
return msg;
```
I then use a template node to create the input to the Google Sheet node:
Let me know if that helps, here's a screenshot of the flow (the subflow is just a single function node of the above so I can use it with multiple injects.
---
### Author:  [@Matt.L](https://discourse.nodered.org/u/Matt.L)
#### Post date: [11 May 2021 04:45 UTC](https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382/5 "2021-05-11T04:45:22Z")
Thanks for the help!
The biggest hurdle is the lack of documentation on that node, but after some fiddling, it looks like it can handle a few different input types.
Is that template node there to create a CSV entry? If so, then I think I have all the work to make this happen.
One thing I'd like to know is if I can have the GSheet node skip the first row so I can make it a header.
For one flow, I have 2 temperature and humidity sensors as well as 2 temperature sensors I want to log side by side to compare and analyze. So having a heading row to keep all that straight will be important.
---
### Author:  [@rgerrans](https://discourse.nodered.org/u/rgerrans)
#### Post date: [11 May 2021 13:42 UTC](https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382/6 "2021-05-11T13:42:56Z")
I set the node to append row, then it adds the data from the template node as a new row in the column order as submitted from the template.
---
### Author:  [@Matt.L](https://discourse.nodered.org/u/Matt.L)
#### Post date: [11 May 2021 17:06 UTC](https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382/7 "2021-05-11T17:06:46Z")
I have the GSheet node working but only if msg.payload is an array.
Is your template node producing a parsed JSON array or is the output plain text?
In my case, I have 4 separate topics I'm joining then trying to log that. Because the timing of the topics is variable, the order in the join operation is not guaranteed. I ended up having to write a function that transforms the payload from a JSON object to an array in a specific order.
But the good news is that it works now and I'm sharing what I've done so hopefully it can help someone else trying to use the node-red-contrib-google-sheets node.
Here is my sample flow I used for debugging (gsheets node has not been configured to hide credentials):
```auto
[{"id":"abc60c3f.532ff","type":"tab","label":"sample_solution","disabled":false,"info":""},{"id":"23108ab0.cc5a86","type":"debug","z":"abc60c3f.532ff","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1210,"y":240,"wires":[]},{"id":"4bc3a186.df034","type":"moment","z":"abc60c3f.532ff","name":"","topic":"","input":"","inputType":"date","inTz":"ETC/UTC","adjAmount":"0","adjType":"hours","adjDir":"subtract","format":"dd:HH:mm:ss","locale":"en-US","output":"payload","outputType":"msg","outTz":"US/Pacific","x":340,"y":160,"wires":[["b22f4ff3.c4a2f"]]},{"id":"f12ea934.0a3e18","type":"join","z":"abc60c3f.532ff","name":"","mode":"custom","build":"object","property":"payload","propertyType":"msg","key":"topic","joiner":"\\n","joinerType":"str","accumulate":false,"timeout":"","count":"4","reduceRight":false,"reduceExp":"","reduceInit":"","reduceInitType":"","reduceFixup":"","x":670,"y":60,"wires":[["e4b57bd8.5c7f18","1f5095d3.bbb29a"]]},{"id":"babaaa00.f54548","type":"debug","z":"abc60c3f.532ff","name":"","active":true,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":1070,"y":40,"wires":[]},{"id":"b22f4ff3.c4a2f","type":"change","z":"abc60c3f.532ff","name":"","rules":[{"t":"set","p":"topic","pt":"msg","to":"test2/timestamp","tot":"str"}],"action":"","property":"","from":"","to":"","reg":false,"x":530,"y":160,"wires":[["f12ea934.0a3e18"]]},{"id":"e4b57bd8.5c7f18","type":"debug","z":"abc60c3f.532ff","name":"","active":false,"tosidebar":true,"console":false,"tostatus":false,"complete":"true","targetType":"full","statusVal":"","statusType":"auto","x":790,"y":180,"wires":[]},{"id":"1f5095d3.bbb29a","type":"function","z":"abc60c3f.532ff","name":"","func":"//var payload = {};\n//msg.payload.temp_c = msg.payload[\"test2/test2/temp_c\"];\n//msg.payload.temp_f = msg.payload[\"test2/test2/temp_f\"];\n//msg.payload.humidity = msg.payload[\"test2/test2/humidity\"];\n//payload.timestamp = msg.payload[\"test2/timestamp\"]\n//payload.temp_c = msg.payload[\"test2/test2/temp_c\"];\n//payload.temp_f = msg.payload[\"test2/test2/temp_f\"];\n//payload.humidity = msg.payload[\"test2/test2/humidity\"];\n//msg.payload = payload;\n\nvar data_array = [msg.payload[\"test2/timestamp\"],\nmsg.payload[\"test2/test2/temp_c\"],\nmsg.payload[\"test2/test2/temp_f\"],\nmsg.payload[\"test2/test2/humidity\"]];\nmsg.payload = data_array\nreturn msg;","outputs":1,"noerr":0,"initialize":"","finalize":"","x":880,"y":40,"wires":[["babaaa00.f54548","b8800c06.3a846"]]},{"id":"828ff31f.2c596","type":"inject","z":"abc60c3f.532ff","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"test2/test2/temp_c","payload":"0","payloadType":"str","x":90,"y":20,"wires":[["f12ea934.0a3e18"]]},{"id":"5e7e2387.e398dc","type":"inject","z":"abc60c3f.532ff","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"test2/test2/temp_f","payload":"32","payloadType":"str","x":100,"y":60,"wires":[["f12ea934.0a3e18"]]},{"id":"dfb13806.a15ca8","type":"inject","z":"abc60c3f.532ff","name":"","props":[{"p":"payload"},{"p":"topic","vt":"str"}],"repeat":"","crontab":"","once":false,"onceDelay":0.1,"topic":"test2/test2/humidity","payload":"25","payloadType":"str","x":100,"y":100,"wires":[["f12ea934.0a3e18","4bc3a186.df034"]]},{"id":"b8800c06.3a846","type":"GSheet","z":"abc60c3f.532ff","creds":"","method":"append","action":"","sheet":"","cells":"","flatten":false,"name":"","x":1080,"y":120,"wires":[["23108ab0.cc5a86"]]}]
```
---
### Author:  [@rgerrans](https://discourse.nodered.org/u/rgerrans)
#### Post date: [11 May 2021 18:00 UTC](https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382/8 "2021-05-11T18:00:28Z")
> [@Matt.L](#):
>
> I have the GSheet node working but only if msg.payload is an array.
> Is your template node producing a parsed JSON array or is the output plain text?
Parsed Json. The '[]' in the mustache template produces the array for sending to the node (if I'm remembering correctly).
---
### Author:  [@rgerrans](https://discourse.nodered.org/u/rgerrans)
#### Post date: [11 May 2021 18:04 UTC](https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382/9 "2021-05-11T18:04:58Z")
> [@Matt.L](#):
>
> In my case, I have 4 separate topics I'm joining then trying to log that. Because the timing of the topics is variable, the order in the join operation is not guaranteed. I ended up having to write a function that transforms the payload from a JSON object to an array in a specific order.
You could use a Join node using msg.parts to specify the order of the join - [Join Sequence Recipe (flow) - Node-RED](https://flows.nodered.org/flow/d1882664c755b52a7565378c6dca3f18)
---
### Author:  [@system](https://discourse.nodered.org/u/system)
#### Post date: [10 July 2021 18:05 UTC](https://discourse.nodered.org/t/trying-to-log-data-to-google-sheets/45382/10 "2021-07-10T18:05:25Z")
This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.