Google Sheets 2 Google Calendar - Unable to apply previously posted fixes for duplicate entries

Question

I am trying to create a sheet that can export entries to Google Calendar. I have done this! Unfortunately, each time I run the code it duplicates every single entry in the calendar.

I have read through the other threads that manage this issue, but I am a novice and my code is very different from the others. I also am unsure how to create a "unique ID" that can be used to manage the duplicates by checking for entries by ID.

I wonder if someone can help by reviewing my code and sheet? I have removed my calendar ID, so you will need to add your own to test the code/sheet.

/**
 * Import events from Google Calendar into Google Sheets
 */

function getEvents() {

var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var cal = CalendarApp.getCalendarById("[INSERT CAL ID HERE]");
var events = cal.getEvents(new Date("7/1/2022 12:00 AM"), new Date("7/31/2022 11:59 PM")); //array of events - uses square brackets


var lr = ss.getLastRow(); // clean up spreadsheet - clear content when events are removed from the calendar
ss.getRange(2, 1, lr-1, 5).clearContent(); // first row of data, first column of data, lr-1, number of columns of data 
//Error when blank - must have at least one row of text. Row 2 cannot be blank on first run.


for(var i = 0;i<events.length;i++){ //loop through all of the events to find how many things are in the array and run the function for all of them 

  var name = events[i].getTitle(); //first event i=0 - this will run through entries in the timeframe specified and get all titles
  var st = events[i].getStartTime();
  var et = events[i].getEndTime();
  var loc = events[i].getLocation();
  var des = events[i].getDescription();

  ss.getRange(i+2, 1).setValue(name); //assign the variable to a column/location in the sheet. Change the number on each line to change to assign to a new column
  ss.getRange(i+2, 2).setValue(st);
  ss.getRange(i+2, 2).setNumberFormat("mm/dd/yyyy h:mm:ss AM/PM"); //set date formatting for st
  ss.getRange(i+2, 3).setValue(et);
  ss.getRange(i+2, 3).setNumberFormat("mm/dd/yyyy h:mm:ss AM/PM"); //set date formatting for et
  ss.getRange(i+2, 4).setValue(loc);
  ss.getRange(i+2, 5).setValue(des);
  
  }

}

/**
 * Export event entries from Google Sheets to the Google Calendar
 */

function addEvents() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
    var cal = CalendarApp.getCalendarById("[INSERT CAL ID HERE]");

  var data = ss.getRange("A2:E" + lr).getValues(); //concatentation

  for(var i = 0;i<data.length;i++){ 
    
    //Logger.log(data[i]); 
    // [i] moves each entry to a new line, [0] selects only the column specified

      cal.createEvent(data[i][0], data[i][1], data[i][2], {location: data[i][3], description: data[i][4]});

  }

}

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item for invoking function addEvents()
 */

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Calendar Sync')
    .addItem('Sync now', 'addEvents')
    .addToUi();

}

Here is a snip of the sheet:

Google Sheet Sample to Export to Calendar

Answer 1

To prevent any duplicates, save the id of the event by

let id = cal.createEvent(data[i][0], data[i][1], data[i][2], {location: data[i][3], description: data[i][4]}).getId()

and save in column F for instance (if F is free)

ss.getRange(i+2, 6).setValue(id)

So expand data as follows

var data = ss.getRange("A2:F" + lr).getValues();

and when you loop for(var i = 0;i<data.length;i++), check if (data[i][5] == '')