Date and Time - 4/6 - Reading from Google Sheets

Some external systems do not work with timestamps but with date and time strings.

For example, Meta Marketing API has the Insights API section which "provides a single, consistent interface to retrieve ad statistics". When we get into the parameters, we'll see that they expect a time range in the "perfect date" format:

We are interested in how ads perform on a daily, weekly or monthly level. So for example, we can ask: How much is this campaign spending, the time range is 2023-03-01 to 2023-03-14 and we want to group results into 7-day chunks. The response would be something like this:

{
    "data": [
        {
            "impressions": "17046",
            "inline_link_clicks": "102",
            "spend": "584.58",
            "date_start": "2023-03-01",
            "date_stop": "2023-03-07"
        },
        {
            "impressions": "18900",
            "inline_link_clicks": "101",
            "spend": "557.53",
            "date_start": "2023-03-08",
            "date_stop": "2023-03-14"
        }
    ],
    "paging": {
        "cursors": {
            "before": "MAZDZD",
            "after": "MQZDZD"
        }
    }
}

In this example, we will work with the date part only. If you have a scenario where the time part is needed - you will apply the exact same techniques.

It's easy, we send strings, we receive strings, we don't care about the time zone.

It's time to read

OK, now we want to read what the user selected in Google Sheets and use those settings to ask the API for data.

Remember, we want to cover the general case: GS and GAS time zones are different.

The first thing that comes to my mind is to use getDisplayValue method on each cell. In that way we read the string representation "2023-03-01" directly, skipping the underlying Date value.

But of course, sooner or later our users will find this "perfect date" format confusing, and they will want something like this:

Or "March 1, 2023" or some weird format, it's the Front-end, we don't control it.

So method getDisplayValue won't work, we'll have to use getValue and formatDate. We have the Date, we have the desired format "yyyy-MM-dd", the only thing left is the timeZone.

If GS file is in CET time zone and GAS project in PT zone, this code:

function test() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const startDate = ss.getRangeByName("StartDate").getValue();
  const endDate = ss.getRangeByName("EndDate").getValue();

  Logger.log(startDate);
  Logger.log(endDate);
}

We will get logs:

We are reading the Date from the GS file, so we need to read the time zone of the GS file:

function test() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const startDate = ss.getRangeByName("StartDate").getValue();
  const endDate = ss.getRangeByName("EndDate").getValue();

  // const timeZone = Session.getScriptTimeZone(); // GAS project
  const timeZone = ss.getSpreadsheetTimeZone(); // GS file
  Logger.log(timeZone);

  const startDateString = Utilities
    .formatDate(startDate, timeZone, "yyyy-MM-dd");
  const endDateString = Utilities
    .formatDate(endDate, timeZone, "yyyy-MM-dd");

  Logger.log(startDateString);
  Logger.log(endDateString);
}

We'll get:

And that's it, everything works the way we want.

When we need to read from the GS cell, convert it to some formatted string to talk to the external system: no problem. Even if the GS file and GAS project do not have the same time zone. The GAS project time zone is irrelevant. We will get both the Date and time zone from the GS file - the only thing left is to provide the format.

Now the reading is covered, we'll move on to writing to the Google Sheets, that part is not that easy.

Questions, Corrections and Suggestions are appreciated: contact me.