How it was made: eAssessment Scotland Google Spreadsheet booking form with website and Twitter integration

Even though our RSC is toast after this July we’ve continued our support for the eAssessment Scotland Conference until that time. Our involvement with eAS goes back to the very beginning with 2009’s Strategies across sectors and since then this event has continued to grow becoming the largest UK conference dedicated to e-assessment.

For this year we’ve moved the booking form to Google Spreadsheets (moving to the cloud to avoid the issue of our soon to be mothballed event system), which has been an opportunity to continue to tweak my previous work on Google Spreadsheet based booking systems (Event Manager v3, Game To Learn: Take 2, framework for custom interfaces). For this version I’ve implemented:

3rd party website integration (e.g. hosting the form remotely and in our case wrapping it in a WordPress template);
better handling of parallel session selection e.g. quotas for maximum number of delegates;
email notification confirming booking details and registration; and
Twitter integration with automatic ‘tweet out’ of registrations and adding delegates to a named Twitter List

I realise not everyone is going to be interested in all of this, but the solution is modular so you can pick the bits you like. So if you want to replicate any of this here’s how to do it (video followed by written instructions):

Setting up the Spreadsheet

To get you started I’ve published a template for you. What this has is a basic outline with the beginnings of a Google Form and some templates for the emails sent to delegates. It also comes preloaded with some Google Apps Script to handle the emailing and Twitter stuff:

*** Booking Form with Twitter Integration Template ***
BTW if anyone knows a better way of sharing templates could you let me know (avoiding the iframe embed because it’ll be stripped in some RSS readers)

From the link above click ‘Use this spreadsheet’. This will take you in to the form view. From here you can add additional questions (you can edit at a later point if you prefer). Protected names (e.g. field names that are used later in the script) are First Name, Email Address, Twitter Username and ‘Tweet Out’. You can change these if you really need to, but you’ll also have to find them in the script.

Optional – Parallel session selection

If you want to include parallel session selection Add item > Choose from a list to the form. Start you session option text with a unique identifier e.g. ‘[PM3] App, App and Away’. You can also have multiple parallel sessions, morning and afternoon etc. Some tweaking is required on the main spreadsheet to access this go to ‘See responses –> Spreadsheet’. Next:

On the ‘Public’ sheet enter the sessionIDs and sessionName you used in the Form. Note: these must exactly match the Form values or the embed later on won’t work.
In the Max column you can also change the number of delegates.
In the Count column there is a formula that counts the of the sessionID in the ‘Bookings’ sheet.
If you are embedding the form (as explained later) you’ll also need to publish the Public sheet by selecting Share –> Publish as a web page and changing Sheets to publish to ‘Public’.

Spreadsheet setup cont’d

If you are not already in spreadsheet view click on ‘See responses –> Spreadsheet’. Next you might want to edit the spreadsheet ‘Templates’ sheet. The workflow to bear in mind when filling this in is:

Delegate fills in form
On submit they get automated email response summarizing entered data
Confirming the booking allows the admin to send confirmation email, adding delegate to twitter list and if allowed ‘tweets out’ booking

To incorporate other booking information in the confirmation emails you can use the markup ${"The column name"} in the template eg ${"Email Address"}

Optional – Connecting to Twitter

Adding the twitter bits is another optional bit. If you want to do it then in the Template sheet there are some cells for a default twitter message and twitter list (if you are using a Twitter list of delegates you’ll need to create this in Twitter as well). You’ll also need to register for a Twitter API key which is both free and relatively painless. Below are the instructions for registering for a API key and using it with the spreadsheet:

Register for an API key with Twitter (if you’ve already registered a Google Spreadsheet/Twitter mashup you can reuse your existing Consumer Key/Consumer Secret).
In the form these are the important bits:
     – Application Website = anything you like
     – Application Type = Browser
     – Callback URL = https://spreadsheets.google.com/macros
     – Default Access type = Read & Write
Once finished filling in the form and accepting Twitter’s terms and conditions you’ll see a summary page which includes a Consumer Key and Consumer Secret. Take a copy of these as you’ll need them later.
Back in the spreadsheet there should be a Bookings menu option with Configure Twitter (you might need to select this option twice, once to authorize the script and a second time to enter the customer key and secret saved from earlier.
Now in the Spreadsheet select Tools > Script Editor … and from the Script Editor window select Run > authorize.
This should bring up an authorization message box, click on Authorize.
In the Twitter Authorization window enter your login details of the event Twitter account and select ‘Allow’.

Now, time for a cup of tea …

Quick recap: we’ve copied the template spreadsheet; edited the booking form; customised the email templates; and if needed connected the spreadsheet to twitter. Next I’m going to show you how to embed the Google Form in another website. This is again an optional step, you can just use the live Google Form but you won’t be able to prevent full parallel sessions from being selected (unless you manually remove them from the form).

Optional – Embedding booking form in to another website (in this case WordPress)

To do this you are going to need access to a webserver where you can host a .php file. I’ve created two versions of the file you’ll need. There is the:

Both of these files need a bit of editing. In both files some variables are needed:

$formKey – in your Google Spreadsheet click on Booking –> Get Form Key and copy the value from the dialog box
$redirectUrl – this is the page you want to send users to after they submit their booking
$dataUrl – you can get this value by opening your Google Spreadsheet and clicking Share (top right) –> Publish as webpage. T
his should open a window similar to the one below. Things to check are you are publishing ‘Public’ with automatic republish on changes and that the get a linkto the published data is CSV and the Public sheet. The value you need for $dataUrl is the entire link generated in the text area.

What the code does is read the Google Form and sessions counts and then modifies a copy of the original form. To prevent incomplete data from being submitting the code also uses the jQuery Validation plugin to check the data.

Required fields and format are set around line 90. The plugin documentation contains information on additional configuration but hopefully you can see the pattern of field name (as defined in the form html source) and requirements. So in the example code:

entry.2.single is required
entry.5.single is required and in email format
entry.6.single is required and a valid twitter username without ‘@’
entry.8.single is used for a parallel selection. If a selected session is full it has no value so the delegate is asked to select again

Summary

At this point you are probably due another cup of tea. I realise that this isn’t an ‘out of the box’ solution, but hopefully there is another of an outline to help you do something similar/use information here to solve a different problem/loose many hours of sleep.

Event booking amplification using Twitter

The Twitter integration still intrigues me. Currently 80% of delegates have allowed us to ‘tweet out’ their booking, which is a lot higher than I anticipated. At the same time none of the delegates with Twitter accounts have retweeted their ‘tweet out’ meaning that in terms of amplification the information is only reaching the followers of @eassessscotland. May be the next iteration should be to get delegates to optionally sign in with Twitter, pre-populating some of their registration form and then choose to tweet out on their own account as well as the conference account. [It would also be good if the process could add delegates to the lanyrd site, but their API doesn’t allow this … yet

PS I’ll be doing my App, App and Away workshop at eAS11 which has a couple of places left. This session covers very simple uses of Google Forms and Apps Script to support assessment.