datetime google-sheets google-apps-script calendar google-calendar-api

Issue in creating calendar events in user timezone using google apps script?

I have a script in a google sheet that creates events in a calendar. The Google sheet time zone is GMT-5 while in the script editor timezone is GMT-4, now the issue is my timezone is GMT+5 when I run this event creation script it does not create an event based on my timezone rather it does it according to google sheet/script timezone.

I am able to get user timezone and convert the startTime and endTime accordingly, Here is the sample script:

 function run() {
    
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var customizeSheet =spreadsheet.getSheetByName("✨ CUSTOMIZE PACE");
  
  var calendarData = customizeSheet.getRange("A2:I2").getDisplayValues()[0];
  var calendar = CalendarApp.getCalendarById("CalendarID");
  
  var timeZone = getUserTimeZone();
  calendar.setTimeZone(timeZone);
  var timeZoneOffset = getTimeZoneOffset(timeZone);

  var eventDate = new Date(calendarData[1]);
  createEventP(calendarData[0],eventDate, calendarData[2], calendarData[4], calendarData[6],calendar,timeZoneOffset)
}

function createEventP(eventName, eventDate, startTime, endTime, eventDescription, calendar,timeZoneOffset) {
 
  var startDate = Utilities.formatDate(eventDate,  "GMT"+timeZoneOffset, 'yyyy-MM-dd') + ' ' + startTime + ' ' + "GMT"+timeZoneOffset;
  var endDate = Utilities.formatDate(eventDate,  "GMT"+timeZoneOffset, 'yyyy-MM-dd') + ' ' + endTime + ' ' + "GMT"+timeZoneOffset;
  var options = {
    description: eventDescription
  };
  calendar.createEvent(eventName, new Date(startDate), new Date(endDate), options);
}

function getUserTimeZone() {
  var userTimeZone = CalendarApp.getDefaultCalendar().getTimeZone();
  return userTimeZone;
}

function getTimeZoneOffset(timeZone) {
  var now = new Date();
  var timeZoneOffset = Utilities.formatDate(now, timeZone, 'Z');
  return timeZoneOffset;
}

The following lines convert to the user timezone:

 var startDate = Utilities.formatDate(eventDate,  "GMT"+timeZoneOffset, 'yyyy-MM-dd') + ' ' + startTime + ' ' + "GMT"+timeZoneOffset;
 var endDate = Utilities.formatDate(eventDate,  "GMT"+timeZoneOffset, 'yyyy-MM-dd') + ' ' + endTime + ' ' + "GMT"+timeZoneOffset;

but the issue is with the following line, it converts the user timezone to script timezone again due to the usage of date object new Date():

calendar.createEvent(eventName, new Date(startDate), new Date(endDate), options)

Any help to resolve this issue would be much appreciated. Please let me know if you need any additional information.

Solution

In Google Sheets, datetimes and durations are stored as dateserial values. When you read the value in a date, time, datetime or duration cell in Apps Script, the dateserial is automatically converted to a JavaScript Date object.

From the documentation:

A JavaScript date is fundamentally specified as the time in milliseconds that has elapsed since the epoch, which is defined as the midnight at the beginning of January 1, 1970, UTC (equivalent to the UNIX epoch). This timestamp is timezone-agnostic and uniquely defines an instant in history.

So perhaps no conversions are needed in the first place. Just use Range.getValues() instead of Range.getDisplayValues() to get Date objects instead of strings that look like dates. Try something like this:

function run() {
  const calendar = CalendarApp.getCalendarById('...put ID here...');
  if (!calendar) throw new Error('Cannot find calendar by that ID.');
  const eventData = SpreadsheetApp.getActive()
    .getRange('✨ CUSTOMIZE PACE!A2:I2')
    .getValues()
    .flat();
  createEventP(calendar, eventData);
}

function createEventP(calendar, eventData) {
  const [eventName, eventDate, startTime, unused, endTime, eventDescription] = eventData;
  const startMoment = new Date(eventDate.getTime() + startTime.getTime());
  const endMoment = new Date(eventDate.getTime() + endTime.getTime());
  calendar.createEvent(eventName, startMoment, endMoment, { description: eventDescription });
}

See these resources: