javascript - How to synchronize Google Calendar & Spreadsheet with Script

Question

I am trying to create a Google Apps Script that keeps a Google Calendar and a "master spreadsheet" on Drive synchronized -- is this possible? I found these two posts:

• http://blog.ouseful.info/2010/03/04/maintaining-google-calendars-from-a-google-spreadsheet/

• http://blog.ouseful.info/2010/03/05/grabbing-google-calendar-event-details-into-a-spreadsheet/

I'm quite sure this could be done using a lot of if statements and logic, but maybe there's a simpler way?

I ended up just providing the following simple script. All that was really necessary was adding events based on two columns, and this would've taken too long to develop.

function onOpen() {
  //spawns a menu with a button that triggers AddToCal
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Add event to calendar",
    functionName : "AddToCal"
  }];
  sheet.addMenu("Data To Calendar Plugin", entries);
};

function AddToCal(){

  //get the current row
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var cell = ss.getActiveCell();
  var R = cell.getRow();

  //grab values for current row to pass to calendar event
  var date_of_event = ss.getRange('G'+R).getValue();
  var date = new Date(date_of_event);
  var event_title = ss.getRange('A'+R).getValue();
  //access the calendar
  var cal = CalendarApp.getCalendarById('[IDREMOVED]');
  cal.createAllDayEvent(event_title,date);

  ss.toast("Event added to " + cal.getName());
  }

Answer 1

Yes, it's possible to write a two-way event synchronization script, but it isn't going to be simple. Those two posts you refer have parts that could be reused, but they are quite elementary compared to the challenges you'll face with actual synchronization. You may want to read over Using Google Apps Script for a event booking system which does create calendar entries based on a spreadsheet (but doesn't do on-going synchronization). I've done some debugging of that script in past.

Synchronization would need to support:

• Creation of events in either location
• Modification of event details in either location (although you could opt to consider only a subset of event details for simplification)
• Deletion of events in either location
• Recurrence, e.g. CalendarEvent.getEventSeries() handling (or choose to avoid)

This is pseudo-code that you could start with:

Open Calendar, Read Calendar events into calArray (will all attributes you care for)
Open Spreadsheet, Read Spreadsheet events into sheetArray

For each event in calArray:
  Search for calEvent in sheetArray.
  If found, compare lastUpdated values.
    If equal, do nothing
    Otherwise copy most recently updated to least recently updated
    Continue with next event
  If not found then copy calEvent to new sheetEvent, including lastUpdated value.
  Continue with next event

For each event in the sheetArray (...that hasn't been handled yet)
  Similar logic above.

Write updated sheetArray to spreadsheet.
Write updated calEvents to calendar API (see note 1 below)

Notes:

1. All updates to calEvents could be made to array and written to calendar API immediately, as an alternative to a bulk update. This would eliminate the need to track the changes locally, although it would be a good idea to touch the lastUpdated value.

2. You will want to use CalendarEvent.getLastUpdated() when reading calEvents, and store a similar value in your spreadsheet (tied to an onEdit trigger) to facilitate comparisons.

3. It would simplify comparisons to record CalendarEvent.getId() against events in the spreadsheet. You also have CalendarEvent.setTag(key,value) that could be used to record custom metadata into the calendar, for instance to indicate events that originated or have been synchronized with your spreadsheet. (These tags are not accessible through the GCal UI, so would only be accessible via script.)

4. You should think about the range of dates or number of events you want to deal with, and limit the scope of the script. If you don't, you are sure to run into execution time limits in real operation.

5. Some Calendar Event characteristics don't lend themselves to easy expression in a spreadsheet, for instance:

   • Guest list
   • Reminder list