Updating Google Calendars from a Google Spreadsheet

I got a request today along the lines of:

We’re in the process of creating a master calendar of events spreadsheet relevant to [various things]. These [various things] will all then have their own Google calendar so they can be looked at individually, embedded etc and everyone could of course have access to all and view them all via their personal Google calendar, turn different calendars on or off, sync with Outlook etc. etc.

X said “wouldn’t it be great if we made the master spreadsheet with Google docs and it could somehow automate and complete the calendars”.

Sigh…;-) So – is it possible?

I’ve only had a quick play so far with Google Apps script, but yes, it seems to be possible…

Take one spreadsheet, liberally sprinkled with event name, description, start and end times, an optional location, and maybe a even a tag or too (not shown):

The time related columns I specified as a date type using the “Data Validation…” form from the Tools menu:

Now take one Google apps script:

function caltest1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  var dataRange = sheet.getRange(startRow, 1, numRows, 5);
  var data = dataRange.getValues();
  var cal = CalendarApp.getDefaultCalendar();
  for (i in data) {
    var row = data[i];
    var title = row[0];  // First column
    var desc = row[1];       // Second column
    var tstart = row[2];
    var tstop = row[3];
    var loc = row[4];
    //cal.createEvent(title, new Date("March 3, 2010 08:00:00"), new Date("March 3, 2010 09:00:00"), {description:desc,location:loc});
    cal.createEvent(title, tstart, tstop, {description:desc,location:loc});
 }
}

(This was largely a copy and paste from Sending emails from a Spreadsheet Tutorial, which I’d half skimmed a week or two ago and seemed to remember contained a howto for bulk mailing from a spreadsheet, and the Apps script Calendar class documentation.)

And here’s the result of running the function:

The email tutorial adds a bit of gloss that allows a further column to contain state information about whether an email has already been set; we could do something similar to specify whether or not an event has been automatically added to the calendar, and if not, add it when the function is run.

Because it can be a pain having to go into the script editor to run the function, it’s easier to just create a menu option for it:

Here’s how:

function onOpen() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var menuEntries = [ {name: "Shove stuff in calendar", functionName: "caltest2"} ];
  ss.addMenu("OUseful", menuEntries);
}

I had a little play to see if I could trivially get an RSS feed into the spreadsheet using an =importFeed() formula, and use the details from that to populate the calendar, but for some reason the feed importer function didn’t appear to be working?:-( When I tried using CSV data from a Yahoo RSS2CSV proxy pipe via a =importData() formula, the test function I’d written didn’t appear to recognise the date format…

PS Arghh… the test formula assumes a Date type is being passed to it… Doh!

Hack round importFeed still not working by grabbing a CSV version of the feed into the spreadsheet:
=importdata(“http://pipes.yahoo.com/ouseful/proxy?_render=csv&url=http%3A%2F%2Fopen2.net%2Ffeeds%2Frss_schedule.xml”)

Tweak the calendar event creation formula:

cal.createEvent(title, new Date(tstart), new Date(tstop), {description:desc});

Run the function:

Heh heh :-)

PS it’s also possible to move content from a Google Calendar to a Google spreadsheet, as Grabbing Google Calendar Event Details into a Spreadsheet shows…

PPS it strikes me that the spreadsheets2calendar route provides one way of generating an iCal feed from a list of event times held in a spreadsheet, by popping the events into a Calendar and then making the most of its output formats? A bit like using Yahoo pipes as a quick’n'easy KML generator?

Written by Tony Hirst

March 4, 2010 at 8:20 pm

Posted in Google Apps, Tinkering

Tagged with apps script, calendar, spreadsheet

23 Responses

Subscribe to comments with RSS.

Interesting script!

Wondering if the other way around is possible? Or should I make the calendar public and import in Google Docs? It would be great for logging hours for projects or declaring working hours ;-)

Joost Plattel

March 4, 2010 at 8:56 pm

Reply
- Yes, the other way round does appear to be possible – e.g. http://ouseful.wordpress.com/2010/03/05/grabbing-google-calendar-event-details-into-a-spreadsheet/
  
  Tony Hirst
  
  March 5, 2010 at 9:48 am
- Thanks! :-)
  
  Joost Plattel
  
  March 5, 2010 at 10:26 am
[...] Published March 5, 2010 Google Apps Leave a Comment Tags: calendar, spreadsheet A comment to Updating Google Calendars from a Google Spreadsheet, where I showed how to get events described [...]

Grabbing Google Calendar Event Details into a Spreadsheet « OUseful.Info, the blog…

March 5, 2010 at 9:47 am

Reply
[...] March 7, 2010 Google Apps Leave a Comment Tags: calendar, spreadsheet In the post Updating Google Calendars from a Google Spreadsheet, I described a recipe for adding events to a Google Calendar from a Google Spreadsheet using Google [...]

Maintaining a Google Calendar from a Goole Spreadsheet, Reprise « OUseful.Info, the blog…

March 7, 2010 at 5:06 pm

Reply
If data from spreadsheet to calendar is possible, I guess it must be also posssible to push data to the gmail contactslist.
This would be very easy when people can fill in there own adress,phone, birtday etc using google form. All that data is collected in to the spreadsheet and at the end of the day we run a script and all that goes to your contactlist. Whishfull thinking? you never know :-)

Joris

March 11, 2010 at 6:36 pm

Reply
I really like this script. There is an app thats way too complicated to configure

http://code.google.com/apis/gdata/articles/spreadsheets_calendar_base_mashup.html

Can I ask if there is a way to stop duplicated entry? Everey time you run the function it just creates another calendar entry. Can it be configured to update existing entries using title as a unique field?

Mat Agbaba

September 13, 2010 at 10:17 pm

Reply
- “Can I ask if there is a way to stop duplicated entry? Every time you run the function it just creates another calendar entry. Can it be configured to update existing entries using title as a unique field?”
  
  Could help me with this process? We’d like to use this at my institution.
  
  Matthew K. Seibert
  
  November 18, 2011 at 9:10 pm
spoke to soon, noticed you already found a way :)

Mat Agbaba

September 14, 2010 at 8:58 am

Reply
- I was just trying to figure out how to do this thanks! I can’t seem to make it work without doing creating duplicates. How did you get it to update the existing events.
  
  david
  
  david
  
  December 3, 2010 at 4:28 pm
This is awesome

Matt P

August 10, 2011 at 6:26 pm

Reply
how do i update an event that is already created? I don’t want to duplicate with new dates and leave the old behind. Also single day events show up about 50% of the time.

I have copied the script above, and edited the cal.createEvent with the new commands but still isn’t working.

I would greatly appreciate some help.

Thank you

Craig Misak (@cmisak)

September 6, 2011 at 6:59 pm

Reply
- @craig It’s been some time since I looked at the API, but when I cobbled together the script I seem to remember that updating pre-existing events wasn’t an option… I will try to make time to see if that functionality is now supported – but I can’t guarantee when…
  
  Tony Hirst
  
  September 6, 2011 at 7:29 pm
Did anyone got the solution for updating exisiting events and avoiding duplicate items in calendar. Thanks

Pramod

November 17, 2011 at 5:05 am

Reply
I was wondering if we could work together to make it so that it does not duplicate events when the script runs.

I am a Professor and Librarian looking to create a bookings module, which this would allows me to do.

Ultimately, what I want to to is make a Google Form, that is also a spreadsheet, and then have a script run that exports all new events from the spreadsheet into a Google Calendar.

I look forward to your reply.

Matt

Matthew K. Seibert

November 18, 2011 at 8:02 pm

Reply
- I am wondering the exact same thing, using this and the reprise of this post I have been able to make it publish to a specific calendar, but every time I run the function it recreates the event.
  
  Any help would be great!
  
  MJChibanga
  
  November 23, 2011 at 7:08 pm
I found this in the Calendar API. Maybe this would help with the updating events problem, and stop the creation of duplicates. I’m really interested in solving this problem and so are my co-workers and other academic institutions. I look forward to working with you.

http://code.google.com/apis/calendar/data/2.0/developers_guide_java.html#UpdatingEvents

Matthew K. Seibert

December 9, 2011 at 5:04 pm

Reply
- Here is a page of JavaScript features, including a calendar update
  
  Matthew K. Seibert
  
  December 9, 2011 at 5:12 pm
http://code.google.com/apis/calendar/data/1.0/developers_guide_js.html#Interactive_Samples

Matthew K. Seibert

December 9, 2011 at 5:13 pm

Reply
Has a solution been found? I would really be interested in this functionality too, regarding the updating of times without creating a duplicate.

Rob

January 24, 2012 at 4:40 am

Reply
For many years I have wanted to be able to use, administer and update my relational database information in an active spreadsheet, linked to an events list or even a call list. Programmers right and left have pishawed me and said it ain’t going to happen.

I hoped Google Apps would have this feature, but I sure can’t find it. For instance, I would like to see all my Google calendar events on a spreadsheet and be able to update that calendar on either the event view or in that synchronized active spreadsheet view.

This would not involve any exporting or importing of course.

Any suggestion?

iP

February 27, 2012 at 1:38 am

Reply
[...] Maintaining a Google Calendar from a Google Spreadsheet and Google Spreadsheet and Google Calendar Linkup Update [...]

The ProAct Project (Spring 2012)

March 6, 2012 at 2:34 pm

Reply
Hi,

Great tutorial! This is exactly what I’m trying to do at work, managing hundreds of meetings for individual clients.

Anyone know how I can add the info to a particular “sub” calendar that’s not my default? I’ve tried various functions but just can’t get it to work.

Ben

March 7, 2012 at 5:29 pm

Reply

OUseful.Info, the blog…