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:

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:

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!

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?

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 :-)

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?

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

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

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.

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.

Regarding the duplicate, I added a function that checks first if an event with the same title exists that day. You have to iterate through the returned CalendarEvent object and set a validator to false. Then, if the validator is still true after that recursive function, you can go ahead and add the event :)

Hi, great thread here :) i’ve been trying this and took a look at the duplicated event thread, however if we want to run the script to create event A and then want to update event A again how do we do it? How do we get the eventid from the event A we just created so that we can maybe update it?

I’ve been looking for/working on a two way sync and here’s a proof of concept I got working. It still needs to be fined tuned, but I thought I would post it in case anyone has insight in optimizing the code, and for anyone that would find it useful. Basically the code creates an array from the calendar, and the spreadsheet, combines them. Sorts it on last modified (date last updated for event, and the standard scripted last modified column for spreadsheet), removes duplicates, and submits to both the calendar (currently after deleting all events) and the spreadsheet. I was planning on adding a key, and having a list where you would type the key to remove the items from both locations, the upside being you can add spreadsheet rows from calendar and vice versa. Thanks in advance for any helpful input.
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries = [ {name: “Sync Spreadsheet to Calendar”, functionName: “calsync”}];
//{name: “Sync”, functionName: “myimport”}];
ss.addMenu(“Calendar Sync”, menuEntries);
}

function calsync()
{
// This function should be executed from the
// spreadsheet you want to export to the calendar
var mySpreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(“Test123”);

Here’s an updated version of the code I post. I’ve cleaned up the code, and added some comments, the biggest change is now I pull multiple columns into the description, so those can be updated in both places but the spreadsheet will keep the columns organized. It was the best solution I could think of without being able to add actual fields to the calendar event. I also put in some code to replace variations of initials and first names, my solution was hardcoded after I gave up finding a better way to do it, I would love to hear some suggestions on how to handle that batter. Hopefully this helps someone looking for something similiar to what I needed, I still have to work this in at a more global level to our task tracking workbooks, so I may or may not have another major update. I’m getting a funky extra title row, but won’t be able to revisit it until next week, I think I need to exclude a header row in one of the for loops.

function calsync()
{
// // Here are the steps the script follows
// 1.Builds events array from calendar, if null ignores, i add a feature, or facility on a sheet level with a template sheet, so
// 2.Get calendar from sheet, combine columns, and add headers so that many columns fit in description
// 3. Concatenate arrays
// 4.Filter for last modified, take most recent data
// 5. replace initials or first name with full name
// 6. array is ready to be sent back to calendar
// 7. split out columns based on header (currently it depends on the order, I would like to be able to find based on header and header rank, and then send them in the standard order,
// but since no one is adding new calendar events, just edditing its not a big deal.
// Just a side note, I use the flagged for discussion column to query all sheets on all workbooks where a team member would like to raise issue, to achieve maximum visibility on questions/concerns.

Guys, sorry, i’m a complete newbie to Google Script. Can someone please help with getting this script work with the following three fields: “Name”, “Surname”, “Date”, which would result in an annual event entry in the calendar like this: “Name Surname Birthday” [at a corresponding date every year]?