CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-calendar-api
user18154470
user18154470

Reputation: 1

Google Sheets to Google Calendar - Prevent Duplicate Events

I am working on the development of a shared Google calendar that will pull information from a single, shared Google sheet. The following script was developed based on this video:

function createCalendarEvent() {
  let communitycalendar = CalendarApp.getCalendarById("[email protected]");
  let sheet = SpreadsheetApp.getActiveSheet();

  let schedule = sheet.getDataRange().getValues();
  schedule.splice(0,2);
  
  schedule.forEach(function(entry) {
    communitycalendar.createEvent(entry[0], entry[1], entry[2]);

});
}

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Sync to Calendar')
      .addItem('Add appointments', 'createCalendarEvent')
      .addSeparator()
      .addSubMenu(ui.createMenu('Delete')
          .addItem('Delete shifts from Calendar', 'clearCalendar'))
      .addToUi();
function createCalendarEvent() {
}

function clearCalendar() {
}};

This script pulls data from a basic test sheet, for now. The issue is that this creates duplicate entries every time the script is run. I understand this is a common issue that has been resolved in other stack overflow threads, but these solutions prevent this issue by having the script check an eventID and replace the prior calendar invite rather than duplicating. Since my calendar is a shared calendar with events that people will be signing up for, this would get confusing to my coworkers and instead, it would be best to have a script that checks for preexisting calendar invites and does not create a new invite if a prior one already exists (based on say, unique event ID or title).

The basic logical structure for this piece of code would be

IF eventID/title is on calendar already, DONT create new event.

Does such a solution exist? I am a novice programmer so I am not sure how to write such a script but I have done my due diligence to check around stack overflow and I cannot find a solution.

Thank you for your help!

Upvotes: 0

Views: 922

Answers (1)

Tanaike
Tanaike

Reputation: 201378

About your logic of IF eventID/title is on calendar already, DONT create new event., from your showing sheet image, I cannot see the event ID. And from based on say, unique event ID or title, in this answer, I used the event title for checking the duplicate events. When your script is modified, how about the following modification?

Modified script:

In this modified script, in order to retrieve all events, Calendar API is used. So please enable Calendar API at Advanced Google services.

function createCalendarEvent() {
  const calendarId = "[email protected]";
  let communitycalendar = CalendarApp.getCalendarById(calendarId);
  let sheet = SpreadsheetApp.getActiveSheet();
  let schedule = sheet.getDataRange().getValues();
  schedule.splice(0, 2);
  const events = {};
  let pageToken = "";
  do {
    const {items, nextPageToken} = Calendar.Events.list(calendarId, {maxResults: 2500, pageToken});
    if (items.length > 0) items.forEach(o => events[o.summary] = true);
    pageToken = nextPageToken || "";
  } while (pageToken);
  schedule.forEach(function (entry) {
    if (!events[entry[0]]) {
      communitycalendar.createEvent(entry[0], entry[1], entry[2]);
    }
  });
}
  • When this script is run, at first, all events are retrieved and create an object for searching the event title. And, when the event title has already been existing, the event is not created.

Reference:

Upvotes: 1

Related Questions