2
votes

I'm having some trouble getting time zones to play nicely with a custom Sheets extension when importing data into Google Sheets.

It seems that there is sometimes a mismatch between time zones that results in incorrect behavior.

Here's my code to try to combine some date data columns in order to display a properly formatted date:

   activeSheetTimeZone = Session.getScriptTimeZone();
  
  //Merge dates into start and end only
  var range1 = sheet.getRange(2,shiftDayIndex,sheet.getLastRow()-1,1).getValues().map(date => Utilities.formatDate(date[0], activeSheetTimeZone,"MMM d"));
  var range2 = sheet.getRange(2,shiftStartIndex,sheet.getLastRow()-1,1).getValues().map(date => Utilities.formatDate(date[0], activeSheetTimeZone,"h:mm a"));
  var range3 = sheet.getRange(2,shiftEndIndex,sheet.getLastRow()-1,1).getValues().map(date => Utilities.formatDate(date[0], activeSheetTimeZone,"h:mm a"));

When importing a .csv file, this causes the dates to be shifted inappropriately (looks like it's treating the imported data as GMT +0 and adjusting it to Central Time according to the project settings).

However! If the user does certain things in the imported sheet, it seems to synchronize the imported data to be in Central Time, so the formatted dates show up correctly. It's not totally clear to me which actions cause the sync and which ones don't. It's also not clear to me if I can somehow force the "time zone sync" to occur in the scripting for the custom extension.

Does anyone have a way to make sure that there aren't time zone conflicts when using custom Google Sheets extensions?

Edit: Here's How to Fix This

As Ale13 says below "You can also change the time zone programmatically by using the setSpreadsheetTimeZone(timezone) method."

This is the solution here.

This issue is not caused by a script/sheet time zone mismatch, since both getScriptTimeZone and getSpreadsheetTimeZone result in the same behavior.

Instead, it seems that imported data can exist in a "phantom UTC" state until some sort of action is taken on the imported data to cause it to sync with the Spreadsheet's time zone.

Adding a line to the extension with setSpreadsheetTimeZone solves the issue.

1
The question I have is what is correct behavior. I did a project where the people in the field wanted the field notes to reflect the correct time of day for the current location that they were working in. So we collected all dates as strings and posted them as if they were collected in Houston thus neglecting all time zone considerations. However, a manager might want to know what time it was in Houston when that oil refinery caught fire. So what's the correct behavior?Cooper
Session.getScriptTimeZone() returns the script's time zone, not the spreadsheet's. Take a look at developers.google.com/apps-script/reference/spreadsheet/…Diego

1 Answers

1
votes

The script's time zone is different from the spreadsheet's time zone. Hence, the two methods will return two different values if the time zones are not the same.

So depending on this, when you import the .csv file in your spreadsheet, they will be converted to the spreadsheet's time zone.

This fact is also mentioned in Google's official documentation.

Note that spreadsheets have a separate time zone, which can be changed by clicking File > Spreadsheet settings in Google Sheets. Spreadsheet time zones that differ from the script time zone are a frequent source of scripting bugs.

You can also change the time zone programmatically by using the setSpreadsheetTimeZone(timezone) method.

Reference