[UPDATED]: To create a google calendar event using AppsScript we can use the simple method of createEvent(eventName, startTime, endTime). I have worked with this and did not have any issues. But the only date format I have used was the long date/time format in the google sheets (e.g. 3/9/2021 13:30:00).

Using the code below I want to combine date and time columns into a single Date object to feed into createEvent() method but get and error of Invalid Date.

function calendarSyncNew() {
  var dataSheet = mySheet;
  var calendarId = calendarId;
  var eventCal = CalendarApp.getCalendarById(calendarId);
  var eventArray = dataSheet.getRange('A2:E').getValues();

  for (x = 0; x < eventArray.length; x  ) {

    var event = eventArray[x];
    var eventName = event[3];
    var eventDate = event[0];
    var timeZone = event[4];
    var startTime = new Date(`${eventDate} ${event[1]} ${timeZone}`); 
    var endTime = new Date(`${eventDate} ${event[2]} ${timeZone}`);
    eventCal.createEvent(eventName, startTime, endTime);
    Logger.log(`startTime is: ${startTime}`);
    Logger.log(`endTime is: ${endTime}`);
  }
    
}
    

CodePudding user response：

I assume this question is related to your previous question? If that's the case, kindly see the sample data I used and the script below:

Sample Data:

Script:

  // use getDisplayValues for getting the string equivalent of the data instead
  // filter out rows without values in column A
  var eventArray = dataSheet.getRange('A2:I')
                            .getDisplayValues()
                            .filter(x => x[0] != '');

  eventArray.forEach(event => {
    var eventName = event[4];
    var startTime = new Date(`${event[0]} ${event[1]} ${event[3]}`);
    var endTime = new Date(`${event[0]} ${event[2]} ${event[3]}`);
    eventCal.createEvent(eventName, startTime, endTime);
  });

Output:

Output was adjusted to my timezone so it doesn't reflect the dates from the sample data properly but it checks out.