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());
}

do you have a sample google sheet for this code?

For future people looking for a calendar sync script, I wrote one as well that handles all-day events and removing events from the spreadsheet: https://github.com/Davepar/gcalendarsync

Hey Davepar,
Thanks for the script to sync sheets and calendar. One question, is it possible to have added rows be synced automatically without having to select the calendar sync option?

Does this script preserve formulas? Right now I'm using a script that after writing event id's to a column will remove all formulas for some reason...

@davepar this is great thank you so much.

Someone can help me? I would modify the script to make limited times appointment and not only all day events.
I add others two rows in the table and used a concatenate function to join date and time, now I've two rows (DateTimeStart and DateTimeEnd) but in european format dd/mm/yyyy hh:mmm.
How can I convert the function isoDateString, in the right mode, thanks.