[Work in Progress] Creating a framework for custom form interfaces using Google Apps Script

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):

Google Spreadsheet used to create form

you immediately after a few clicks get this (or click here to to see the live version of the form):

Form made from Google Spreadsheet

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.

Setup for Google Spreadsheet

Setup for Google Sites

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.

  1. 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.
  2. 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?


Join the conversation

comment 19 comments
  • melissa

    Is there a way to just remove the selection from the drop down menu instead of labeling it FULL?
    Is it possible to auto confirm if there is space open, taking out the step of confirming?
    It looks like the time periods can only be times, how would I allow text in that spot? This would work great but I need to modify it a bit to work for parent/teacher conference sign ups so instead of times it’s a teacher or date, then the drop downs include time slots.

  • melissa

    me again..I’ve been fooling around with this doc and script and I like it very much, nice and simple but powerful. like i said before i’m trying to create a simple sign up that only shows or allows the user to submit a time slot that is not taken.
    A few other questions – is there a way to only send them the text of the selected drop down menus? For instance, I have 3 drop down menus for Wed, Thurs and Fri, each have time slots. The user will only select one time slot from one drop down menu. When it sends them the email it gives a summary of the drop down menus that still have “no selection”.
    I could put all days’ time slots in one drop down menu but I may end up having several different teachers and using one drop down menu for each teacher.
    any thought of integrating calendar with this?
    great work.

    • Martin Hawksey

      Hi Melissa – all the things you are asking for are possible but require the script to be further customised (which unfortunately Ii don’t have the time to do). A better starting point for you might be this other solution which I use as an alternative method to the one outlined above.
      Calendar integration should also be possible to do (a degree of this is already used in my other Google booking spreadsheet thing while I’m thinking about it)
      Hope this helps

  • melissa

    Awesome I will take a look at those. Great work!

  • Tawmess

    Martin —
    Depending on your setup, for your problem with “You can’t embed in another website (except Google Sites)” — it sounds like the same issue documented here…
    In your failing iframe, if you were pointing to the spreadsheet’s UiApp published as a service — then simply try “comment #3”, of the above link…
    (Without the fix of comment #3, the same symptoms occur, when embedding a published spreadsheet script, even within a Google Sites webpage — either via the script gadget, or an iframe gadget. so, it might help with your symptom/issue.)

  • Michael O'S

    When I click on the link * SuperEventForm Spreadsheet * the spreadsheet opens BUT I cannot save a copy for myself (the option is grayed out). Could you send/share this spreadsheet with me please? I am very interested in looking at the code and figuring out how it works.
    Thank you!

    • Martin Hawksey

      just make sure you are signed in to google when you open the doc (I know firefox has a problem remembering you’re signed in – just check your name appears top right)

    • Scott

      did you get this to work ever? I can make the copy fine, but there just appears to be no script attached to the spreadsheet. It would be a huge help to me to have this script (even just as text without the associated spreadsheet).

      • Martin Hawksey

        @Scott – I copied the script into your version of the spreadsheet. Make a copy can be flaky at times

        • Scott

          Thanks so much Martin, I really appreciate the help.

  • Scott

    The “SuperEventForm Spreadsheet” linked to no longer appears to have the attached script? am I missing something. After I copy when I go to script editor it brings up a blank script template?

    • Martin Hawksey

      Sometimes it takes a couple of seconds for the Google servers to catch up

  • Michael O'S

    Still cannot see the code for the “SuperEvent Form Spreadhseet”. Opened it, made a copy and no code……

  • Eliseu Rodrigues

    I made a copy of SuperEventForm Spreadsheet but there is no script.
    Could you please publish the script or send it to me by e-mail?
    Thank you very much.

Comments are closed.