Grabbing Google Calendar Event Details into a Spreadsheet

A comment to Updating Google Calendars from a Google Spreadsheet, where I showed how to get events described in a Google spreadsheet into a Google Calendar wondered if the other way around is possible? … It would be great for logging hours for projects or declaring working hours ;-)

Yes:-) Twenty seconds ago, this spreadsheet was empty:

A quick run of a script and I populated it from my default calendar using Google Apps script. Once again, all I did was peek through the docs and pull out the fragments I needed: Here’s the script:

function caltest3(){
  // The code below will retrieve events between 2 dates for the user's default calendar and
  // display the events the current spreadsheet
  var cal = CalendarApp.getDefaultCalendar();
  var sheet = SpreadsheetApp.getActiveSheet();
  var events = cal.getEvents(new Date("March 8, 2010"), new Date("March 14, 2010"));
  for (var i=0;i<events.length;i++) {
    var details=[[events[i].getTitle(), events[i].getDescription(), events[i].getStartTime(), events[i].getEndTime()]];
    var row=i+1;
    var range=sheet.getRange(row,1,1,4);

So now not only can we use a spreadsheet as a database we can also use a calendar in a similar way, and if necessary, link them all together?

Author: Tony Hirst

I'm a Senior Lecturer at The Open University, with an interest in #opendata policy and practice, as well as general web tinkering...

23 thoughts on “Grabbing Google Calendar Event Details into a Spreadsheet”

  1. Thanks for this and other posts about Google Appscript!

    When I play around with the possibility it seems like it’s not possible to grab the location from a calendar event into a spreadsheet – have you found a solution for this?

  2. This is just what I’ve been looking for, but how do I extract the data from a non-default calendar?


  3. @Sam, I am new to the game of G-scripting, but I think that the following might work.

    In place of line 06, use
    var cal = CalendarApp.openByName(calname); where calname is the name of the calendar you want to extract from if the name was Family you’d type “Family” in place of calname.

  4. any idea how to make this script work? looks like the api got updated, i’m running into errors

    1. oh I got it working, is there a way for me to use the
      var cal = CalendarApp.openByName(calname);

      to get MULTIPLE calendars?

      var cal = CalendarApp.openByName(cal1name,cal2name,cal3name);

  5. I’m not a programmer, so I don’t know that that’s a possibility, but it occurs to me that there’s a workaround:

    use the script on a different worksheet for each calendar you want to pull in, and then you can use the import range function to import all the calendars onto a single worksheet and then use the Sort function to make everything appear in order.

    Ain’t pretty, but it would work.

    I haven’t experimented with Yahoo pipes, but from what I’ve heard of it, it might possess the ability to aggregate multiple calendar feeds into a single one which you could then call in your gDocs script…

    1. Maybe you could use a syntax like
      var cal[0] = CalendarApp.openByName(cal1name);
      var cal[1] = CalendarApp.openByName(cal2name);
      var cal[2] = CalendarApp.openByName(cal3name);


      var cal[i] = CalendarApp.openByName(cal_name[i]);
      with a for next loop

  6. How could I use a filter on the events.title ?
    I would like this value to come from a cell on the active worksheet…

    Thanks !!

  7. I’ve got the same issue

    “oh I got it working, is there a way for me to use the
    var cal = CalendarApp.openByName(calname);

    to get MULTIPLE calendars?”

    Does someone know how to do this ?

    I’ve also changed

    to :

  8. Does anyone know how this script could be run AUTOMATICALLY so that the spreadsheet is “always” reflecting the calendar events.? This is so that some other process (digital sign) can always read the spreadsheet and I know it will always be up to date.

    1. @john it’s a long time since I played with this script, but it may be worth seeing if you can run the appropriate function using a timed trigger event in Google Apps Script editor…?

  9. This is an awesome script. Love it. Makes life so much easier. Anyone know how to get it to calc the hours an event lasts in a new column?

  10. var cal = CalendarApp.openByName(calname); does not work for me. Checked it multiple times, and it’s the same as the ones listed here.

  11. For those of you trying to get multiple calendars, you can do it by replacing line 6 with something like:

    var cal=CalendarApp.openByName(“Calendar 1”);
    var cal=CalendarApp.openByName(“Calendar 2”);

  12. Hi there – Has anyone uploaded this to the Script Gallery. I would also appreciate if there was a function that demanded that I pasted the name of the calendar…. Sorry, not very used to code anything :(

  13. Is there a way to have this add to rather than replace the data in the spreadsheet you’re targeting? This would mean that whenever I run the script for a new date range it puts it below the existing info instead of on top of it?

  14. I get this error when running the script : TypeError: Cannot call method “getRange” of null. (line 13, file “Code”)

    I wonder if someone can help:I am a complete noob to scripting… probably I am doing something very simple that is incorrect…

    your help much appreciated!

    thank you

Comments are closed.

%d bloggers like this: