Google Forms, Google Sheets, Google Apps Script: SpreadsheetApp, FormApp
In this tutorial, we are going to build a simple seat booking Google Form. It will contain a registeree’s name and the session that they wish to attend.
Each time a registeree submits a request to book a seat for a session, that seat is taken from the list displaying only the remaining seats for that session for when the next user submits the form.
Take a look at the example below (click to expand the image):
If all the seats have been booked for a seminar, then that seminar will not appear on the form. If all seats have been booked for all seminars, then the Google Form will close.
We will even create a live list of attendees that we can embed on our website using Google Sheets.
Google Forms can’t do this for you out of the box. We will need to use Google Sheets to store our bookings and do some manipulation of the data. Then, we will use Google Apps Script to update the Google Form with the number of seats each time the form is submitted.
The Google Apps Script script has been prepared in a way that someone with limited coding skills can use it as a template by plugging in a few global variables. Alternatively, a more advanced user can incorporate the code into a larger project.
I’ll assume you have the basic abilities to create a simple Google Form and have used Google Sheets.
This tutorial can be followed as a useful step-by-step guide or if you are more confident, you can simply jump to the code using the contents below.
Google Sheets: COUNTIF, ARRAYFOMULA, FILTER, REGEXMATCH, REGULAR EXPRESSIONS (RE2)
Okay, wait! Stop!…
…I know the byline contained regular expressions, but I promise you I won’t just leave you with the formula for you to figure out your own jumbled mass of jibberish characters or inundate you with a list of commands and leave to attempt to piece it all together.
I promise to give you some clear examples with an explanation of each to you can apply it to your project.
Take a deep breath, mate, and let’s get cracking.
We are going to look at two related scenarios:
Imagine that you have a huge list of items. You have a hunch that some of the cells contain certain values of interest for you. You want to build a new list with only those values in them.
Imagine that you have a list of full names, and you want to use Google Sheets to create a new list of full names that only contain John.
You have that same huge list again, but this time you only want the total count of all the rows that contain certain values in each cell.
Imagining that list of full names again, you are now going to get a total count of all full names that contain John in it.
We’ll first go through how to create these formulas and then provide a number of clear examples on some common criteria for searching cell for values using REGEXMATCH and regular expressions.