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?
[...] « Grabbing the JSON Description of a Yahoo Pipe from the Pipe Itself Grabbing Google Calendar Event Details into a Spreadsheet [...]
Updating Google Calendars from a Google Spreadsheet « OUseful.Info, the blog…
March 5, 2010 at 10:00 am
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
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
@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
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
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
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
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
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