How to polyfill timezone support into a Luxon-enabled Google Apps Script project?

Trying to polyfill timezone support into a Luxon-enabled Google Apps Script project.

I am developing a small Google Apps Script to be able to create a Google sheet with a table that will include some time zones and the local timezone (as seen from the end-user standpoint). Something like this (should the user be located in London):

The idea that the user's location timezone would be automatically detected by the script, and returned by a function to populate the E2 cell. The E3 and E4 cells would be filled by hand with standard IANA timezone names, and another function in the script (say, "=TIME_AT(E4)", for example, at F4, would return the current time in Tokyo).

Please note that I need to work with the IANA strings, although the calculations for the different times could be done by offset. What I mean by that is that a user could perhaps add new lines to the sheet as new branches of the organization are created around the world (the E column validation will be set to only allow values from the IANA tz names list).

To do this, I decided to leverage Luxon, which has timezones support, but I am having a bit of trouble to make everything work.

I started by creating a file in my Google Apps project, named it luxon.js.gs, and copy-pasted all of the luxon library code. Then, following the instructions from the Luxon timezone manual, I tried to check whether the runtime supported IANA timezones with:

luxon.Info.features()

The only feature returned was "relative". So, once again following the timezone documentation from Luxon, I tried to "polyfill" the timezone support following the instructions here. It led me to the formatjs https://github.com/formatjs/date-time-format-timezone page. But from there I am not sure what to do. I tried to copy-paste the js file into the project, but when I tried to check for the luxon.Info.features as above I got this:

12:11:08 PM	Error	
ReferenceError: window is not defined
(anonymous)	@ date-time-format-timezone-complete-min.js.gs:74
7../code/data-loader.js	@ date-time-format-timezone-complete-min.js.gs:74
e	@ date-time-format-timezone-complete-min.js.gs:1
a	@ date-time-format-timezone-complete-min.js.gs:1
(anonymous)	@ date-time-format-timezone-complete-min.js.gs:1

I didn't get exactly what that means, but does it mean that I cannot do this polyfilling because the runtime is not a browser one? If so, how to solve it?

Thank you.

I believe your goal is as follows.

• In your Spreadsheet, your current time is put into the cell "F2".
  • Unfortunately, I cannot know whether your value of "F2" is the date object. So, in this answer, I suppose that the value of "F2" is the date object like =NOW(). Please be careful about this.
• The cells "E2:E" has the valid timezones of IANA.
• You want to retrieve the cells "E2:E", and want to convert your current time and put in column "F" using the timezone and the value of "F2".

I thought that in your situation, formatDate(date, timeZone, format) might be able to be used. When this is used, how about the following sample script?

Sample script 1:

In this sample, it supposes that the cell "F2" is the date object.

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const [base, ...values] = sheet.getRange("E2:F" + sheet.getLastRow()).getValues();
  const b = base[1];
  const res = values.map(([e]) => [new Date(Utilities.formatDate(b, e, "yyyy-MM-dd'T'HH:mm:ss"))]);
  sheet.getRange(3, 6, res.length).setValues(res).setNumberFormat("hh:mm");
}

• When this script is run, the converted values are put into cells "F2:F".

Sample script 2:

In this sample, it supposes that the cell "F2" is the string.

function myFunction() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const [base, ...values] = sheet.getRange("E2:F" + sheet.getLastRow()).getDisplayValues();
  const b = Utilities.parseDate(new Date().toISOString().split("T")[0] + base[1], base[0], "yyyy-MM-ddHH:mm");
  const res = values.map(([e]) => [Utilities.formatDate(b, e, "HH:mm")]);
  sheet.getRange(3, 6, res.length).setValues(res).setNumberFormat("hh:mm");
}

• When this script is run, the converted values are put into cells "F2:F".

• In this script, if you want to put the values to column "F" as the date object including year, month, and day, please modify as follows.

  • From

      const res = values.map(([e]) => [Utilities.formatDate(b, e, "HH:mm")]);
    

  • To

      const res = values.map(([e]) => [new Date(Utilities.formatDate(b, e, "yyyy-MM-dd'T'HH:mm:ss"))]);
    

Note:

• When I tested these scripts using the situation of your provided sample Spreadsheet image, I confirmed that a value of 16:56 with Europe/London is converted to 11:56 and 00:56 for America/New_York and Asia/Tokyo, respectively.
• This sample script is for the Spreadsheet of your showing image. When you change the Spreadsheet, this script might not be able to be used. Please be careful about this.

References:

• map()
• formatDate(date, timeZone, format)

Added:

From your following reply,

> Would it be possible to create a formula like "function TO_TIMEZONE(time, timezone)" that could be used in each result cell, referring to the cell that has the current time and the cell that has the target timezone?

How about the following sample script?

const TO_TIMEZONE = (time, timezone) =>
  new Date(Utilities.formatDate(time, timezone, "yyyy-MM-dd'T'HH:mm:ss"));

• In this case, when your showing sample image is used, please put a custom function like =TO_TIMEZONE($F$2,E3) and =TO_TIMEZONE($F$2,E4) into cell "F3" and "F4", respectively. By this, when "F2" is 16:56 as the date object, 11:56 and 00:56 are obtained.
• Although, unfortunately, I cannot still understand whether your cell value of "F2" is the date object, in this sample, it supposes that "F2" is the date object. Please be careful about this.