3

I have a Google Apps Script which receives a date/time value formatted in rfc3339, with Timezone info, for example: 2022-12-29T06:34:07.442788Z

However, Google Sheets doesn't recognize this as a date (and neither does Looker/Data Studio) due to the presence of the "Z"

How can I parse this into a date-consistent format? I can just trim the Z easily, which resolves the issue (but then it is treated as local time, which isn't an issue as I know the data is always received in UTC and I can set the spreadsheet to the same timezone) - but is there a better way to do this?

2
  • Welcome to Web Applications Stack Exchange. It is unclear whether you need to offset the timestamp to some specific timezone or if UTC is fine with you. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Dec 29, 2022 at 8:30
  • I ended up using string.slice(0, -1); and just removing the Z, and then setting the destination sheet/table settings (File -> Settings -> Timezone) to GMT/UTC so that they match. This ended up being easier than using third party parsing libraries such as Moment.js (which did work as well but aren't needed when I am confident the values match)
    – Stacks
    Dec 29, 2022 at 20:43

1 Answer 1

2

You can convert an ISO8601 timestamp into a JavaScript Date object and write the date into a spreadsheet cell like this:

  const ss = SpreadsheetApp.getActive();
  const cell = ss.getRange('Sheet1!A2');
  const timestamp = cell.getDisplayValue();
  const date = new Date(timestamp);
  cell.setValue(date);

The datetime in the cell will be shown in the timezone set in File > Settings > Time zone. It will represent the same moment as the UTC timestamp. If you want to display the time in GMT, choose the GMT (no daylight saving) timezone.

To convert the date into a text string that represents another timezone, use Utilities.formatDate(), like this:

  const ss = SpreadsheetApp.getActive();
  const timezone = ss.getSpreadsheetTimeZone();
  const cell = ss.getRange('Sheet1!A2');
  const timestamp = cell.getDisplayValue();
  const date = new Date(timestamp);
  cell.setValue(Utilities.formatDate(date, timezone, 'yyyy-MM-dd HH:mm'));

You can also set timezone to a long format string such as 'America/New_York', as listed by Joda.org.

Google Sheets will automatically convert certain types of text strings into numerical values. You can take advantage of that like this:

  const ss = SpreadsheetApp.getActive();
  const cell = ss.getRange('Sheet1!A2');
  const timestamp = cell.getDisplayValue();
  const dateString = timestamp.replace('Z', '');
  cell.setValue(dateString);

Note that the datetime value in the target cell will refer to a moment in time that differs from the the timestamp, unless you set the spreadsheet's timezone to GMT.

You can also convert timestamps to dateserial values with a spreadsheet formula like this:

=datevalue(left(A2, 10)) + timevalue(mid(A2, 12, 12))

...or, taking a shortcut:

=value(left(A2, len(A2) - 1))

Format the result cell as Format > Number > Date time. Again, note that the value will not refer to the same moment as the timestamp, unless the spreadsheet's timezone is GMT.

See Working with date and time values in Google Sheets and Working with Dates and Times in Apps Script.

3
  • Unfortunately this won't work; I need an Apps Script solution before it's recorded (as the API tool I am using overwrites any data present into the spreadsheet)
    – Stacks
    Dec 29, 2022 at 20:36
  • Google sheets doesn't recognize the value as a date/time value because of the Z (it is looking for a time/date only, doesn't handle the time zone). Once removed Google Sheets recognizes the value as a valid date/time.
    – Stacks
    Dec 30, 2022 at 23:40
  • I had a chance to test your solution and it does work well and seems more correct, although functionally it has no difference from removing the Z. It's certainly frustrating that Google Sheets doesn't recognize IS8601/RFC3339 directly into the sheet if they contain TZ information (only without TZ info).
    – Stacks
    Jan 1, 2023 at 4:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.