Date and Time - 3/6 - Timestamps

Now that we know the definitions, let's explore the timestamps. They are easy and robust.

Demo Project

We will now explore a demo project, the usual scenario where we have to communicate between Google Sheets and some external system.

Stripe API has the endpoint for listing the invoices. It returns the invoice objects, each of them having the "created" property, it's a timestamp.

If you put this number in the Epoch Converter tool - you will note that it has the resolution in seconds.

Additionally, the same endpoint has parameters for filtering on the "created" field:

You can see the description says "this value" but I worked with this API before - that value is the timestamp number with seconds resolution. Consistent, thank you Stripe developers, the request and the response define the time the exactly same way.

Now we can offer our Google Sheets users a very nice menu for listing the invoices:

• Created in last N hours
• Created between some Start moment and End moment

getValue and setValue

I wrote the entire code, but it was too long and too many parts were not important for the topic. So I will just show the interesting parts.

Once we get to the "StartTime" and "EndTime" ranges, we can call the getValue method. We will get a Date variable, carrying the timestamp:

function readTimeRangeInvoices() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const startTime = ss.getRangeByName("StartTime").getValue();
  const endTime = ss.getRangeByName("EndTime").getValue();
  console.log(startTime);
  console.log(startTime instanceof Date);
  console.log(startTime.getTime());
  console.log(endTime);
  console.log(endTime instanceof Date);
  console.log(endTime.getTime());
}

The logs we get are:

All these logs show that we are getting back the Date object.

Before talking to the external system (Stripe API) we just have to adapt the resolution, milliseconds to seconds:

   const invoices = Stripe.listInvoices(
     Math.round(startTime.getTime() / 1000),
     Math.round(endTime.getTime() / 1000));

When writing back to the sheet, we will take the number from Stripe (timestamp in seconds resolution) and convert it into the Date before calling the setValue method. Of course, we need to multiply it by 1000 to get the milliseconds.

  const values = invoices.map(i => [
    new Date(i.created * 1000),
    i.status,
    i.email,
  ]);

and you get something like this:

Mismatches

In most of the projects, we set the GAS project time zone to be the same as the Google Sheets file. In that way, we have a WYSIWYG system: in our code, debugger, logger - the date and time look the same as in the Google Sheets file.

Sometimes it's not possible: GAS project is not contained in Google Sheets file, they may be a standalone Library project. Sometimes we clone the GS file together with the contained GAS project and give it to another client office in another time zone... It's always good to think about the mismatch scenario and have it covered.

GAS project time zone change

The example project screenshots are made for both the GS file and the GAS project having the same time zone: CET. When I change the GAS project zone to Pacific Time (PT) it works exactly the same.

getValue gives the same timestamp, setValue interprets the invoice timestamp the same way: GS file time zone is still CET, so it converts it to the same string.

GS file time zone change

Now I will move the file into the PT zone. The GAS project is back in the CET zone. When I call the "last 48 hours" I get the exact same invoices:

I copied the old results (yellow block) just for comparison. This is different, but it's expected, the same timestamps in the different time zone have different string representations.

Displaying in Google Sheets (front-end) probably works similarly to formatDate method:

• date is the number that the cell stores as a value, timestamp or some other way
• timeZone is the Google Sheets file setting
• format is the user setting for that cell, probably saved as another cell property

GAS project and GS file time zone change

Now when I change both the GAS project and GS file to the PT zone:

It's exactly the same as in the previous scenario (GS file in PT zone, GAS project in CET zone).

So only the representation depends on the GS file project. If we change the GAS project zone to "America/New_York" - we will get the same results.

Google Apps Script is like a honey badger from the legendary YouTube video. It simply doesn't care.

getValue protocol between GS (front-end) and GAS (back-end):

setValue protocol between GS (front-end) and GAS (back-end):

Date Shifting Demystified

This section is just a bonus, explaining the "difference in hours surprise" in part 1 of this guide (Date Shifting section). Sometimes I am creating examples that "break" things - in that way, I have a quick reminder of how things work.

On the Parameters sheet, we have the settings, so the user can choose different options.

Here, when we change the time zone of the GS file, after reloading, date and time cells keep their string representations the same! Shouldn't they change too, we changed the time zone.

There are two choices:

• Protect the underlying data. Keep the timestamp and change the representation. Risk to confuse the user, but keep the data consistent.
• Protect the user's WYSIWYG experience. Change the data underneath, change the timestamp.

Google developers chose the 2nd option, I think it's a good choice. I can imagine the end users entering a lot of data in some inconvenient way (reading from some paper). Then they realize their GS file is in the wrong time zone, OK let's change it. If we insist to keep the timestamps, they would be surprised that their data "changed". This choice would probably generate a lot of support tickets.

So far, we called the "Latest N hours" menu item. It always picked the same set of invoices, the last 48 hours are the same in any time zone. Only the representation was different, GS file, as the front-end, was displaying them according to the time zone.

Now, when we call the "Time Range" menu, in the PT zone one of the invoices is filtered out:

It makes sense, in CET our settings were "3/10/2023 6:00:00" to "3/11/2023 20:00:00". We got 3 invoices in that range, in the CET zone (yellow block).

We change the GS file zone to PT. As we explained, our settings will remain the same strings "3/10/2023 6:00:00" to "3/11/2023 20:00:00". But timestamps "under" these cells will change. And we will get only 2 invoices (green block). But that's exactly what the user expects in the PT zone with these settings!

But when you combine date cells with the NOW function (which is just a timestamp) you can get the confusion like in part 1 of this guide. Timestamp exploration is finished, let's check how we deal with dates.

Questions, Corrections and Suggestions are appreciated: contact me.