OUseful.Info, the blog…

Trying to find useful things to do with emerging technologies in open education

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(){
  //http://www.google.com/google-d-s/scripts/class_calendar.html#getEvents
  // 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++) {
    //http://www.google.com/google-d-s/scripts/class_calendarevent.html
    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);
    range.setValues(details);
  }
}

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?

Written by Tony Hirst

March 5, 2010 at 9:47 am

Posted in Google Apps

Tagged with ,

23 Responses

Subscribe to comments with RSS.

  1. [...] « Grabbing the JSON Description of a Yahoo Pipe from the Pipe Itself Grabbing Google Calendar Event Details into a Spreadsheet [...]

  2. 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?

    Johan

    April 13, 2010 at 10:19 pm

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

    Sam

    Sam

    May 31, 2010 at 6:08 pm

  4. @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.

    Chris

    July 25, 2011 at 2:27 am

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

    squash

    October 5, 2011 at 9:33 am

  6. Cool script but I can’t seem to get it to work!

    Nate

    November 12, 2011 at 2:52 am

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

      to get MULTIPLE calendars?

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

      Nate

      November 12, 2011 at 2:58 am

  7. 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…

    squash

    November 14, 2011 at 7:16 am

    • 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);

      Or

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

      Sam Van Kerckhoven

      November 22, 2011 at 1:47 am

  8. 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 !!

    Sam Van Kerckhoven

    November 22, 2011 at 1:48 am

  9. 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

    from:
    CalendarApp.openByName
    to :
    CalendarApp.getCalendarById

    Sam

    December 8, 2011 at 6:48 pm

  10. 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.

    John Cousins

    June 28, 2012 at 3:45 pm

    • @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…?

      Tony Hirst

      June 29, 2012 at 11:36 pm

  11. 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?

    Danny

    August 4, 2012 at 6:01 pm

    • Figured it out. Subtract the difference and multiply by 24.

      Danny

      August 4, 2012 at 6:07 pm

  12. [...] spreadsheet « Grabbing the JSON Description of a Yahoo Pipe from the Pipe Itself Grabbing Google Calendar Event Details into a Spreadsheet [...]

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

    Eric

    September 19, 2012 at 10:33 pm

  14. 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″);

    SMILE (@SmileUpdate)

    October 12, 2012 at 4:25 pm

  15. 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 :(

    Engsig Testa

    October 25, 2012 at 5:31 pm

  16. 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?
    THanks

    alex stanton

    November 26, 2012 at 4:29 pm

  17. [...] blog.ouseful.info/2010/03/05/g… [...]

  18. 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

    Toby Stephens

    May 31, 2013 at 2:25 am


Comments are closed.

Follow

Get every new post delivered to your Inbox.

Join 820 other followers

%d bloggers like this: