Problem
Execute a function updating a spreadsheet when an event in Google Calendar is created.
Solution
Use the EventUpdated
installable trigger that is fired each time an event is modified in Calendar (e.g. created, updated, or deleted - see reference). From there, you can go the easy way (update all data in the spreadsheet with a built-in CalendarApp
class) or the hard way (update data that was changed with incremental sync - see official guide).
Part 0 - install trigger
/**
* Installs Calendar trigger;
*/
function calendarTrigger() {
var trigger = ScriptApp.newTrigger('callback name here')
.forUserCalendar('calendar owners email here')
.onEventUpdated()
.create();
}
Part 1 - callback (Calendar -> Spreadsheet)
/**
* Updates spreadsheet;
* @param {Object} e event object;
*/
function updateSpreadsheet(e) {
//access spreadsheet;
var ss = SpreadsheetApp.openById('target spreadsheet id');
var sh = ss.getSheetByName('target sheet name');
var datarng = sh.getDataRange(); //assumed that data is only calendar data;
//access calendar;
var calendar = CalendarApp.getCalendarById(e.calendarId);
//set timeframes;
var start = new Date();
var end =new Date();
//get year before and three after;
start.setFullYear(start.getFullYear()-1);
end.setFullYear(end.getFullYear()+3);
//get events;
var events = calendar.getEvents(start, end);
//map events Array to a two-dimensional array of values;
events = events.map(function(event){
return [event.getTitle(),event.getDescription(),event.getStartTime(),event.getEndTime()];
});
//clear values;
datarng.clear();
//setup range;
var rng = sh.getRange(1,1, events.length, events[0].length);
//apply changes;
rng.setValues(events);
}
Notes
- As per Tanaike's comment - it is important to account for triggers (both simple and installable) to not firing if event is triggered via script or request (see restrictions reference). To enable such feature you will have to introduce polling or bundle with a WebApp that the script will call after creating an event (see below for a bundling sample).
- Your solution is better suited for backwards flow: edit in spreadsheet -> edit in Calendar (if you modify it to perform ops on Calendar instead of updating the spreadsheet, ofc).
- Make use of
Date
built-in object's methods like getFullYear()
(see reference for other methods) to make your code more flexible and easier to understand. Btw, I would store "ms in a day" data as a constant (86400000
).
- Never use
getRange()
, getValue()
, setValue()
and similar methods in a loop (and in general call them as little as possible) - they are I/O methods and thus are slow (you can see for yourself by trying to write >200 rows in a loop). Get ranges/values needed at the start, perform modifications and write them in bulk (e.g. with setValues()
method).
Reference
EventUpdated
event reference;
- Calendar incremental synchronization guide;
Date
built-in object reference;
setValues()
method reference;
- Using batch operations in Google Apps Script;
- Installable and simple triggers restrictions;
WebApp bundling
Part 0 - prerequisites
If you want to create / update / remove calendar events via script executions, you can bundle the target script with a simple WebApp. You'll need to make sure that:
- The WebApp is deployed with access set as
anyone, even anonymous
(it is strongly recommended to introduce some form of request authentication);
- WebApp code has one function named
doPost
accepting event object (conventionally named e
, but it's up to you) as a single argument.
Part 1 - build a WebApp
This build assumes that all modifications are made in the WebApp, but you can, for example, return callback name to run on successfull request and handle updates in the calling script. As only the calendarId
property of the event object is used in the callback above, we can pass to it a custom object with only this property set:
/**
* Callback for POST requests (always called "doPost");
* @param {Object} e event object;
* @return {Object} TextOutput;
*/
function doPost(e) {
//access request params;
var body = JSON.parse(e.postData.contents);
//access calendar id;
var calendarId = body.calendar;
if(calendarId) {
updateSpreadsheet({calendarId:calendarId}); //callback;
return ContentService.createTextOutput('Success');
}else {
return ContentService.createTextOutput('Invalid request');
}
}
Part 2 - sample calling script
This build assumes that calling script and the WebApp are the same script project (thus its Url can be accessed via ScriptApp.getService().getUrl()
, otherwise paste the one provided to you during WebApp deployment). Being familiar with UrlFetchApp
(see reference) is required for the build.
/**
* Creates event;
*/
function createEvent() {
var calendar = CalendarApp.getCalendarById('your calendar id here');
//modify whatever you need to (this build creates a simple event);
calendar.createEvent('TEST AUTO', new Date(), new Date());
//construct request parameters;
var params = {
method: 'post',
contentType: 'application/json',
muteHttpExceptions: true,
payload: JSON.stringify({
calendar: calendar.getId()
})
};
//send request and handle result;
var updated = UrlFetchApp.fetch(ScriptApp.getService().getUrl(),params);
Logger.log(updated); //should log "Success";
}