Google Sheet - Multiple Cells

General
1

Apologies for this very basic question but - Using Home Assistant, I’m trying to store temperature data in a google sheet. It works but the flow below only puts the temperature in the sheet and I would like the current date/time as well so I end up in my spreadsheet with two columns.

|23 Aug 2021 16:15|28.2|

What do I need to change to my flow to get this date/time column passed to the google sheet component?

PortugalTempNode
PortugalTempNode895×319 29.4 KB

2

Look at the built in help, the states

The data to be written to the sheet at the specified cells, a string will write to a single cell an array will write a row and a matrix will write multiple rows

So instead of sending separate messages, send an array with [timestamp,value]...

  • delete the timestamp node
  • add a function node BETWEEN the temperature node and the GSheet node containing...
    msg.payload = [Date.now(), msg.payload];
return msg;

PS: untested (I have never used or installed GSheet)

3

I don't remember the exact issue with using the Date.now() but I ended up with the following function to get it into Excel's date format

var date1 = new Date();
var dateexcel = 25569.0 + ((date1.getTime() - (date1.getTimezoneOffset() * 60 * 1000)) / (1000 * 60 * 60 * 24));
msg.dateexcel = dateexcel.toString().substr(0,20);
return msg;

That then feeds into a template node to format (probably could have integrated that into my function node and saved a step)

["{{dateexcel}}","{{payload}}"]