Recently in the Office of Online Learning at the college where I work we were investigating options for online registration (for staff development). Our college has a few generic systems in place, but nothing was really precisely aligned with what we needed.
As an avid Google Doc user, I suggested using Google Forms to capture registration data. Getting the forms to work is a no-brainer (I even designed it with data validation and different paths, based on answers). But we needed to be able to cut-off registration once all the seats filled. For our purposes, we didn't need the data to integrate with our student information system, because Google Spreadsheets would dump out a nice Excel file that could be used with our bigger, more robust Access database.
Google Drive offers a spectacular suite of tools. Add to that the thousands of people around the globe that contribute their knowledge and talent, and the breadth and depth of utilities is astounding.
So the question was, how do we manage cut-offs? Sure, we could keep an eye on the registration, and modify the form every time a course closed, but that isn't precise (nor is it scalable).
I tapped my buddy Malcolm, who is a programming genius, and he came up with some code in Google Scripts to accomplish the automatic cut-off. While it worked, it's really not user friendly if the form is to be adapted to other, non-technical users throughout the college.
I happened upon a script in the Script Gallery (for Google Spreadsheets) called "formRanger". And. It. Is. Awesome.
Essentially, you design the form, fire up formRanger in the "responses" spreadsheet, and it
will populate the drop-downs (or multiple choice questions) of the corresponding form with information from the spreadsheet. This is useful if you constantly change the selection items in a form (for instance, a weekly reading list or different types of projects for students to select from). I find it much easier than working directly with the options in a form.
But there is another technology in the showcase today - and that is basic Google Sheets logic. Nothing hard, I promise. But Brian Gray has a sweet tutorial on a way to make a column in a spreadsheet dynamic, based on availability. So the idea here is to merge the two technologies. Use Brian's magic to have a column appear of all courses that aren't filled, and then Andrew Stillman's formRanger to bring those choices into the form.
This, I believe, will be my longest, most technical (read: boring) blog to date. I promise a fluffier one for next week. Today's blog will appear intimidating. But I swear - it is not terribly difficult. I rank it a 3.5 out of 5 for difficulty. Just stay the course!
+-+3-5.png "3.5 out of 5") |
| 3.5 out of 5? That's only 70% difficult. Pssshhhh... |
So without further ado, I present formRanger!
STEP 1: Create a Google Form
Google makes it very simple to create forms - it is easier (and slightly more robust, in some ways) than creating assessments in most learning management systems (and the process is almost identical). The only catch - you can't activate the coveted "Script Gallery" from the new Google Spreadsheet, so make sure you either disable the "new Sheets" (go here and make sure "Try the new Google Sheets" is not checked) or re-purpose an existing spreadsheet/form that was created with the older version.
For demonstrative purposes, I've created a very simple form:
+-+Border.PNG "Registration form (Google Form).") |
| Perfect form. |
Since we will be populating the drop-down list (or multiple choice, etc.) based from the Google Sheet, no need to enter values.
 |
| Don't worry about filling in options, the formRanger will do that! |
STEP 2: Go to the Google Sheet (Responses Spreadsheet)
Google will automatically create a spreadsheet where the responses go, based on the questions in the form.
 |
| Ever take a screenshot of a cursor? You have to be super quick. |
At this point, you should check to make sure that you have the old Google Sheets (don't worry, you'll be able to go right back to the new Sheets when you want). The best way is to go to the "Tools" menu, and see if it has "Script gallery".
 |
| We're in business! |
STEP 3: Get formRanger and install it
Search for "formRanger" in the search box. Note that you will need to install it for this particular sheet, but you can check the code out at GitHub if you're curious.
 |
| formRanger sounds like it should have theme music. |
But it's not enough to install it, the script needs to be authorized. This is the scary part for some people, because the permissions might be offputting. I briefly reviewed the code, and I didn't see anything malicious. I also have a pretty good feeling about Andrew Stillman, the author. He seems legit (by the way, you should check out some of his other scripts for Google - this guy makes workflow and teacher productivity scripts that outperform many big services).
 |
| No worries - go ahead and authorize. |
The next step is to run the initial configuration. It's painless! By the way, if you've made it this far, keep going. I know what it's like to skim through a tutorial with pictures and get overwhelmed. Don't worry! This isn't bad at all.
 |
| "Initial configuration" makes you sound like a programmer. Or a robot. |
STEP 4: Populate the form with choices from the Sheet
Again, formRanger is really intended for populating choices in a form question through the spreadsheet (saving you from having to edit all the choices in the form each time you want to deploy it). The walk through from Jay Atwood does a really good job explaining this. But we want to leverage a little more out of it. Essentially, we want the list of options to be dynamic (so in my example, let's say there are five opportunities for Session I training dates, and four opportunities for Session II. Each session can accommodate 30 people, and I want the drop-down list for Session I to only show sessions that don't have 30 people yet enrolled. So we have to do a little magic here - we have to somehow make only the viable training sessions appear in a list. If you're an Excel wizard, then it isn't hard. But if you're not, you can just pay attention here. Or check out that tutorial on YouTube by Brian Gray that I mentioned earlier, who does a tremendous job explaining it (although his video talks about a previous version of formRanger - but his video is still worthwhile).
Buckle your seat belt, it is going to get a little intimidating. Below is a screenshot of the original form data spreadsheet, with two entries.
 |
| Original form data |
Create another sheet in your Sheets document. This is where your options are going to come from. Note that in my form, I have two drop downs. I think it is sufficient to show the "magic" on one of the spreadsheets, and you can replicate it on your own. I called my sheet "Choices for Session I" (and "Choices for Session II" for the other sheet - although they could both reside on the same sheet, if desired).
I created six columns: Session Title (where I put the options for training classes - ultimately, this is what will show in the form), Response Counts (which will have a formula that counts the registrants from the Form Responses), Response Limits (which I hard code "30", as that is my "cap"), Filtered Sessions (another formula will be in here in a minute), Backup Text (which will be the message displayed if all sessions are filled), and Available Sessions (which will also have a formula and will be the ultimate decider of what is displayed in the drop-down for the form - either the remaining days, or the backup text).
 |
| Here's what the "Choices for Session I" spreadsheet looks like before I put formulas in. All the data you see here should be populated by YOU. |
In Response Counts (Column B), we want to tally up the number of times each of the respective Session I dates have been selected. On the actual registration sheet, "Form Responses" (which is the default name), Column E (in the form data) is where the users choice for Session I training will appear. So we need to do a simple countIf statement that counts data in Column E from "Form Responses" and compares it to each of the respective data from Column A in "Choice for Session I" sheet. So, in cell B2, use:
=countif('Form Responses'!E:E,A2)
And then drag down the handle to apply that formula to B3-B6. Unless you already have data in the "Form Responses" sheet, you should have all zeroes.
By the way, even if you don't use Excel or Google Sheets often, the countIf function is really handy (as is the countA function, which will just count any cell in the range that has any data in it).
 |
| This is what Column B should look like right now. |
For the Filtered Sessions column, what we really want is a list of all sessions that are not filled up yet. This isn't too bad, either. The filter command looks at a column of data (in the biz, we call that an "array"), and compares it to some specification. in this case, we want to filter all the choices from A2:A6, and look to see if the number in the corresponding cells of Column B is less than the value of the adjacent cell in Column C. Here's what you should use (change your range if you have a range that isn't the same as A2:A6):
=filter(A2:A6,B2:B6<C2:C6)
That's it! I actually submitted some registrations from the live form before taking the next screenshot to show you what Column D will look like when people start registering. Note that the options for Wednesday and Thursday are not in Column D, because they both have 30 people registered. In the words of the immortal Colonel John "Hannibal" Smith (portrayed by the memorable George Peppard), "I just love it when a plan comes together".
 |
| This is what Column D should look like. |
At this point, we are almost done with the magic. Theoretically, Column D should always be the most up-to-date list. The problem is that if all the sessions are filled, then Column D has some wonky behavior (it starts returning errors).
Therefore, Column F, Available Sessions, needs to display either the remaining sessions or the "Backup Text". This is extra nerdy spreadsheet stuff. If you really want to understand it, just know that spreadsheets get sassy if you try to use cells with errors in them (and go look up the iserror function). Otherwise, just copy and modify this, as needed:
=if(not(iserror(D2)),filter(D2:D,not(iserror(D2:D))),filter(E2:E,true))
This next screenshot shows Column F when there are still some available sessions, and then when all the seats are filled:
 |
| Look at how Column F behaves based on the contents of Column D. |
OK. So where are we?
Oh yeah. We just used some magic to make Column F have an up-to-date list of all available courses (or a pleasant message if there are none).
Back to formRanger! We need to use formRanger to tell the Google Form to use choices from Column F of the affiliated spreadsheet. So, here it goes. Go to the formRanger tab in the spreadsheet, and select "Assign form item(s) to column(s)". Scroll down to the proper questions (in my case, I need question 4 (denoted by Q4). I checked "Populate options from column", and then I made sure that the "Sheet" field had the proper sheet ("Choices for Session I"), and the "Column" field indicated the "Available Sessions" choice.
 |
| Make sure the settings are accurate, and they correspond to the proper question on the form. |
All the heavy lifting is done! Use the "Refresh form" option from the formRanger tab, just to make sure the form gets updated. If you want, use the "Set triggers for form refresh" option from that tab, too. The options are:
- Every time the form is submitted
- For registration cut-offs, this is probably your best bet
- Every time the spreadsheet is edited
- This is particularly handy if you are re-purposing the form or constantly changing the information; not so much in this registration example
- Every five minutes
If you are reading this, then congratulations! You've made it! I promise something fun, easy, and fluffy for next week. Until then, have fun with your cut-offs (forms and/or jeans).
Spreadsheet & Form
Very informative. I had no idea about the power of Google scripts/forms. I like!
ReplyDelete