Prevent Duplicate Calendar Events Apps Script

[Megan Moore]

Hi All, 

I am trying to create calendar events from a spread sheets. So far I have been able to sync the data and update sheets to run the script without having to open Apps Script. I am struggling to figure out how to update the events without creating duplicates. Here is my code so far. Any advice would be greatly appreciated! 

function addevents() {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("AddEvents");

var cal = CalendarApp.getOwnedCalendarById("calendarID");

var last_row = sheet.getLastRow();

var data = sheet.getRange("A2:J" + last_row).getValues();

for(var i = 0;i<data.length;i++){

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

    }

}

function onOpen() {

var ui = SpreadsheetApp.getUi();

ui.createMenu('Sync to Calendar')

.addItem('Sync Now',

'addevents')

.addToUi();

}

[CBMServices Web]

Hi Megan,

I also create lots of calendar events. I try to keep it simple and if I need to update an event, I just delete the old one and create a new one. There is a problem with that approach because Google does not send event delete notices to event participants when the event is deleted. But luckily I don't typically have to do that very often.

What I would suggest is for you to get the event id and save it in the same row as your source data for the event. If the data for that row changes, then you can do like me, grab the old event ID and delete it before creating a new one or try to update it with new info. Either way, you would need the event ID to make it easier to do that.

Hope that helps.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/bb7c340e-bca4-40e7-a96e-e367468bfc5en%40googlegroups.com.

[Laurie J. Nason]

I think that keeping the id from your google calendar against your record in the google sheet is probably the easiest way to do it if you are able to.

I use a slightly different method and when I create the event in the calendar, I use ’setTag’ to mark it as one of my events - where you could use the unique key from your google sheet to work out which event to update/delete (or all of them!)

Laurie

------ Original Message ------

From "CBMServices Web" <cbmserv...@gmail.com>

To google-apps-sc...@googlegroups.com

Date 20/09/2022 03:00:56

Subject Re: [Apps-Script] Prevent Duplicate Calendar Events Apps Script

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/CAFX%2Bz3WZq_pBYRKzWHPkTNV6GMv5pmua_%3Dy5VPTJCb7C8FAAOA%40mail.gmail.com.

[Jim Willeke]

We started doing this.

First we gather the data into an Object

var request = new Submission(row);

Then check for conflicts

getConflicts(request);

/**

* Creates an object from the provided row

*/

function Submission(row) {

this.row = row;

// when reservation form was filled out

this.timestamp = aSheet.getRange(row, 1).getValue();

// email who filled out form

this.email = aSheet.getRange(row, 2).getValue();

// Displayed Description

this.eventDisplayed = aSheet.getRange(row, 3).getValue();

// data of event

var eventDate = new Date(aSheet.getRange(row, 4).getValue());

// fix event start

var startTime = aSheet.getRange(row, 5).getValue();

this.startTimestamp = new Date(eventDate.getFullYear(), eventDate.getMonth(), eventDate.getDate(), startTime.getHours(), startTime.getMinutes());

//use this for formatting

this.dateString = Utilities.formatDate(this.startTimestamp, 'America/New_York', 'MMMM dd, yyyy');

//this.timeString = this.startTimestamp.toLocaleTimeString();

// fix endtime

var eventTimeEnd = calendarSheetName.getRange(row, 6).getValue(); // eventTimeEnd(F)

this.endTimestamp = new Date(eventDate.getFullYear(), eventDate.getMonth(), eventDate.getDate(), eventTimeEnd.getHours(), eventTimeEnd.getMinutes());

// Displayed Description

this.eventDescription = aSheet.getRange(row, 7).getValue();

// contact phone

this.name = aSheet.getRange(row, 9).getValue();

// contacts name

this.name = aSheet.getRange(row, 10).getValue();

}

Where request is:

/**

* Submit a request object to getConflicts() and it will

* - create the event if there are no conflicts

* - send an email

*/

function getConflicts(request) {

var conflicts = eventCal.getEvents(request.startTimestamp, request.endTimestamp);

if (conflicts.length < 1) {

request.status = "Approve";

// update sheet

upDateSheetWithStatus(request);

createEventFromRequest(request);

// create email message

draftEmail(request);

// send email

sendEmail(request);

} else {

request.status = "Conflict";

Logger.log(request);

// update sheet

upDateSheetWithStatus(request);

// create email message

draftEmail(request);

// send email

sendEmail(request);

}

Logger.log(request);

}