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);
}