Google sheets appscript to create calendar failing-CodePudding

I'm trying to create a calendar from the google sheets using appscript. I'm using trying below and getting error.

Here's what I'm trying to do. I want to create all the list of events in googlesheets to calendar, any further updates in sheet should update calendar only upon any recent entries/edit to the sheet and that particular event only.

Example: I've events from row A2: A15 in googlesheets, I want to create all at once. Post which if I add new row A16, it should create a calendar event for A16 details. Also, If I edit A10 row, then my existing calendar in A10 should update without spamming other calendar events.

Just for context: A1 = Title, B1 = Start date & time , C1 = End date & time, D1 = guest emails.

And I tried to declare the variable guest in for loop, it didn't work as well. Any suggestions or recommendations on how I can fulfill this.

function create_Events(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Calendar");
  var last_row = sheet.getLastRow();
  var data = sheet.getRange("A1:E"   last_row).getValues();
  var cal = CalendarApp.getCalendarById("CALENDARID");
  //Logger.log(data);

  for(var i = 0;i< data.length;i  ){
    //index 0 =
    var event = CalendarApp.getDefaultCalendar().createEvent(data[i][0],
    new Date(data[i][1]),
    new Date(data[i][2]),
    {guests: data[i][3]});

 Logger.log('Event ID: '   event.getId());

    
  }

}

CodePudding user response：

That works for me. I added a column with id (eventId) to be able to update in the future. The id will be also a good indicator to follow the process and prevent duplicates.

const myCalend = CalendarApp.getCalendarById("############@gmail.com");
const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');

function createCalendarEvent() {
  sheet.getDataRange().getValues().forEach((entry, i) => {
    if (entry[0] != '' && entry[4] == '') {
      let id = myCalend.createEvent(entry[0], entry[1], entry[2], { guests: entry[3] }).getId();
      sheet.getRange( i   1, 5).setValue(id)
    }
  });
}

CodePudding user response：

Try something like this

function create_Events() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Calendar");
  const vs = sh.getRange("A1:E"   sh.getLastRow()).getValues();
  const cal = CalendarApp.getDefaultCalendar();
  vs.forEach((r, i) => {
    if (r[0] && r[1] && r[3]) {
      cal.createEvent(r[0], new Date(r[1]), new Date(r[2]), { guests: r[3] });
    } else {
      Logger.log(i);
      Logger.log(JSON.stringify(r));
    }
  });
}