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
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
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
[...] spreadsheet « 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…
August 22, 2012 at 5:53 pm
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
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
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
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
[...] blog.ouseful.info/2010/03/05/g… [...]
Google Calendar / Spreadsheet reciprocity script with Gscript - How-To Video
April 1, 2013 at 1:31 pm
[...] http://blog.ouseful.info/2010/03/05/grabbing-google-calendar-event-details-into-a-spreadsheet/ [...]
Google Calendar / Spreadsheet reciprocity script with Gscript | appsgoogleplus.com
April 1, 2013 at 3:40 pm