Create a gist now

Instantly share code, notes, and snippets.

@swh /sync.js
Last active Aug 20, 2017

What would you like to do?
Spreadsheet / Calendar syncing in Google Apps
/* 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?

Davepar commented Jan 8, 2016

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?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment