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’.
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? 😉