Spreadsheet / Calendar syncing in Google Apps

sync.js

/* Gobal settings */
var calId = "YOUR_CALENDAR_ID";
var alertEmail = 'YOUR_EMAIL_ADDRESS';
var sheetName = 'Holiday';

/*
 * FIXME: handle rows being deleted from the spreadsheet - right now they will
 * stay in the calendar if that happens
 */

/*
 * Add a custom menu to the active spreadsheet, containing a single menu item.
 * The onOpen() function, when defined, is automatically invoked whenever the
 * spreadsheet is opened.
 */
function onOpen() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name: "Sync Calendar Now",
    functionName: "syncActive"
  }];
  spreadsheet.addMenu(sheetName, entries);
};

/*
 * Check that we have the right sheet active.
 */
function syncActive() {
  var sheet = SpreadsheetApp.getActiveSheet();
  if(sheet.getName() == sheetName) {
    syncSheet(sheet);
  } else {
    Browser.msgBox("This operation only works on the Holiday sheet");
  }
}

/*
 * This is called periodically to sync the Sheet to the Calendar
 */
function timerEvent() {
  var sheet0 = SpreadsheetApp.getActive().getSheets()[0];
  if (sheet0.getName() == sheetName) {
    syncSheet(sheet0);
  } else {
    MailApp.sendEmail(alertEmail, 'Calendar/Holiday sync failed', 'Expecting Holiday sheet, got '+sheet0.getName());
  }
}

/*
 * Do the actual work
 */
function syncSheet(sheet) {
  var headerRows = 1;  // Number of rows of header info (to skip)
  var range = sheet.getDataRange();
  var data = range.getValues();
  var changes = 0;
  for (i in data) {
    if (i < headerRows) continue; // Skip header row(s)
    var row = data[i];
    
    var who = row[0];

    // Skip rows that aren't filled in
    if (!who) continue;
    
    var date_start = new Date(row[1]);
    var date_end = new Date(row[2]);
    // For some reason the google calendar API v3 end date is off-by-one, so add a day
    date_end.setDate(date_end.getDate() + 1);

    var title = who+" holiday";
    // Column F is a notes field, we'll stick that in location
    var location = row[5];
    // Column G (7th col) holds the eventId, where known
    var id = row[6];
    
    // Check if event already exists, update it if it does
    var event = null;
    try {
      // If id is null then we seem to get an object representing the whole Calendar, so this check is important
      if (id) {
        event = Calendar.Events.get(calId, id);
      }
    } catch (e) {
      // do nothing - we just want to avoid the exception when event doesn't exist
    }

    var eventFromSheet = {
      'summary': title,
      'start': {
        'date': isoDateString(date_start)
      },
      'end': {
        'date': isoDateString(date_end)
      }
    };
    if (location) {
      eventFromSheet.location = location;
    }

    var insert = 0;    
    // Event doesn't exist, or it's been deleted
    if (!event || event.status == 'cancelled') {
      insert = 1;
    } else {
      // Can't do a deep compare because the object we get back from the API has extra stuff in it
      if (event.summary == eventFromSheet.summary &&
          ((!event.location && !eventFromSheet.location) || event.location == eventFromSheet.location) &&
          event.start.date == eventFromSheet.start.date &&
          event.end.date == eventFromSheet.end.date) {
      } else {
        // You only get to update() an Event once, for some reason, work around that by deleting then inserting
        Calendar.Events.remove(calId, id);
        insert = 1;
      }
    }
    if (insert) {
      var newEvent = Calendar.Events.insert(eventFromSheet, calId);
      // Update the data array with event ID
      row[6] = newEvent.getId();
      changes++;
    }
  }

  if (changes) {
    // Record event IDs to spreadsheet
    range.setValues(data);
    MailApp.sendEmail(alertEmail, 'Calendar/Holiday sync', 'Added / changed '+changes+' events');
  }

  return changes;
}

/*
 * Format a Javascript date as an ISO date (YYYY-MM-DD), ignoring
 * timezone info (otherwise unfortunate things happen with daylight savings transitions)
 */
function isoDateString(date) {
  // Not using Utilities.formatDate() because it's too hard to guess the right timezone :-/
  return Utilities.formatString("%04d-%02d-%02d", date.getFullYear(), date.getMonth()+1, date.getDate());
}