google calendar, spreadsheet and script handle time zone differently

Question

I am trying to write a script to add events from Spreadsheet to Calendar. I found the time of added events is always 1 hour off. I checked the time zone settings and here's what I found.

Calendar: (GMT-04:00) Eastern Time Spreadsheet: (GMT-05:00) Eastern Time Script: (GMT-05:00) Eastern Time

So they are all Eastern Time, but the Calendar uses the Daylight Savings Time while the Spreadsheet and Script.

I know I can manually adjust GMT every time DST changes so that the resulting time is right. But is there a better way, so that I can set and forget?

Answer 1

Instead of hardcoding the timezone with GMT-04:00 use the literal expression of your geographic area (aka Canonical ID) as defined in this standard : Joda.org

this is what Google Apps Script is using.

You can also get that more simply using

SpreadsheetApp.getActive().getSpreadsheetTimeZone()

or from the calendar object : calendar.getTimeZone()

all these parameters will handle daylight savings automatically, which is not the case for the GMT+xx:xx method.

You shouldn't have any problem as long as both are on the same timezone. Calendars have a setTimeZone(timeZone) method and spreadsheets have a setSpreadsheetTimeZone(timeZone) method so it is easy to synchronize both

---

EDIT

Following comment

Since you seem to combine date and time in a unique date object and that the daylight saving puts you into trouble (because "pure time" values in spreadsheets are converted to a date JavaScript object automatically and with summer time correction), below is a small code that I use to workaround this issue. I added a few logs to show intermediate values;

function combineDateAndTime(){
  // in this example cell A1 has a date and cell B1 has time
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheets()[0];
  var dateOnly = sh.getRange('A1').getValue();
  var timeOnly = sh.getRange('B1').getValue();
  Logger.log('dateOnly object is : '+dateOnly+'\ntimeOnly object is : '+timeOnly);
  var hours = Number(Utilities.formatDate(timeOnly, 'GMT'+timeOnly.toString().split('GMT')[1],'hh'));
  var minutes = timeOnly.getMinutes();
  var completeDateAndTime = new Date(dateOnly.setHours(hours,minutes,0,0));
  Logger.log('completeDateAndTime is : '+completeDateAndTime);
}

Note : there are other ways to retrieve hour values, see this post for example.