Date Handling

I am using data from a Postgres node, which contains some date fields, to populate a Google Sheet.

In the output of the Postgres node, I can see data values in the format “2020-05-18T11:19:09.000Z”, but when these are written to the Google Sheet, they appear as “Mon May 18 2020 11:19:09 GMT+0000 (Coordinated Universal Time)” - these cannot be formatted in the Google Sheet.

Is there way to specify a date format (using either a function node, or some other way)?

No that is sadly not possible. You would have to set the format on the column in Google Sheets itself.

Another thing you probably have to do to make it work is to add on the Google Sheets node under “Options” -> “Value Input Mode” and set it to “User Entered”.

Thanks, @jan. I have set the Input Mode, but it made no difference. Also tried setting the format on the Google Sheet column, but the sheet cannot interpret a date like “Mon May 18 2020 11:19:09 GMT+0000 (Coordinated Universal Time)” to a normal date.

The best solution I could come up with is to place a FunctionItem in between the Postgres and Google Sheet nodes, where I substr the dates into a “yyyy/mm/dd” string. Not elegant, but this way at least Google Sheets can interpret the value as a date.

Very interesting. I guess then that is the only solution. In the Google API there is no way to say how the input should be interpreted. They only have those options:

So it seems you really have to define in Google Sheets itself what format the column should have and then send it in a format Google Sheets understand.