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]);
    }
    }
  }
}

Answer 2

Well,

If I'm understanding correctly, you're looking to have a sheet of events, each with it's own identifier, and make sure they are always in sync.

If this is correct, my advice is to just use the event ID as your identifier on the sheet and when running your script, to only target events in the sheet without an identifier.

For getting the event ID, simple as:

// var event = eventCal.createEvent(nameevent, startTime, endTime, { description: desc });
const eventID = eventCal.createEvent(nameevent, startTime, endTime, { description: desc }).getId();

You could append it where you would like, though I think first or second column would be best.

Try:

function sendToCal() {

  const spreadsheet = SpreadsheetApp.getActive().getSheetByName('XCALENDAR')
  const eventCal = CalendarApp.getCalendarById("xxx");
  const signups = spreadsheet.getRange("A2:M").getValues();

  const targetEvents = signups.filter(i => i[0] === ``);

  for (let newEvent of targetEvents) {

    const rowIndex = signups.findIndex(i => i === newEvent);

    const [d, e, f, g] = [newEvent[3], newEvent[4].split(":"), newEvent[5], newEvent[6].split(":")];
    const [nameevent, desc, color] = [newEvent[1], newEvent[11], newEvent[12]];

    const startTime = new Date(d.getFullYear(),d.getMonth(),d.getDate(),parseInt(e[0]),parseInt(e[1]),0);
    const endTime   = new Date(f.getFullYear(),f.getMonth(),f.getDate(),parseInt(g[0]),parseInt(g[1]),0);
    
    const eventID = eventCal.createEvent(nameevent, startTime, endTime, { description: desc }).getId();

    const updatedRow = [eventID, ...newEvent];
    spreadsheet.getRange(rowIndex+2, 1, 1, updatedRow.length).setValues([updatedRow])

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

  }

}

The main changes are:

• Increasing the original signups range.
• Targeting only events without an ID (targetEvents).
• Increasing all indexed values by 1 due to new ID column.
• Update row after creating event to include ID.

If you have any issues please let me know!