Export from Calendar to Sheet using Apps Script

Sometimes it can be really useful to grab events from your Google calendar into a spreadsheet, where you can process them further. Here’s some example code to let you do that.

I think the code is fairly self-explanatory – just change the email address in the opening lines to the one that corresponds to the calendar in question.

function getCal() {
 var mycal = "***********@gmail.com"; // change to suit
 var cal = CalendarApp.getCalendarById(mycal);
 //Clear the sheet:
 var sheet = SpreadsheetApp.getActiveSheet();
 //Get the events (use your own dates and keywords, or pass them as arguments):
 var events = cal.getEvents(new Date("January 01, 2014 00:00:00 CST"), new Date("May 31, 2014 23:59:59 CST"), {search: 'Birthday'});
 //Then loop through and write out (choose your own calendar fields, I've used just five):
 for (var i=0;i<events.length;i++) {
 var details=[[mycal, events[i].getTitle(), events[i].getDescription(), events[i].getLocation(), events[i].getStartTime(), events[i].getEndTime()]];
 var range=sheet.getRange(i+1,1,1,6);

The key here is the CalendarApp.getEvents() method, which has the syntax:

getEvents(startTime, endTime, options)

The options parameter is a JavaScript object that specifies advanced parameters:

start       the index of the first event to return
max       the maximum number of events to return
author   an email address used to filter results by the event creator
search    a full-text search query used to filter results
statusFilters[]    an array of statuses used to filter results

In he code example I’ve used the search parameter to grab all calendar events between January 1st 2014 and May 30th 2014 that contain the word birthday. I also added a little code to the onOpen() trigger to add a menu item used to launch the code – see this post for some clues on how to do that. Here’s the code in the editor:

Click image to enlarge

Click image to enlarge

And, once the code has been run, here’s the result:

Click image to enlarge

Click image to enlarge



Leave a Reply

Your email address will not be published. Required fields are marked *