Automatically close a Google Form after hitting response limit
August 2, 2015 - Tech
I’m a part of an organization that runs a weekly event that is limited to 20 participants.
In the past, the original organizer sent out emails and text messages to see if people were interested in attending. If they were, he would keep a manual count. Clearly, this wasn’t an efficient way of gathering responses, so when my partner and i took over the coordination of the event, we immediately implemented Google Forms to automate RSVPs.
The problem was our extremely limited space, so after we send out an email, we found ourselves constantly checking the responses to see if we had reached capacity. So the next step in our goal of efficient event management was to somehow automatically close the form when as soon as we hit our limit.
formLimiter automatically sets Google Forms to stop accepting responses after a maximum number of responses, at a specific date and time, or when a spreadsheet cell contains a specified value. Great for time-bound assignments, event registrations with limited seats, or other first-come, first-served signup scenarios.
Perfect for limiting those straightforward one-time events, but our issue came from our recurring, weekly event. We used the same form and response collection every week.
Basically, the “close form when it hits this number” wouldn’t work for us because we were adding new responses to the form every week and Google forms continues counting responses from when it was last live. So with this option, we would have to reset the delimiter after every week (40, 60, 80, etc.), somehow reset the responses or start a new form with a new formula every week. None of which were appealing.
We wanted a form that we could use over and over, closing off responses after hitting a specific number and re-opening it with minimal work.
Limit Number of Google Form Responses for a Recurring Event
Since “maximum number of responses” forced us to do a couple extra steps every week, I looked into using the “spreadsheet cell contains a specified value” as a way to limit responses for a recurring event.
Okay, a specific cell had to have a “specified value” and the form would close. How could we force everyone signing up to enter the exact value we needed to appear in the destination cell?
For example, if the value the form is looking for is “apple” we needed that cell to say “apple” to trigger the form closure. It couldn’t be multiple choice and it couldn’t be just any answer — it had to be “apple” (or whatever I chose).
In this option, we had to pose a question in the form with only one acceptable response. We ultimately decided to add a required field that asked the prospective attendee to confirm their attendance:
By submitting this form, you are confirming your attendance for this week’s event
And the only available answer from the drop down was “I agree”. Since it was required, one couldn’t submit the form without choosing “I agree” — this worked not only to trigger the auto-limit, but also had a secondary benefit of reinforcing to the participants that we expected them to show up (we’ve had problems with a few no-shows here and there).
So, this week (and every week following), once formLimiter detects that an “I agree” appears in cell D21, it will automatically close the form and no longer accept responses.*
* This worked only because we alway removed the previous week’s responses to an “ARCHIVE” tab,