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

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 and ever since I’ve been an avid follower of his work on OUseful.info. When I saw Tony had started playing around experimenting with using Google Apps Scripts 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 which aren’t available to in the basic version of Docs/Site. To get access you need at least a Google Apps Standard, 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 using Spreadsheet 😉

Having signed up to Google Apps I then started following Tony’s posts on Updating Google Calendars from a Google Spreadsheet and Maintaining a Google Calendar from a Google Spreadsheet, Reprise. My spreadsheet layout is almost identical except I added 2 more columns, an ‘Added Date’ and ‘ID’.

image


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 and Google site populated with data from the spreadsheet (web page view) – 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 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 to add delegates to Google Contacts either using the setNotes() or 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, but that is going into a whole realm of coding I would prefer to avoid.
So SharePoint anyone? 😉

chevron_left
chevron_right

Join the conversation

comment 12 comments
  • Tony Hirst

    [Way too kind… 🙂 Glad you find some of the things I post occasionally useful, or at least, interesting to play with!]
    Re: there only being one form per spreadsheet, if each event is templated in the same way, can’t you have a field in the form that specifies the event and then use that to direct what info gets added to what event sheet?
    If the form spewed items into a single form handling sheet, then you could run a script over that sheet that looks for new events, and copies the relevant details over to the appropriate event sheet?
    I’m not sure if the onEdit function applied to the form results sheet could be used to automatically fire a function that copies results to a specific event sheet when a form is submitted?
    There’d be privacy issues of course – being able to see details of folk in one sheet no matter what event they were registering for – but sys admins offer have that power anyway!

    • Martin Hawksey

      Ah suddenly the mists lift and a direction becomes clear … a reprise maybe in order 😉

  • links for 2010-03-11 « Onlinejournalismtest's Blog

    […] MASHe » Updating a Google Calendar and Google Site from a Google Spreadsheet (the beginnings of an … 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 … (tags: googledocs howto spreadsheets) […]

  • Marcello

    This sounds like a fantastic, and easy to implement script. But I haven’t got it working yet.
    You’ve stated the links will be turbulent, but is it possible to download/view a working spreadsheet?
    How about making a copy of the original so we can take a look at it.
    Thanks

    • Martin Hawksey

      Hi Marcello – working on an improved script. Should be out later this week. I’ll post another comment here when ready.
      Martin

  • Marcello

    Thanks Martin,
    I’ve also been trying to get this, slightly different script to work, but I’m still not sure why it’s not working.
    http://www.google.com/google-d-s/scripts/sites_tutorial.html
    I’m looking forward to the updated script.
    Basically all I want to be able to do is have a Google Docs form where people submit their school information. I will then use another spreadsheet (Spreadsheet Mapper v2.0) to process the info into a KML file, and use Map Channels to create a really cool Google Map.
    With that same Google Docs form, I want to have a script that runs, say once a day (or even every time the spreadsheet is updated), that creates an Announcement on the Google Site, with a nice HTML layout. Something like they are doing in the above Soccer Site example.
    The other main feature of my site is another Google form that collects school event information. That info is then exported to a Google calendar, and a Google sites announcement (as in the Soccer example).
    Is that asking to much? 😉

  • Marcello

    @Martin, you’re a legend!
    I watched the video, and was pretty impressed.
    It’s going to take me some time to get my head around the code, and get it doing what I want, but thanks a lot for sharing.
    Amazing stuff!
    It wasn’t asking too much…

  • Kennith Sellars

    Is there a way to maintain each event with it’s data on a seperate sheet within the spreadsheet and still have it auto populate or push data to google cal?

  • melissa young

    I am not a programmer and most of the language here frankly frightens me. 🙂 But conceptually, I know what I want, but don’t have the skills, nor do I know some one who would to make it work.
    I am the Math curriculum director for a large urban district. I have “assigned” every teacher a particular standard to go and research and find resources. They are copying and pasting these into a google docs survey form. On my end, I am getting a lengthy and cumbersome spreadsheet. I figured out how to share it “live” so that others can see whats been completed so far. But what I would really like is for the fields to populate a web-based lesson plan template so that people don’t have to scroll a mile down or across to find information. I have seen google docs unit and lesson plans too. I know there is something i am missing… I would love for their info they find to drop into something that looks like a nice web-page wiki or other template looking site. Anyone out there who can help me?? or who can I find to help? Thanks!

Comments are closed.

css.php