Linking a Google Form with data from the responses in the Spreadsheet [Event/Resource Booking]

is there a way to use a Google Form for event booking and cap entries for individual parallel sessions?

This was the question I got from my colleague Kenji Lamb who is helping organise Game To Learn: Take 2!. The event has a number of parallel sessions with limited spaces in each. Ideally Kenji wanted a booking form which would track numbers and prevent people booking into sessions which were full. So is it possible? The quick answer as demonstrated by the existence of the registration form is yes, but there was some head scratching along the way (and there is one very big caveat at the end of this post). Here's how I did it:

Step 1: Getting live booking numbers out of Google Spreadsheet

The Google Visualization API provides a way to access spreadsheet data using their query interface (I've used this previously in gEVS – An idea for a Google Form/Visualization mashup for electronic voting). The problem is to do this you need to make your entire spreadsheet publically available, okay for anonymous voting but isn't ideal if you are collecting personal information like email addresses and phone numbers. Here's what I picked up from the documentation:

non-embedded visualization runs with the privileges of the person viewing the visualization, so the spreadsheet must either assign view access to everyone, or to the specific person running the visualization from Google Visualization Docs

The other option is to 'Publish as a web page' selecting a single sheet. There are a number of different formats you can choose for the data including CSV (comma separated values). I'll come back to what you can do with the CSV data in a second.

So we have a way of getting some data out but it needs to be filtered to leave the bit you need – how many places are left. My solution was to use a series of sheets and functions to filter the data. In this spreadsheet there are 5 sheets:

  • Form Raw – this is where the form data comes into
  • Parsed Data – this strips out extra session title/day info text using
    • =IF(ISERROR(FIND("17th May", 'Form Raw'!C2)),"-","X") – for day; and
    • =LEFT('Form Raw'!D2,4) – to extract the first 4 digit identifier
  • Session Counts – counts the numbers of things using COUNTIF (I could have skipped this sheet and just gone to the free spaces but wanted an easy way for admin to tweak numbers)
  • Public – the bit were most interested, how many spaces are left
  • Templates – used for sending out email confirmation to delegates

Initially I prefilled Parsed Data with functions in the cells but for some reason when the form was being submitted it would remove the function from the equivalent Parsed Data row. Consequently I had to write an Apps Script which would programmatically insert the function (I was going to use Apps Scripts anyway to automatically send an email confirmation when someone booked. I'm not going to go into the detail of the App Script in this post, the code is in the shared spreadsheet (if you need clarification on anything use the comments below).

Step 2 Snaffling your Google Form

So we have a public sheet which has a summary of the available slots, how can we use this in the associated booking form? There is very little you can do with the form has hosted by Google, but if you know your away around html and have somewhere to host your alternate version it's easy enough to style Google Forms by viewing your live web form then copy the source to another webpage.

Step 3 Converting CSV to something more useful instead (PHP/JavaScript)

Initially I tried grabbing the .csv data from Google using jQuery but kept getting access denied errors, so instead I resorted to PHP. Here's my PHP code snippet. PHP handles .csv well, and it would have been easier if my host used 5.3> because I could have used str_getcsv() – there's a workaround I modified to use the file version of this function.

I could have used PHP to dynamically write the form indicating if there were any filled sessions, instead I opted to use jQuery as that sort of thing is a lot easier. First converting a PHP array into a JavaScript object (got this trick from here):

<script type="text/javascript">
    var list=<?php echo json_encode($data); ?>;
</script>

Step 4 Updating the form

Next disabling any of the parallel session select options that are full:

function checkForm(){
$('select > option').each(function() {
        var str = $(this).text();
        var id = str.substring(0,4);
     if (parseInt(list[id]) <= 0){
         $(this).attr('disabled', true)
         $(this).text("[FULL] "+str);
     } });

Because Google do form validation server side to prevent the user submitting the form with missing data and being redirected back to an unmodified version of the form you need to validate before submission. Fortunately jQuery has a Validation Plugin which makes it easy to do validation client side. All you need to do is include

<script type="text/javascript"
    src="http://ajax.microsoft.com/ajax/jquery.validate/1.7/jquery.validate.min.js"></script>

in the head of the page then initialise validation with:

$("#yourFormName").validate();

To make parts of the form required just add the class=”required” to the form elements.

Some other things you can do in Google Spreadsheet (I did not know that)

First off Google have added some nice email notification options to Google Spreadsheet. These let you setup immediate or digest notification for changes to the spreadsheet or other events.

image

Second thing I discovered is that Apps Script are by default only editable by the person who first added it to the spreadsheet (for others who open the script editor they can see the script but there is a little padlock next to the script name). To allow any of the spreadsheet collaborators to edit you need to go into Tools > Scripts > Script Editor > Share > Share settings.

Third thing, if you set up a script trigger that includes sending emails they will be addressed as coming from the account of who ever setup the trigger. I decided to spare myself queries from Game To Learn delegates to my personal gmail account by sharing the spreadsheet with one of our catchall email addresses, then using this one to create the email trigger.

The big caveat

As the public data like shown on this page is only updated every 5 minutes there is a chance that the session could go over quota if bookings are made in quick succession. If anyone knows a way around this I would be very grateful ;-).

Finally if you want to have a go I’ve setup an example form here. You can see how the validation works and if you select session ABC1 and wait 5 minutes before refreshing the page ~:-S you can see what happens when a session is full (for this example the spreadsheet keeps adding an additional space every 10 minutes)

3 thoughts on “Linking a Google Form with data from the responses in the Spreadsheet [Event/Resource Booking]

  1. John

    Awesome,
    Thanks for sharing. There is so much that is possible with Google Apps with a little ingenuity.

  2. Pingback:

  3. Pingback:

Comments are closed.