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.
Session.getScriptTimeZone()
returns the script's time zone, not the spreadsheet's. Take a look at developers.google.com/apps-script/reference/spreadsheet/… – Diego