Create Google Calendar Events from Spreadsheet but prevent duplicates

Question

I'm trying to write a script that will take data from a Google spreadsheet and create events in my Google calendar.

I managed that fine but it produced duplicates every time I ran it. So now I'm trying to prevent that by creating a column 17 in the spreadsheet with an automatically produced unique event ID for each row and then each time the script is run it will look at the event ID for each row and delete the corresponding event in the calendar before recreating it with the original data or updated data if I've changed the row.

I'm new to scripting of any kind and cobbled this together but am hitting a wall now. Can anyone help sort this out?

function CalInsert() {
    var cal = CalendarApp.getDefaultCalendar();
    var id = SpreadsheetApp.getActiveSheet().getRange(2,17).getValue();

    if (id != 0) {
        var event = cal.getEventSeriesById(id);
        event.deleteEventSeries();
    }

    var sheet = SpreadsheetApp.getActiveSheet();
    var startRow = 2; // First row of data to process
    var numRows = sheet.getLastRow(); // Number of rows to process
    var dataRange = sheet.getRange(startRow, 1, numRows, sheet.getLastColumn());
    var data = dataRange.getValues();

    for (i in data) {
        var row = data[i];
        var title = row[0]; // First column
        var desc = row[13]; // Second column
        var tstart = row[14];
        var tstop = row[15];

        var event = cal.createEvent(title, tstart, tstop, {description:desc});
        var eventid = event.getId();
        SpreadsheetApp.getActiveSheet().getRange(2,17).setValue(eventid);
    }
}

Solution 1

This is very similar to a question asked just two days ago, which was about synchronizing a spreadsheet of events with a calendar. It sounds like you want to consider the spreadsheet to be the master of events that it originates, which would simplify the problem considerably. The basics of what you need to do are covered in this answer. If you'd rather just modify existing code, I've got an implementation below.

I have a modified version of the code from this blog, that will modify pre-existing calendar entries to match the info in the spreadsheet. I have arranged my spreadsheet differently, and this is reflected in the code.

Date | Title | Start Time | End Time | Location | Description | EventID

The event ID column gets filled in by the script when new events are created, and is then used in later invocations to retrieve events from the calendar, thereby avoiding duplication.

Script

/**
 * Adds a custom menu to the active spreadsheet, containing a single menu item
 * for invoking the exportEvents() function.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 * For more information on using the Spreadsheet API, see
 * https://developers.google.com/apps-script/service_spreadsheet
 */
function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Export Events",
    functionName : "exportEvents"
  }];
  sheet.addMenu("Calendar Actions", entries);
};

/**
 * Export events from spreadsheet to calendar
 */
function exportEvents() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var calId = "YOUR_CALENDAR_ID";
  var cal = CalendarApp.getCalendarById(calId);
  for (i=0; i<data.length; i++) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    var date = new Date(row[0]);  // First column
    var title = row[1];           // Second column
    var tstart = new Date(row[2]);
    tstart.setDate(date.getDate());
    tstart.setMonth(date.getMonth());
    tstart.setYear(date.getYear());
    var tstop = new Date(row[3]);
    tstop.setDate(date.getDate());
    tstop.setMonth(date.getMonth());
    tstop.setYear(date.getYear());
    var loc = row[4];
    var desc = row[5];
    var id = row[6];              // Sixth column == eventId
    // Check if event already exists, update it if it does
    try {
      var event = cal.getEventSeriesById(id);
    }
    catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }
    if (!event) {
      //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
      var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
      row[6] = newEvent;  // Update the data array with event ID
    }
    else {
      event.setTitle(title);
      event.setDescription(desc);
      event.setLocation(loc);
      // event.setTime(tstart, tstop); // cannot setTime on eventSeries.
      // ... but we CAN set recurrence!
      var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
      event.setRecurrence(recurrence, tstart, tstop);
    }
    debugger;
  }
  // Record all event IDs to spreadsheet
  range.setValues(data);
}

Delete / Recreate

In this alternative, the eventID is used to find and delete the previously existing event. After that, a new event is created with the data in the spreadsheet. This has the benefit that all values of the event can be updated, including start and stop times (see Notes below). On the other hand, any changes that were made to the original event will be lost - for instance, if other people had been invited to the event, or custom reminders were added.

To use this alternative, simply replace the matching code with this:

// Check if event already exists, delete it if it does
try {
  var event = cal.getEventSeriesById(id);
  event.deleteEventSeries();
  row[6] = '';  // Remove event ID    
}
catch (e) {
  // do nothing - we just want to avoid the exception when event doesn't exist
}
//cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
var newEvent = cal.createEvent(title, tstart, tstop, {description:desc,location:loc}).getId();
row[6] = newEvent;  // Update the data array with event ID
debugger;

Notes

• The Documentation for getEventSeriesById wrongly states it returns null when no matching event is found, when instead it throws an exception. (nasty!) So I've enclosed it in a try / catch block just to keep on swimming.
• Unfortunately, while getEventSeriesById works to retrieve an event, it returns an EventSeries object, which does not support the setTime() method. If you don't expect to change the time of events, this OK. Otherwise, you can change the Event into an EventSeries by setting the recurrence rules & times, or delete the old event and create a new one, as shown in Delete / Recreate. Issue 1154.
• The spreadsheet always wins. Any event changes (in relevant fields) recorded via the Google Calendar will be overwritten by the script.

Solution 2

Id like to Post this for anyone who would like to use it, I have modified the script to work within a sheet I was already using. Date Format and event duplication were a couple of issues that needed to be fixed but after some testing im pretty happy with how this is working.I Use it to Book jobs and share them with my employees who are mobile and do construction type work across the city. Next step is to pull calendar events to the spreadsheet so it can work both ways and I can use the calendar app on my phone to book jobs on the fly so if anyone has any advice im all ears, also i still need a script to insert form response data into the same sheet and add complete rows where the job numbers match keeping the existing Data intact.

`function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name : "Export Events",
functionName : "exportEvents"
}];     sheet.addMenu("Calendar Actions", entries);
};

function parseDate(s) {
var months = {jan:0,feb:1,mar:2,apr:3,may:4,jun:5,
            jul:6,aug:7,sep:8,oct:9,nov:10,dec:11};
var p = s.replace(".", "").split('-');
return new Date(p[2], months[p[1].toLowerCase()], p[0]);
}

/**
* Export events from spreadsheet to calendar
*/
function exportEvents() {
var sheet = SpreadsheetApp.getActiveSheet();

var headerRows = 6;  // Number of rows of header info (to skip)
var range = sheet.getDataRange();
var data = range.getDisplayValues();
//var calId = "Your calendar Id"; // PRODUCTION
var calId = "Your_calendar Id to test"; // TEST
var cal = CalendarApp.getCalendarById(calId);
//Logger.log(cal);
//Logger.log(data.length);
for (i=0; i<data.length; i++) {
if (i < headerRows) continue; // Skip header row(s)
if (data[i][0].length < 1) continue; // Skip if no content.

var row    = data[i];
Logger.log(row);
var date   = parseDate(row[0]);  // First column
//Logger.log(date);
var title  = row[1];           // Second column

var tstart = new Date();
var s = row[2].split(":");
tstart.setHours(s[0]);
tstart.setMinutes(s[1]);
tstart.setSeconds(s[2]);
tstart.setDate(date.getDate());
tstart.setMonth(date.getMonth());
tstart.setYear(date.getYear());

var tstop = new Date();
var e = row[3].split(":");
tstop.setHours(e[0]);
tstop.setMinutes(e[1]);
tstop.setSeconds(e[2]);

tstop.setDate(date.getDate());
tstop.setMonth(date.getMonth());
tstop.setYear(date.getYear());
var loc  = row[4];
var desc = row[5];
var id   = row[6];              // Sixth column == eventId
// Check if event already exists, update it if it does
var event = null;
if (id.length > 0) {
try {
event = cal.getEventSeriesById(id);
}
catch (e) {
// do nothing - we just want to avoid the exception when event doesn't            exist
} 
}
if (!event) {
//cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new
Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
var newEvent = cal.createEvent(title, tstart, tstop,                                     
{description:desc,location:loc}).getId();                     
var r = i + 1;
var cell = sheet.getRange("G" + r);
cell.setValue(newEvent);
}
else {
Logger.log(event);
event.setTitle(title);
event.setDescription(desc);
event.setLocation(loc);
// event.setTime(tstart, tstop); // cannot setTime on eventSeries.
// ... but we CAN set recurrence!
var recurrence = CalendarApp.newRecurrence().addDailyRule().times(1);
event.setRecurrence(recurrence, tstart, tstop);
}
debugger;
}
}

`

Comments

[Mogsdad]

It would be helpful to update your question to include references to where you got the initial script from, especially since you don't describe your source data. I think you started with this blog entry.

[Serge insas]

It seems this is a subject you really like :-) +1 for being exhaustive.

[Kurt Leadley]

I'm using many pieces of this code, thanks Mogsdad. Have you come across any solutions for You have been creating or deleting too many calendars or calendar events in a short time. Please try again later.. I get that a lot and my spreadsheet is only handling about 100 events at a time. Currently I am using Utilities.Sleep to mitigate this problem, but it makes the script run very very slowly.

[A P]

Very useful script, just one question. How did you format the Start and End Times? Mine is off by half an hour, which is weird behaviour