Creating a dynamic sign-up sheet using Google Forms and formRanger

Continuing on from my recent post about the Google Apps for Education Summit KL, I started work on a form for sign-ups for (possibly) Parent teacher conference (PTC). 

Using the excellent formRanger script by +Andrew Stillman , I was looking to create a form that dynamically changed the list based upon user selection - the idea being that once an item was chosen, it would be taken from the list.

The formRanger script allows a user to provide a list of options for a multiple choice question from a spreadsheet. The power of this is that it allows a multiple-choice option to be easily edited. The tutorial can be seen below and explains this a lot better than I can.

However, what it doesn't do automatically (unless I am wrong) is allow you to dynamically change that list (although my version is similar to a tutorial here). I wanted a form that can remove items that a user has chosen - in my example case, removing superheroes from a possible list of choices when someone has already selected it.

 I created 3 sheets on my 'superhero' spreadsheet:

1. The responses (from the completed form)
2. A chosen superhero page (to flag up if a superhero was chosen)
3. A page to display the remaining courses available (looking at the ones not yet flagged)

So, I first created my list for formRanger (some superheroes):

OK, so Joker isn't a superhero.

The next step was to work out how to get the spreadsheet to calculate if an option was chosen. My untidy 'hack' was to simply use a countif as a flag (perhaps a rather inefficient means, but it worked for me):

Formula checks if the sign-up list from the sheet FormResponses
has been completed using countif

Finally, I created a filter page to recreate the list based upon the flag (i.e. if the flag was 1, the superhero would not be taken across):

Batman is chosen by the user

countif flags the choice

The filter only moves the non-flagged items onto the new sheet

I then configured the formRanger script to run on submit, on change and every 5 minutes (to be on the safe side!) and set the assigned column to the amended list on the final sheet.

Once the script is run, after about 6 seconds (not sure if I can amend this time delay), the form is reloaded and Batman is no longer available to choose:

Finally, here's a quick video that hopefully explains it in a little more detail.

Google Apps for Education Summit - KL

Just got back from the Google summit in KL and have had loads of great ideas, particularly on Google spreadsheets, but some other nice tips as well, which I may go into in a different post.

One of the main things that sticks in my mind right now (along with some other great tips from +Jay Atwood ) was the ability to use Google translate inside a spreadsheet.

At the moment I have no idea how I can use this (as I work in an international school with a good number of EAL learners, i'm sure that there may be some uses), but it's a great little feature. Along with this, I saw some more excellent uses of Google scripting inside spreadsheets for automating a wide range of tasks. Most of the scripts are written by a guy called +Andrew Stillman, who has (I think) websites here and here. 

My current favourite is a script for showing student grades on a google site - basically, they (the students) log in and are shown their grades in graphical format. This graph is pulled from a google spreadsheet that is stored in a user's drive. A script is added to the site (pretty much a copy/paste job) to make the chart load.

The tutorial for this is linked below:

This isn't a video, but you can click here to watch the real one!