CODEWITHSUNDEEP
google-apps-script
toddnief
toddnief

Reputation: 21

Dealing with time zones in Apps Script when importing data into Google Sheets

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.

Upvotes: 2

Views: 986

Answers (1)

ale13
ale13

Reputation: 6062

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

Upvotes: 1

Related Questions