Last active
July 1, 2020 11:28
-
-
Save swh/8d83dd60c983bcce541a to your computer and use it in GitHub Desktop.
Spreadsheet / Calendar syncing in Google Apps
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* 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()); | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.