Sync Google Sheet and Google Calendar by ID preventing duplicates

Question

With this code I could put the events from google spreadsheet to google calendar, but now I would to sync these events between the two platforms by a unique ID I could store in the spreadsheet.

First of all, I am trying to set a trigger every 5 minutes, but I don't know if it is the best solution, considering also that the events on the sheet are more than 300 (and they could increase, but with 5 or 6 new events every day) and so I would avoid the error Error: "You have been creating or deleting too many calendars or calendar events in a short time."

The second thing I am trying to do is to call the id from the google calendar to the column A of the sheet but without success.

How could I proceed?

function sendToCal() {
  var spreadsheet = SpreadsheetApp.getActive().getSheetByName('XCALENDAR')
  let eventCal = CalendarApp.getCalendarById("xxx");
  var signups = spreadsheet.getRange("A2:M").getValues();

  for (x = 0; x < signups.length; x++) {
    var shift = signups[x];

    let d = shift[3]
    let e = shift[4].split(":")
    let f = shift[5]
    let g = shift[6].split(":")

    var startTime = new Date(d.getFullYear(),d.getMonth(),d.getDate(),parseInt(e[0]),parseInt(e[1]),0);
    var endTime   = new Date(f.getFullYear(),f.getMonth(),f.getDate(),parseInt(g[0]),parseInt(g[1]),0);
    var nameevent = shift[1];
    var desc = shift[11];
    var color = shift[12];
    var event = eventCal.createEvent(nameevent, startTime, endTime, { description: desc });

    if (color) {
      event.setColor(CalendarApp.EventColor[color]);
    }
  }
}

Answer 1

Try with limited signups (with getLastRow()) and getDisplayValues() to prevent any issue with formatting (assuming your dates are dd/MM/yyyy)

function sendToCal() {
  var spreadsheet = SpreadsheetApp.getActive().getSheetByName('XCALENDAR')
  let eventCal = CalendarApp.getCalendarById("xxx");
  var signups = spreadsheet.getRange("A2:M" + spreadsheet.getLastRow()).getDisplayValues();

  for (x = 0; x < signups.length; x++) { 
    var shift = signups[x];
    if (shift[0]==''){
    let d = shift[3].split("/")
    let e = shift[4].split(":")
    let f = shift[5].split("/")
    let g = shift[6].split(":")

    var startTime = new Date(parseInt(d[2]), parseInt(d[1])-1, parseInt(d[0]), parseInt(e[0]), parseInt(e[1]), 0)
    var endTime = new Date(parseInt(f[2]), parseInt(f[1])-1, parseInt(f[0]), parseInt(g[0]), parseInt(g[1]), 0)
    var nameevent = shift[1];
    var desc = shift[11];
    var color = shift[12];
    var event = eventCal.createEvent(nameevent, startTime, endTime, { description: desc });
    spreadsheet.getRange('A' + (+x + 2)).setValue(event.getId())
    if (color) {
      event.setColor(CalendarApp.EventColor[color]);
    }
    }
  }
}