Question

Submit ISO time to spreadsheet

  • 2 November 2022
  • 6 replies
  • 32 views

Hi! I have a zap setup to send submitted JSON data from a webhook to Google Sheets and am trying to get it to include the ISO timestamp for the SYSTEMCOLUMN_SubmitTime field.

The issue is that it keeps converting the timestamp like 2022-11-01T19:21:25.0946694Z to something like 2022-11-01 15:21:25 before it inserts into the sheet which drops the milliseconds.

How can I set it to just insert the ISO time?


This post has been closed for comments. Please create a new post if you need help or have a question about this topic.

6 replies

Userlevel 7
Badge +8

Hi @casey71 

 

Are you sending the entire load to Google Sheets in one cell or is the time being sent in a different cell? Meaning, could it be a Google Sheet formatting issue? 

 

Also, you can always use Zapier Formatter to keep the formatting as you need it. 

Hi @MohSwellam , 

I’m sending the data to an individual column which is set to Plan Text format but it is still getting converted. I need it to stay as a string and was trying to prefix the timestamp with an apostrophe to force it to be text but can’t seem to find a way with the Formatter action.

Userlevel 7
Badge +8

The prefixing option should work, it didn't? Have you checked the zap history (or maybe the test data) to see what value Zapier is passing? 

 

If for some reason it is passing the time without the milliseconds, you can use the formatter to make sure the Data Out from the formatter contains the required format. 

Data in:

COL$A:

2022-11-02T18:05:51.7353376Z

Data out:

COL$A:

2022-11-02 14:5:51.000

My understanding is Data out is what gets echoed back from Sheets which appears to be converting the timestamp even though I have the field set to Plain text.

Do you know how I would set up the prefixing to add the apostrophe using Formatter?

Userlevel 7
Badge +8

Hi @casey71 

 

Yes that is correct, it seems that is gets formatted by GSheet 

 

You can simple type * before the field that is mapped in the Sheet step, so it will send it as a string. Give it a try and let me know

Thanks, for pointing me to that. I got it work with apostrophe (‘).