How to deal with three different time zones: in Sheets, Google Apps Script and Calendar?

Question

I want to get time with titles from cells (without time zones) and create events in a calendar.

Title/Subject   Start Times End Times
Board meeting   10/22/2017 13:00:00 10/22/2017 14:00:00
Team meeting    10/22/2017 10:00:00 10/22/2017 11:00:00

Cannot find method formatDate(string,string,string)

var data = sheet.getDataRange().getValues();

returns strings, after converting to date with a new Date(data[1][1]) I have a timezone from spreadsheet plus timezone from script. How do I insert it to calendar with different timezone just it to be 10/22/2017 13:00:00?

Answer 1

I understand you want the event time to look the same in the calendar as in the spreadsheet, despite all the time differences.

The first thing I'd do is to use getDisplayValues() instead of getValues(). This returns a string that matches what you see in the spreadsheet; not a date object. This takes the timezone of the spreadsheet out of the picture.

The timezone of the script is still in the picture, because it will be used when we parse the date string with new Date(). To reconcile the outcome with the calendar time zone (called calZone below), I do the following computation:

  var d = new Date();
  var d1 = new Date(Utilities.formatDate(d, calZone, "yyyy-MM-dd'T'HH:mm:ss"));
  var offset = d.valueOf() - d1.valueOf(); 

The idea is that d1 is the date d that is converted to text in calendar's timezone and then parsed again using the script zone. So the difference between d and d1 is the offset (in microseconds) between the two timezones. I use this offset to adjust the start and end times of the event.

The complete function, creating an event for the start/end datetimes provided in cells A1 and B1:

function createEvents() {
  var times = SpreadsheetApp.getActiveSheet().getRange("A1:B1").getDisplayValues();
  var cal = CalendarApp.getDefaultCalendar();
  var calZone = cal.getTimeZone();

  var d = new Date();
  var d1 = new Date(Utilities.formatDate(d, calZone, "yyyy-MM-dd'T'HH:mm:ss"));
  var offset = d.valueOf() - d1.valueOf(); 

  var start = new Date(new Date(times[0][0]).valueOf() + offset);
  var end = new Date(new Date(times[0][1]).valueOf() + offset);    
  cal.createEvent("test event", start, end);
}