Date and Time - 5/6 - Writing to Google Sheets

Previously our task was to read Google Sheets cell/range and to convert the value into the string in some desired format. In this post, we will explore the opposite direction.

This is the most complex part of this guide, so grab a coffee.

Facebook Marketing API returns a string in perfect date format "2023-03-01" and we want to write it into the cell. In the general case, the GAS project and GS file have different time zones. The cell may have the same format, displaying "2023-03-01", but what if GS users want to have the format "3/1/2023"?

It can get more complicated, we may pull our invoices where our sales have a protocol for invoice description like this: "[service name] - Nov22". We will extract "Nov22" as the month part and we are expected to write "2022-11-01" in the cell. Or maybe "11/1/2022", or even "November 1, 2022".

Write Date - Bad Idea

In part1 we learned that converting string to date uses different time zone, depending on the format:

• "2023-02-26" will use UTC time zone
• "02/26/2023" will use the GAS project time zone
• "2023-02-26 00:00:00" will use the GAS project time zone
• "2023-02-26 " (with space) will use the GAS project time zone

These features are inherited from JavaScript, not funny JavaScript.

Time Zone Difference

With some hacks, we can see that we can ensure that we always use the same time zone: the GAS project time zone. We can avoid the UTC completely. If we get the "perfect date" string, we will just append the zero time part: " 00:00:00". Now we just need to find the difference between the GAS project and GS file time zones.

Unfortunately, reading the GS file time zone:

function test() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const timeZone = ss.getSpreadsheetTimeZone(); // GS file
  Logger.log(timeZone);
}

Gives us just a city:

No UTC offset, no daylight saving, nothing useful.

Currently, my GS file is in the CET time zone and my GAS project is in the PT time zone. If I write to the currently selected cell:

function test() {
  const str = "2023-03-01";
  const date = new Date(str + " 00:00:00");
  SpreadsheetApp.getActiveRange().setValue(date);
}

I will get:

If somehow I would know the time difference (9 hours) I could make it work:

function test() {
  const str = "2023-03-01";
  const date = new Date(str + " 00:00:00");
  date.setHours(date.getHours() - 9);
  SpreadsheetApp.getActiveRange().setValue(date);
}

PT zone enters daylight saving earlier than CET. There are periods when the difference is 8 hours. Furthermore, PT leaves the summer time later than CET.

Shifting the Timestamp

But wait a minute, we have the start string "2023-03-01 00:00:00", we know the format "yyyy-MM-dd HH:mm:ss". We have a Date variable (created in the GAS time zone).

Can't we just move that date plus/minus one hour until we get the same string in the GS file time zone? "Europe/Belgrade" will become very useful now, we will use it when calling the Utilities.formatDate method.

function moveDate(originalDate, expectedString, targetTimeZone, format) {
  const MINUTES_RESOLUTION = 60;

  let result = new Date(originalDate);
  let str = Utilities.formatDate(result, targetTimeZone, format);

  while (str != expectedString) {
    if (str > expectedString) {
      result.setMinutes(result.getMinutes() - MINUTES_RESOLUTION);
    }
    else {
      result.setMinutes(result.getMinutes() + MINUTES_RESOLUTION);
    }

    str = Utilities.formatDate(result, targetTimeZone, format);
  }

  return result;
}

And when we call it we get exactly what we need:

function test() {
  const inputString = "2023-03-01";
  const str = inputString + " 00:00:00";

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const gsFileTimeZone = ss.getSpreadsheetTimeZone();

  const date = new Date(str);
  const movedDate = moveDate(date, str, gsFileTimeZone, "yyyy-MM-dd HH:mm:ss");

  Logger.log(date);
  Logger.log(movedDate);

  SpreadsheetApp.getActiveRange().setValue(movedDate);
}

The GAS project is in the PT zone, GS file is in the CET zone.

If one of the time zones is X hours and 30 minutes from UTC: we will get an infinite loop!!!

But what about the performance? Yes, that could be a problem.

I have an idea: we may try with 60 minutes first, limit the number of iterations, then if the match is not found reduce to 30 minutes and so on.

Luckily, there is a better way.

Write String - Good Idea

The external system sends us a string, we are parsing it in the GAS time zone and writing it into the GS time zone. Having two time zones is causing problems.

Let's take advantage of the Google Sheets front-end feature: when the user enters the string - it "recognizes" the data type automatically:

When we just write the "2023-03-01" string to a cell - we will get the correct format and the correct Date value. This way the GS file time zone becomes irrelevant.

Let's assume we are getting the date string in the format "2022-12-31" but we need to write on several places, each one of them expecting the different formatting:

• Programmers: they want the perfect date, "2022-12-31", they don't even know the other formats exist.
• Managers: they want the normal date "12/31/2022", they don't even know the other formats exist.
• Accountants: they want it precise, time explicitly set to zero "12/31/2022 0:00:00" they rely on that in their formulas.
• Sales: they don't bother with the full year, but they entered some formulas, then they figured out the East/West Coast time difference, so they used duct tape and now they expect noon "12/31/22 12:00:00". They came up with the solution during the office party.

For each column, we know the format. Parsing string to a Date will be done in the GAS project time zone. We can get the GAS project time zone by calling the getScriptTimeZone function. All the components are there.

function test() {
  const str = "2022-12-31";
  const date = new Date(str + " 00:00:00");
  
  appendToReport(date);
}

function appendToReport(date) {
  const timeZone = Session.getScriptTimeZone();
  Logger.log(`timeZone=${timeZone}   date=${date}`);

  const sheet = SpreadsheetApp
    .getActiveSpreadsheet()
    .getSheetByName("Report");

  sheet.appendRow([
    Utilities.formatDate(date, timeZone, "yyyy-MM-dd"),
    Utilities.formatDate(date, timeZone, "MM/dd/yyyy"),
    Utilities.formatDate(date, timeZone, "MM/dd/yyyy 0:00:00"),
    Utilities.formatDate(date, timeZone, "MM/dd/yy 12:00:00"),
  ]);
}

appendToReport function does all the transformation and writing string to the sheet. It doesn't even bother to add 12 hours, it does that by formatting.

If the input string format is "2023-01-01 00:00:00" we don't even have to add the time part, we can convert it directly:

function test() {
  const str = "2023-01-01 00:00:00";
  const date = new Date(str);
  
  appendToReport(date);
}

If the input string is in "1/2/2023" format - we can use the same code.

If the input string is in "January 3 2023" format - we can use the same code.

Everything works fine, we don't care if the GS file and GAS project have different time zones... We don't care if the zone difference is in hours, in 30 minutes, 45 minutes... We don't care if one time zone entered daylight saving and the other didn't... We got to the level of the Honey Badger, we simply don't care.

We have the solution: all the "usual" formats of the input string will be converted to Date using the GAS time zone. The only exception is the "perfect date" format where we have to append the zero time part, to move it from the default UTC zone to the GAS project zone.

Then, when writing into the cell, we can prepare a string in the expected format using the GAS project time zone.

Two digit year

In the case of "Nov22" representing the start of the month, we just need to push it to some usual representation. For example into "Nov 1, 22":

function test() {
  const str = "Nov22";
  const month = str.slice(0, 3);
  const year = str.slice(3);
  const date = new Date(`${month} 1, ${year}`);
  
  appendToReport(date);
}

However two-digit year "22" is inferior to four-digit year "2022". When you pass the string to new Date(), it tries to "guess" the century:

• "Nov 1, 22" is parsed to Nov 01 2022
• "Nov 1, 99" is parsed to Nov 01 1999

But as you start exploring it you get to the switch point:

• "Nov 1, 49" is parsed to Nov 01 2049
• "Nov 1, 50" is parsed to Nov 01 1950

Just be aware of this, in the case of the invoices you probably won't have a problem. But if it's the date of birth then... ask the data provider for the year with four digits. Of course, in case you are sure that it is the 21st century only, you can force two digits to four digits yourself:

function test() {
  const str = "Nov50";
  const month = str.slice(0, 3);
  const year = "20" + str.slice(3);
  const date = new Date(`${month} 1, ${year}`);
  
  appendToReport(date);
}

Just a side note, "yy" being way less precise than "yyyy" is not even allowed in the perfect date format. new Date("22-12-31") would result in an "Invalid Date".

Questions, Corrections and Suggestions are appreciated: contact me.