Trying to create an automation from google sheets to google calendar while avoiding duplicate event entries

Question

I am very new at this and totally lost. I am trying to create a spreadsheet to use in my business with scheduling information that I could automatically send to google calendars to create an event. I would like to be able to add a trigger to the spreadsheet to update this spreadsheet manually as to capture new events and add them to google calendar as well without having to erase the previous information. In other words, I would like to have it just add new content or if it works better, delete all events and recreate events each time it runs. I have come up with the basics, but I have been having a few issues.

1st issue I am having is I am receiving the following error:

Error
Exception: The parameters (String,String,String,(class)) don't match the method signature for CalendarApp.Calendar.createEvent. addEvents @ Scheduling.gs:38

2nd issue I am having is:

Even with the error above, the script does run, however, each time it runs it creates a new event leaving me with several duplicated events.

Would anyone be able to help a very very code illiterate person with how to fix this?

Here is a copy of the script I have:

function myFunction() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var cal = CalendarApp.getOwnedCalendarById("calendaridhere");
  var events = cal.getEvents(new Date("07/01/2022"), new Date("07/31/2022"));
  
  var lr = ss.getLastRow();
  
  for(var i = 0;i<events.length;i++){

    var title = scheduling[i].getTitle();
    var sd = scheduling[i].getStartTime();
    var ed = scheduling[i].getEndTime();
    var loc = scheduling[i].getDescription();

    ss.getRange(i+2, 1).setValue(title);
    ss.getRange(i+2, 2).setValue(sd)
    ss.getRange(i+2, 2).setNumberFormat("mm/dd/yyyy");
    ss.getRange(i+2, 3).setValue(ed);
    ss.getRange(i+2, 3).setNumberFormat("mm/dd/yyyy");
    ss.getRange(i+2, 4).setValue(loc);
    ss.getRange(i+2, 5).setValue(des);


}


}

function addEvents(){
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  var lr = ss.getLastRow();
  var cal = CalendarApp.getOwnedCalendarById("scheduling.ohiolegacy@gmail.com");
  
  var data = ss.getRange("A2:E" + lr).getValues();

  for (var i = 0;i<data.length;i++){
      
      cal.createEvent (data[i][0], data[i][1], data[i][2], {location:data[i][3]});


  }

}

My spreadsheet is very basic, but here is an example:spreadsheetexample

Any help or suggestions would be GREATLY appreciated! Please though, I am new and honestly need a lot of guidance to understand what I am doing.

Thank you!

Answer 1

Try

function addEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  var cal = CalendarApp.getOwnedCalendarById("#######@gmail.com");
  var data = ss.getRange("A2:E" + lr).getValues();
  for (var i = 0; i < data.length; i++) {
    if (data[i][4] == ''){
      var d = new Date(data[i][2].getFullYear(),data[i][2].getMonth(),data[i][2].getDate() + 1)
      ss.getRange('E' + (+i + 2)).setValue(cal.createEvent(data[i][0], data[i][1], d, { location: data[i][3] }).getId())
    };
  }
}

• the event id will be stored in column E if free
• check if columns C and D are dates (and not strings)
• this will also includes the end date inside the calendar