In my scheduling spreadsheet, the start time of events is changing when I run the function.
I want the start time to match what is in the spreadsheet. The time zones match. It is different between the changes in time between the start and end times.
Here is the script I'm using right now:
/**
* Creates a Google Calendar with events for each conference session in the
* spreadsheet, then writes the event IDs to the spreadsheet for future use.
* @param {Array<string[]>} values Cell values for the spreadsheet range.
* @param {Range} range A spreadsheet range that contains conference data.
*/
function setUpCalendar_(values, range) {
let cal = CalendarApp.getCalendarById("c_3lgrub8iht0eg0bl20ekhf7uj4@group.calendar.google.com");
// Start at 1 to skip the header row.
for (let i = 1; i < values.length; i++) {
let session = values[i];
let title = session[0];
let date = session[1]
let startTime = joinDateAndTime_(session[1], session[3]);
let endTime = joinDateAndTime_(session[1], session[4]);
let options = {location: session[5], sendInvites: true};
let event = CalendarApp.getCalendarById("c_3lgrub8iht0eg0bl20ekhf7uj4@group.calendar.google.com").createEvent(title, startTime, endTime, options)
.setGuestsCanSeeGuests(false);
session[6] = event.getId();
}
range.setValues(values);
// Stores the ID for the Calendar, which is needed to retrieve events by ID.
let scriptProperties = PropertiesService.getScriptProperties();
scriptProperties.setProperty('calId', cal.getId());
}
/**
* Creates a single Date object from separate date and time cells.
*
* @param {Date} date A Date object from which to extract the date.
* @param {Date} time A Date object from which to extract the time.
* @return {Date} A Date object representing the combined date and time.
*/
function joinDateAndTime_(date, time){
var hrs = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'HH'));
var min = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'mm'));
var sec = Number(Utilities.formatDate(time,Session.getScriptTimeZone(),'ss'));
Logger.log('date = '+Utilities.formatDate(date, Session.getScriptTimeZone() ,'dd-MMM-yyyy HH:mm:ss'));
Logger.log('time = '+hrs+':'+min+':'+sec);
var dateAndTime = new Date(date).setHours(hrs,min,sec,0);
Logger.log('full date object = '+Utilities.formatDate(new Date(dateAndTime), Session.getScriptTimeZone() ,'dd-MMM-yyyy HH:mm:ss'))
return new Date(dateAndTime);
}