Trying to log data to Google sheets

General
1

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

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?

2

I went this route and it's worked well - node-red-contrib-google-sheets (node) - Node-RED

3

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.

4

No problem. I first added a function node to create an Excel timestamp format:

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:
image

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.

image

1 Like
5

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.

6

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.

7

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):

[{"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"]]}]
8

Parsed Json. The '[ ]' in the mustache template produces the array for sending to the node (if I'm remembering correctly).

9

You could use a Join node using msg.parts to specify the order of the join - Join Sequence Recipe (flow) - Node-RED

10

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.