Over the last couple of weeks in some spare moments I’ve been chipping away at a flexible event form booking system based in Google Spreadsheet. It all started with a need to find a way to take event bookings for some of our bigger events which include parallel session selections. Previously I’d come up with Linking a Google Form with data from the responses in the Spreadsheet [Event/Resource Booking], but the problem was there was a 5 minute delay in latest booking numbers filtering through to the live web form.
Having started dabbling with Google Apps Script UI Services for the Turning Google Spreadsheets into a personal or group bookmarking service I knew I could use this to make a bespoke interface for a Google Spreadsheet which would pull live data, but rather than hand coding this for each event I was keen to come up with something more reusable.
What I’ve come up with is a way for users to design their own forms using a Google Spreadsheet as an interface. So by entering your event like this (click here to see the live version of the spreadsheet):
you immediately after a few clicks get this (or click here to to see the live version of the form):
What is happening here is the Google Apps Script is reading the spreadsheet pulling coded part of the sheet and dynamically making the form. At the same time it’s also aggregating existing bookings to workout if a session is full and making unavailable. It’s a bit like using Google Forms but with a bit more flexibility. Usually at this point I’d dropped the code into the post but there’s too much of it. Instead below is a link to the spreadsheet. Once open if you File –> Make a copy you’ll be able to see the code via the Tools –> Script editor.
*** SuperEventForm Spreadsheet ***
[For those have trouble copying this with the script the code is here]
Setup instructions
I designed the code to work in Google Spreadsheet or in Google Sites (in Sites the booking form and details are still controlled from a Spreadsheet). I’ve prepared two short videos which show you how to setup the booking form for both.
Known issues
As with everything I do there are a couple of rough edges but there are two very important factors to be aware of.
- Warning: This panel was created by another user, not by Google – I imagine Google have put this there to prevent people creating custom user interfaces to maliciously grab user information. A user can dismiss the bar and never see it again, but on first encounter it can be quite alarming and perhaps the language could be toned down or additional explanatory information under the More button.
- You can’t embed in another website (except Google Sites) – with other Google products like Forms and Docs you can embed content in other websites. The usual way you do this is using the iframe tag
But will all of this become obsolete with Google Apps Scripts new web based interface designer for Site and Spreadsheet
One of the reasons for pushing this out a bit greener than I’d like is last night Google soft launched a new web based interface designer (GUI) for Google Apps Script. As you will see from this thread (I’m finkhawk BTW 😉 the Google Engineers are still working on documenting this new tool and as yet I don’t know if you could replicate the ‘dynamic’ form above using it. No matter what it should be a great tool for those that want a bit more freedom from Google Forms.
What next
Whilst putting this together I had another couple of ideas to take it in a different direction. The first was to incorporate the file upload utility in Apps Script to turn this into a assignment submission tool. The idea would be the tutor could design a submission pro-forma by using the ‘booking details’ part prompting the learner to reflect on their submission (anticipated mark, where they did well etc) and simultaneously upload their document to Docs. Submission could trigger some automatic emails like acknowledgement of receipt. The tutor could then record grades/feedback in the spreadsheet and distribute this to students as in emails (basically reconfiguring the send joining instructions part).
I was also wondering if there was a way to build on this with a plugin style architecture. In particular I was thinking you could combine so of the Twitter/Spreadsheet tools like auto add to list or something new like ‘a tweet out on registration’ to build in some event amplification.
So what do you think? Do you see potential? Is there anything else you’d like to see it do?