Create a gist now

Instantly share code, notes, and snippets.

@swh /sync.js
Last active May 18, 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

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