Finding if an event is in a calendar, and if not, making a new event

Question

I'm making an app that adds events to a calendar from a spreadsheet, and then updates the events if the spreadsheet changes. Right now, my code just deletes all the existing events and adds a new one for every entry in the spreadsheet, but sometimes my code errors out because that means I'm adding and deleting too many calendar entries. (Yes, I'm using the sleep utility. Sometimes it crashes anyway.)

It seems like a more efficient way to do this would be to check if the event already exists; if it does, update any changes in the date; and if it doesn't, then create a new event. But I can't quite get my head around how to loop the two lists over each other. If I tell it to loop over all events in the spreadsheet, and inside that to loop over each event in the calendar to look for a match, and if there's no match to make a new event, it seems like it will create a new event for every non-matching item that exists in the calendar for every row of the spreadsheet.

Here's the basic code:

function UpdatePrintCalendar() {
 SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/16U6am5GvgWxh0gcRS8FWf9ws7BxaiJU5KOnSo7kOUlg/edit?usp=sharing&key=AIzaSyBe4KN8M-HhMUbN0UcAao7Xm4rIMgOOC6g")
  .getSheetByName("PD Hed List").sort(3).sort(4);
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var cols = sheet.getLastColumn();
  var endofthings = sheet.getLastRow();
  var stories = sheet.getRange(1,1, endofthings, cols).getValues();
  var rownum = stories.length
  var printCal = CalendarApp.getCalendarById("4d1aq2jtt795hfemn7i76cfi30@group.calendar.google.com");
  var fromDate = new Date(2019,11,1,0,0,0);
  var toDate = new Date(2020,3,31,0,0,0);
  var events = printCal.getEvents(fromDate, toDate);
  for(var i=0; i<events.length;i++){ /* Clear the calendar. This prevents duplicates. */
      var ev = events[i];
      ev.deleteEvent();
    }



  for (x=0; x<stories.length; x++) {
    if (x % 10 == 0) { Utilities.sleep(3000); }
      var story = stories[x];
      var onlinedate = story[1];
      var printdate = story[2];
      var section = story[5]; 
      var slug = story[6];

      var hed = {
          'location': section,
          'description': slug,
      }
      if (Boolean(printdate)) {
        if (slug) {
          printCal.createAllDayEvent(slug, new Date(printdate));   
        }
      }

  }

}


}

Answer 1

You can try Array.prototype.filter()

Take a look how to use it on example1 and example2

What I would do in your case:

1. list all calendar events
2. find id and title
3. list titles in Spreadsheet
4. find what is not match
5. add missing calendar events
6. update spreadsheet.