# Updating a Google Calendar and Google Site from a Google Spreadsheet (the beginnings of an event booking system)

**Update: New version is posted here [The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever](https://hawksey.info/blog/2010/11/eventmanagerv3/ "Permanent Link to The best Google Spreadsheet Event Manager (w/h Sites, Contact, Calendar integration) ever, ever, ever")**

I’m going to start this most with a small homage to Tony Hirst. Tony has the honour of posting the [first ever comment on this blog](https://hawksey.info/blog/2008/06/a-mornings-learning-google-alert-edtechie-he20-sociallearn-ads-fund-he-visual-gadgets/) and ever since I’ve been an avid follower of his work on [OUseful.info](http://ouseful.info). When I saw Tony had started [playing around experimenting with using Google Apps Scripts](http://ouseful.wordpress.com/category/google-apps/) to post data from Spreadsheet to Calendar and vice versa it looked like he was having way to much fun, so like with so many of Tony’s other ideas I thought I would have a go too.

The challenge I set myself was to take Tony’s work and see if I could use it as the basis on an online events booking system. The idea was to enter event details into a spreadsheet which would then be used to automatically populate a site and spreadsheet, delegates being able to book in via a form. Here’s how I got on …

**\#Issue 1 – This solution requires [Google App Scripts](http://www.google.com/google-d-s/scripts/scripts.html) which aren’t available to in the basic version of Docs/Site. To get access you need at least a [Google Apps Standard](http://www.google.com/apps/intl/en/group/index.html), which is free but you need to register with a domain url.**
**Update:** I like to think it was because of my post that [Google now makes Apps Script available to everyone](http://googledocs.blogspot.com/2010/03/apps-script-gallery-for-google.html) using Spreadsheet ;-)

Having signed up to Google Apps I then started following Tony’s posts on [Updating Google Calendars from a Google Spreadsheet](http://ouseful.wordpress.com/2010/03/04/maintaining-google-calendars-from-a-google-spreadsheet/) and [Maintaining a Google Calendar from a Google Spreadsheet, Reprise](http://ouseful.wordpress.com/2010/03/07/maintaining-a-google-calendar-from-a-goole-spreadsheet-reprise/). My spreadsheet layout is almost identical except I added 2 more columns, an ‘Added Date’ and ‘ID’.

[![image](https://hawksey.info/wp-content/uploads/2010/03/image_thumb.png "image")](https://hawksey.info/wp-content/uploads/2010/03/image-1.png)
Tony, I was able to set the locale for the date in the spreadsheet via ‘File –> Spreadsheet Settings’ and added some conditional formatting to highlight items which hadn’t been added yet.
Below is the code I dropped into the spreadsheets script editor.

```

function processEvents() {
 //declare vars
 var cal = CalendarApp.getDefaultCalendar();
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var dataSheet = ss.getSheetByName("Events"); // ref sheet name (thought I might have ended up with multiple sheets)
 var maxcols = dataSheet.getMaxColumns();
 var dataRange = dataSheet.getRange(2, 1, dataSheet.getMaxRows() - 1, maxcols);
 var data = dataRange.getValues();
 var cal = CalendarApp.getDefaultCalendar();
 var site = SitesApp.getSite("sub-tweeter.net", "mashe-events-from-calendar"); // .getSite(domain, sitename)
 var annPage = site.getAnnouncementsPages();
 var col_added, col_title, col_desc, col_tstart, col_tstop, col_loc, col_num, col_ID;
 // pull column header
 for (var j=1;j<=maxcols;j++){
   var header= dataSheet.getRange(1, j, 1, 1).getValue();
   switch(header){
     case "Status/Action":col_added=j-1;
     case "Title":col_title=j-1; break;
     case "Description":col_desc=j-1; break;
     case "Start": col_tstart=j-1; break;
     case "Stop": col_tstop=j-1; break;
     case "Location": col_loc=j-1; break;
     case "Number of places": col_num=j-1; break;
     case "ID": col_ID=j-1; break;
   default:
   }
 }
 // pull data
 for (i in data) {
   var row = data[i];
   var added = row[col_added];  //Check to see if details for this event have been added to the calendar(s)
   if (added == "Add"){
     // collect event details
     var title = row[col_title];
     var desc = row[col_desc];
     var loc = row[col_loc];
     var tstart = row[col_tstart];
     var tstop = row[col_tstop];
     cal.createEvent(title, tstart, tstop, {location:loc, description:desc}); // create calendar event
     var message = "Start:" + Utilities.formatDate(tstart, "GMT", "dd/MM/yy HH:mm") +"<br/>Finish: "+Utilities.formatDate(tstop, "GMT", "dd/MM/yy HH:mm")+"<br/>"+desc; // prepare message
     site.createAnnouncement(title, message, annPage[0]); // add announcement to site
     var annList = site.getAnnouncements();
     var eventID = annList.length; // get announcement ID
     // set value in spreadsheet
     var v = parseInt(i)+2; // +2 is an offset to do with the numbering of rows and the "blank" header row 0;
     dataSheet.getRange(v, 3, 1, 1).setValue(eventID); // add the event ID
     dataSheet.getRange(v, 2, 1, 1).setValue(new Date()); // add today's date/time to 'Added Date' column
     dataSheet.getRange(v, 1, 1, 1).setValue("Added"); //set the fact that we have updated the calendars for this event
   }
 }

```

My main tweaks were:

- dynamically capture the data range using getMaxRows() and getMaxColumns();
- define a Google Site to push data to (as well as the Calendar);
- adding the event to a Google Site using createAnnouncement(); and
- adding the ‘added date’ and ‘Event ID’

Here is the [public calendar](http://www.google.com/calendar/hosted/sub-tweeter.net/embed?src=mhawksey%40sub-tweeter.net&ctz=UTC) and [Google site](http://sites.google.com/a/sub-tweeter.net/mashe-events-from-calendar/events) populated with data from [the spreadsheet (web page view)](http://spreadsheets.google.com/pub?key=tV2Qoz2NXwRgJCfmPoyU6Bg&single=true&gid=0&output=html) - I'm currently playing around with the script a bit more so these links are turbulent.
Having gotten this far my next plan was to use the [Expense Report Approval Tutorial](http://www.google.com/expense_report_approval.html) as a basis for a booking form which would allow a submit/approve workflow. To do this my plan was to have a template spreadsheet for each event which I duplicate using the event ID as an identifier.

**\# Issue 2 – Google Apps Script doesn’t have a method for duplicating spreadsheets**
I think I might have got around this by creating a function which would cycle through an existing spreadsheet, storing the values and then creating a new sheet reversing the process to populate it with data. I don’t think it would however have duplicated form functionality. The concept was beginning to unravel however because …

**\# Issue 3 – You can only have one form per spreadsheet.**
Oh dear!

It was all getting a bit too messy which is a shame because it would have been fun to use [Contact Services API](http://www.google.com/google-d-s/scripts/service_contacts.html) to add delegates to Google Contacts either using the [setNotes()](http://www.google.com/class_contact.html#setNotes) or [setUserDefinedField()](http://www.google.com/class_contact.html#setUserDefinedField) to record events the delegate had signed up for, dietary requirements etc. and lots more interesting stuff.

One solution to the who registration issue might have been to [Leveraging Google App Engine services from scripts](http://www.google.com/appengine.html), but that is going into a whole realm of coding I would prefer to avoid.
So SharePoint anyone? ;-)