Welcome to Jennie Magiera's Technology in Education Blog:

Redefining the (digital) Classroom

Tuesday, November 4, 2014

Creating Google Form Session Sign Up Limits with FormRanger

Lately I've had many instances where we've created sign ups for PD sessions, observation visits or student programming and wanted facilitate this process via a Google Form. However, in all of these examples, we also have wanted to limit the number of people able to register for each option.

Although Andrew Stillman's Google Form AddOn FormLimiter seems like a natural choice, it limits the entire form based on the total number of submissions, regardless of the options selected. We wanted the actual sessions to be limited or closed once they hit their individual maximum, while still allowing registrants to select options that had yet to meet their max.

There may be a more elegant solution out there, but I thought I'd share our workaround. Using another one of Andrew Stillman's Google Form AddOns, FormRanger, and some CountIf and If formulae, I was able to change sessions options to "FULL" in the Google Form once they hit a certain cap.

Note that this doesn't prevent the registrant from still registering for that session, but it does prevent the registrant from knowing what the session was before. When they access the form to fill out, they just see "This session is full, please choose another." If they still select it, that will be session they have chosen in the spreadsheet responses. Should you choose to be kind, you could set up a FormMule email to shoot out an error message prompting them to re-register for another session. In the many instances we've done this, I've never had someone do this.

Below is a screencast walkthrough of how to do this, complete with an explanation of the formulae you need to accomplish it!

2 comments:

  1. Hi Jennie, awesome tutorial! I go one step further with an extra column in Column A called Revised Session Name for Form and use the formula =Unique(B2:B) and have formRanger populate with that column. This way it eliminates options that cannot be selected.
    Here is a quick video detailing it: https://www.youtube.com/watch?v=O0TBZFs8IGQ
    and the spreadsheet containing the formulas is here: http://goo.gl/pecjEs

    ReplyDelete
  2. Jennie,

    Great video. I found that if I coded the Available to set the value to "" Null when the limit had been reached then FormRanger didn't display that option on the next person to open the form. In my testing that worked. John's solution also works.

    I did find a situation where the process doesn't work but there is a fix. If two people have the form open and they both choose a period that only has one slot remaining, then the first one gets the period in the Responses sheet and the second one gets a blank. I think that is because the second one is choosing a response that is no longer available but is displayed on their form. As you said an email telling the person that their selection was filled would handle the problem. I can see the happening when a form is given to all folks at one time like during a class.

    I had always wanted to try FormRanger but never got around to it before your video. Thanks.

    ReplyDelete