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
?
Best Answer
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 ofgetValues()
. 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 (calledcalZone
below), I do the following computation:The idea is that
d1
is the dated
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: