How to getValue() on a date with the correct TimeZone?

0
votes

Someone in the "Europe/London" (UTC +0) Timezone created a Spreadsheet. They input the date "March 22 2019" into one of the fields. This represents the date 3/22/2019 0:00 UTC+O.

I'm in the "America/Los_Angeles" (UTC -7) Timezone. When I run a Google Apps Script and try to get the value, it's evaluated as:

Thu Mar 21 17:00:00 GMT-07:00 2019

This is accurate since 3/22/2019 0:00 UTC+O == 3/21/2019 17:00 UTC-7.

The problem is when I try to get the date programmatically, I'll get it as 21 instead of 22.

For example, if I try to run:

cellRange.getValue().getDate() // returns 21 even though the sheet shows the date as 22

This is because getDate() returns values "according to local time." The local script time is UTC-7, hence it's returning 21.

However, this causes a dissonance:

  • Spreadsheet date: 22 (i.e. the value I see in the sheet)
  • Programmatic date: 21 (i.e. the one returned by getDate() above)

This is problematic when I'm trying to use a function such as joinDateAndTime_() with "March 22 2019" as the date and 6am as the time, for example. This causes it to produce a date "March 21 2019 6:00 UTC-7" instead of "March 22 2019 6:00 UTC-7".

What is the best solution here?

1
datetimegoogle-apps-scriptgoogle-sheetstimezone
Use UTC? Don't allow ambiguity in date representations? – tehhowch

1 Answers

0
votes

Not a complete solution (I'll update soon)

It seems like this is what is happening:

  1. The value is hard-coded as "March 22" (text).
  2. When a user opens the sheet, no matter what timezone they are in, it'll assume it represents March 22 in the sheet's timezone. 3/22/2019 0:00 UTC+0
  3. Once you read the value into a JavaScript Date, all date functions assume you want it in your current (aka the script's) timezone. 3/21/2019 17:00 UTC-7

Solution A: Just add the hours

Forget about the timezones. Instead of hardcoding the hours in a Date, just offset the date by the hours you want.

The only downside is you need to be certain that the date started at 0:00 according to whatever timezone it was in. (E.g. if they decided to write "March 22 2019 5:00", then you'll be offsetting the hours incorrectly.)

Solution B: Do some math

I'll update this soon, but eventually you might want a function sheetTimezoneOffset() that could be used like this:

function getDate(cellRange) {
    var date = cellRange.getValue().getDate();
    var extraneousHours = formatDate(date, "h", sheetTimezoneOffset());
    date = date.addHours(-extraneousHours);
    var offsetHours = 6; // e.g. for 6am
    date.addHours(offsetHours);
    return date;
}