Create a seat booking form with Google Forms, Google Sheets and Google Apps Script

Google Form Seat Booking and Registeree list

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:

Basic Seat Booking Form Google Forms

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.

Contents

Creating the Basic Form

For this little-ish tutorial, we’ll just create a basic Google Form that contains:

  • The registeree’s name
  • The seminar they wish to book.

You could make the seat booking part of a much larger form, but for now, let’s keep what we can simple so we can zoom in on the important stuff.

In a folder in your Google Drive, create a form. Go ahead and title it with at least a short-answer for a name item and a session multiple-choice item.

DS9 Google Form Seminar Seating Basic Template

When you add a short answer text, true or false of multiple-choice to a form, you will be prompted to choose a title and options to a Google Form. Collectively, these are referred to as an item. This is useful to keep in mind when we come to write our Google Apps Script code.

If you are following along, here are the 4 seminars I added, so you can copy and paste into your multiple-choice item options:

  • The history of Terok Nor: 10:00 -12:00hrs: (100 seats remaining)
  • The Dominion War: 12:00 – 14:00hrs: (100 seats remaining)
  • DS9 with The Sisco: 14:00 – 16:00hrs: (100 seats remaining)
  • The Science of the Bajoran Wormhole: 16:00 – 18:00hrs: (100 seats remaining)

I’ve added in the seats at my max seating capacity. This will be what your first registeree sees when they try to book a seminar.

Top

Setting up Google Sheets to Recieve Responses

We can set our Google Form up to push responses to a Google Sheet by going to the Responses tab of our Google Form just above your form template.

To the right, you will see a green and white spreadsheet icon. Click it and a pop-up window will appear with an option for you to create a new Google Sheet for your responses or add the responses to an existing Sheet.

Google Forms Connecting Google Sheet

Go ahead and connect our form to a new sheet. This will open the new sheet for you. Keep it open. We will work on this next.

Before you leave, make sure Accepting responses is toggled to on.

You should be able to now test your form by selecting the Preview Icon and submitting a few dummy requests.  Adding a few dummy requests will help you prepare your Google sheet for the next step.

Google Form Preview Button

As you are adding new dummy requests you will be able to see it populate the connected Google Sheet in the Form Responses 1 tab.

Google Sheets Form Data

You can see that each response contains a Timestamp, the registrant’s name and their chosen seminar.

Of course, the number of seats won’t change yet. We haven’t coded in that option.

Top

Getting a total count of all users for each seminar on Google Sheets

We are going to use Google Sheets to get a count of all users booked for each seminar. We also want a more human-readable list of registrants for our website. First, let’s start by creating our list of registrants for each seminar. This will help us to understand the formula we will use to display each registrant in their own column. Take a look at the example below:

Google Sheets Seat booking names for each seminar
Registerees tab

Ah… that’s so much easier to read and it’s gonna look pretty cool on my seminar website when I embed it too. I even managed to use the Starfleet Academy colours (NERD!!!).

Top

Creating the Seminar Registeree List

Setup

Create a new Google Sheets tab named Registerees. In the first two rows, add your title and details.

On row 3 add the time headers starting from Column A:

  • Time
  • 10:00 – 12:00
  • 12:00 – 14:00
  • 14:00 – 16:00
  • 16:00 – 18:00

Next, on row 4, add your seminar headers. It is important here to make sure that the headers are an exact match to the Google Form seminar options minus the time and seating. We will be using these headers to search for registerees in our Form Responses 1 Google Sheet tab.

Copy and paste the seminars from the form into the sheet now. Starting from cell B5.

In column A from row 5 down, number the rows to indicate your total seating.

At this stage everything in your Registerees  tab should look like this:

Registeree List for Seat booking Google Sheets
Registerees tab

Go ahead and apply a bit of formatting to make it look all pretty.

Top

Formulas

On row 5, under each header, we want to see each registeree for that seminar.  We can reference our Form response 1 tab data for this so it is automatically updated each time a Google Form is submitted.

Form response filter for Google Sheets

Noice!

To do this, we are going to combine a few formulas to get our desired results.

One approach we can take is to use the Google Sheets FILTER function. The FILTER function allows us to select a range – in our case, just the name – and use some related column- in our case, the seminar column – and filter out any undesirable values except what we want.

FILTER takes a display range parameter and then any number of column conditions. For example:

=FILTER(display range, column used to filter with condition 1, column used to filter with condition 2,...)

In our example, we could filter  in our Registeree tab in cell B5 like this:

=FILTER('Form responses 1'!$B:$B, 'Form responses 1'!$C:$C = B$4)

Here the display range is the list of registeree’s name. The next parameter after the coma is saying that if these registerees have selected a seminar that is the same as the header in cell B4 of the Registerees sheet tab, then display that name.

However, there is a bit of a problem. The header in the Registerees sheet tabs and the results in column C of the Form responses 1 sheet tab will never exactly match.

Why?

Because each time a new user submits the form, the seminar options will change their sheet number. This is what we would be looking at in the Form responses 1 sheet tab:

Form response 1 sheet tab each updated item changes the text

See how all those seat numbers change? There is no way we can run a filter on this unless…

… we use REGEXMATCH.

REGEXMATCH can quickly turn into a very complicated beast. I have a whole tutorial on how to use it with the FILTER and the COUNTA function here:

Google Sheets: Counting and Filtering Rows where Cells Contain Particular Values.

For the time being, let’s keep it simple. We are going to use REGEXMATCH to search inside each cell for text matching our header. It can also contain other stuff, but if the text matches that header somewhere in the text we want to select that item for our filter. Here is how our updated formula would look:

=FILTER('Form responses 1'!$B:$B, REGEXMATCH('Form responses 1'!$C:$C,B$4) =TRUE)

Our display range has not changed, but where we had our simple column that we applied our filter condition to, we now have our REGEXMATCH. inside the FILTER, our REGEXMATCH will examine our filter condition range and if any text inside each cell in that range matches our header we will say this is a TRUE cell. Completing our match we then move back to our FILTER and if that cell is then equal to TRUE, we want to display that person’s name.

But wait a minute, Yagi! What if someone sign’s up twice for the same Seminar? 

Great point, kid!

Because we are allowing folks to register to multiple seminars, we can’t limit the form submissions to one time only in Google Forms. However, we can simply display only the unique people who signed up for the seminar.

We can do this by using the UNIQUE function.

=UNIQUE(FILTER('Form responses 1'!$B:$B, REGEXMATCH('Form responses 1'!$C:$C,B$4) =TRUE))

Now once the filter has completed its task, UNIQUE will run to weed out any duplicates.

We are almost there with our formula. The only problem is that if no one registers for a seminar, then the FILTER formula will display a n/a error. This is a little untidy and we are going to embed the Registeree sheet into our cool Starfleet Academy website after all. Let’s fix this up by using the IFERROR function to hide the n/a error.

=IFERROR(UNIQUE(FILTER('Form responses 1'!$B:$B, REGEXMATCH('Form responses 1'!$C:$C,B$4) =TRUE)))

Formula is done! Go ahead and apply it to the other 3 seminars.

IFERROR UNIQUE FILTER REGEXMATCH Google Sheets

You should now be able to go back to your form and add a few more dummy submissions. Your Registerees along with your Form responses  1 sheet tabs will now populate automatically.

Top

Count the number of seats booked for each Seminar

Create a new Google Sheet tab and name it Count. Here we are going to simply list:

  • All of the seminars by name.
  • The number of seats taken.
  • Calculate the remaining seats.

This sheet tab is important. We will reference it in our Google Apps Script code in a moment.

Let’s take a look at what it will look like:

Calculating the remaining seats Google Sheets

In column A, we have added the Seminar names including the times (The times are optional here). Again, make sure you use the exact same item names that you used in your Google Form, minus the number of seats remaining part.

If you are using this project as a template for your own project, you will be able to add all your seminars here. For example, if you have 10 sessions that you added to your Google Form, add the ten sessions here.

Skipping over to cell F1, we have the reference for the Seat capacity for the lecture room. This is referenced when calculating the seats remaining on the sheet.

To calculate the Seats taken in Column B, we are going to take the formula we created earlier in our Creating the Seminar Registeree List chapter and wrap a COUNTA function around it. This is what the formula would look like for cell B2.

=COUNTA(IFERROR(UNIQUE(FILTER('Form responses 1'!$B:$B, REGEXMATCH('Form responses 1'!$C:$C,A2) =TRUE))))

This will effectively count all the registrants for the seminar in the corresponding cell in column A of the Count sheet tab. It won’t count any duplicate registerees for that seminar too. (Does it look familiar?)

COUNTA IFERROR UNIQUE FILTER REGEXMATCH Google Sheets

In column C we are simply subtracting the values in the Seats Taken column, column B, from the Seat Capacity, cell F1.

This will all update automatically when a new form response is submitted. We will grab the data from range 2A:4C in our Google Apps Script code to help us with our form formatting.

Here is a link to the full Google Sheet. Go to File > Make a copy to play with it yourself:

Deep Space 9 Seminars (Responses)

Top

The Code

Now for the exciting bit. Either in your Google Form or your connected Google Sheet open the Google Apps Script Editor:

  • Google Forms: Select the 3 vertical ellipses (top right). A drop-down menu will appear. Select Script Editor
  • Google Sheets: On the menu select Tools > Script editor

Copy and paste in the code below.

Note! Use the pop-out function for the code so you can reference it beside the tutorial. 

Top

Setup

You should be able to use the code above as is after you have added your own set of Globals (lines 10-20) and completed two simple steps.

Top

Globals

First, set your SEATS variable to your seating capacity. In my example, you can see it is set to 100.

Next, add the ID of your form in the FORM_ID variable. You can find this in the Google Form’s URL.

Google Forms ID in URL

On line 5, you can see the SESSION_ITEM_ID. Each item in a form has its own unique id.

In our example, we have two items. The short-answer to input the registerees name and the mutiple-choice item for each of our seminar sessions.

We want to find the item ID for our seminar sessions. We will have to write a little piece of code to discover this. In your Script editor go to File > New > Script file. Name the file Test. This will create a Test.gs file.

Open this file by clicking on it in the sidebar. Copy and paste in the following code:

Save and then run the code by hitting the play button or go to Run > Run function > findItemID.

The first time around, you will probably be prompted to go through a warning. Select through all the warnings and the code will run.

Once the code indicates it is complete, hit Ctrl + enter. This will display the log. It should look like this, but with your own item IDs:

Google Apps Script Log to view Form Item IDsIn our log we can now see that the item with the title, Sign-up for a seminar, has the id 1492288992.

Copy and paste the id into your SESSION_ITEM_ID variable back in your Code.gs file.

Next in our list of GLOBALS is the SS_ID (Line 7). This is the spreadsheet id and can be found in the URL of the Google Sheet:

Spreadsheet ID in URL

Copy and paste in your own id from your Google Sheet. Then on the next line, add the Sheet Tab id (SHEET_NAME) that holds the count data for your Seminars. If you have been following along this probably won’t change.

Finally, on line 9, add your data range. This should include the Seminars, Seats Taken and Seats remaining columns all the way down to the last seminar.

Calculating the remaining seats Google Sheets

Top

Add a trigger

Next, we need to add a trigger to our main onFormSubmit() function in our Code.gs file.

To do this go to Edit > Current project’s triggers. A new window tab will appear.

Google Apps Script Edit Current Project's triggers

This will take you to the G Suite Developer Hub. Down the bottom right of your screen, you will see an Add trigger button. Click it and a pop-up form will appear.

Select the onFormSubmit function to trigger. Keep the deployment to Head. Ensure the event source is From form. Then, select the event type as On form submit so that our code runs each time a form is submitted. To the right, change failure notifications to notify you at the frequency you wish. Then hit save.

G Suite Developer Hub add trigger from

Close the G Suite Developer Hub and head back to your Script Editor.

Top

Run the Code from the Editor once

Next, run the code from the script editor one time. This will prompt you to run through a series of pop-up warnings that you need to select through to run your code.

If you have added a few dummy names on your Google Form already then you will see that your form had updated the seating.

Now go and add a few more dummy names to make sure everything updates okay.

This should be all you need to have your seat booking form up and running. We’ll now go on to explain the code if you re interested or you can skip to the conclusion to see a couple of limitations to the seat booking sheet.

Looking to learn more about Google Apps Scripts in a more structured format? Udemy has some great courses that can get your from the basics to a real Google Apps Script pro!

Got a more specific problem you need help with, but don’t have the time to develop the skills? Fiverr’s your best bet to find a skilled Google Apps Script professional to solve your problem quickly and cheaply. *

*The above affiliate links have been carefully researched to get you to what you specifically need. If you decide to click on one of these links it will cost you just the same as going to the site. If you decide to sign up, I just get a little pocket money to help pay for the costs of running this website.

Top

Code breakdown

Let’s take another look at the code after the GLOBALS. You can pop it out so it’s beside the breakdown for ease of reference. We’ll be referencing the row numbers to make it easy to follow.

Our main function is the onFormSubmit function.  Once the form is submitted, it pushed all the form results to the attached Google Sheet. We want to give the backend enough time to get this done before we start getting our data from our Google sheet.

Top

Sleep(milliseconds)

To do this, we used the sleep function (Lines 50-65). The sleep function takes an amount of time in milliseconds as a parameter. After some brief experimentation, we went for 2000 milliseconds. The sleep function gets the current time. It then loops through 1e7 times, or 10,000,000 iterations subtracting the new current time each loop from the start time. If the difference is greater than the set milliseconds, then the code breaks and we can continue with our onFormSubmit function.

Currently, Google Apps Script does not apply ECMA script 6 promises to make this easier, but it might soon.

Top

Back to onformsubmit()

Getting the spreadsheet data

Back in the onFormSubmit() function on lines 18-22, we call the SpreadsheetApp class. This references three of our GLOBALS  variables to get:

  • the spreadsheet ID (SS_ID) of our connected sheet.
  • the sheet name (SHEET_NAME). In our case, Count.
  • the data range of each seminar session (SESSION_DATA_RANGE). This will contain the Seminar name, Seats take and Seats remaining.

Session Data Range for Seat Booking Google Form

Finally, we will get the values of each cell in the range. This will create a 2d array of values.

Top

Getting the form item

On line 24, we call the FormApp class. We first want to open our form by ID. Again using one of our assigned GLOBALS, FORM_ID.

On the next line, we want to get the correct item. For us, this is the multiple-choice question with all our seminar sessions. We use our GLOBALS again with SESSION_ITEM_ID.

Top

Filter out seminars with full seats

Our first task is to filter out any seminar where the seating capacity is full. We don’t want to display these on our form the next time a user loads it. On lines, 27-30, we use the convenient filter method. Filter iterates over all of our rows of seminars looking at column 2, the seats remaining column. If that column has a number greater than zero, then the filter will return it. Otherwise, it will be discarded. This will be stored in the remainingSessionData variable.

Top

Close the form if all seminar seats are full

If all the sessions are full the filter will return an empty array.

We don’t want the form to continue to be open if all the seats for all the seminars are full. So we will close the form on lines 33-38.

To do this we check to see if the length of the remainingSessionData  array is equal to zero. If this condition is true, we grab our form class and set the accepting responses to false, effectively closing the form. We then add a helpful comment for our users informing them that registration is now closed.

Top

Update the seminar items

Alternatively, if there is still is some seats full in some of the seminar sessions, we want to update the form with the remaining seminars, lines 40-47.

Our first step is to iterate through our remaining session data and map out a new string of texts that will contain:

  • The seminar name and time
  • The remaining seats

We use the map method for this which will return a string for each of our seminars items. The remainingSessionData variable contains a 2d array of each row of remaining seminars. On line 42, we combine the zeroeth item (The seminar name) with the 2nd item (The seats remaining). Each remaining row will be saved in an array a little something like this:

Lastly, we will use our form sessionID variable to update our session items.

sessionID.asMultipleChoiceItem().setChoiceValues(itemList)

We inform the FormApp class that we are creating a multiple-choice item. We then have it set the choice values of our remaining items. setChoiceValues expects an array which we prepared with our itemList variable.

Done!

Top

Embedding the Form and the Registerees sheet tab

Embedding the form and the Resgisteree ( I know, should have been registrants. What’s wrong with me!?) Google Sheet tab is a relatively simple process.

Top

Embed a Google Form

To embed your Google seat booking form, go to the form editor page and in the top right, select the Send button. You will see a Send via option with 3 icons. Select the <> icon to get the embed code.

Adjust the width and height and copy the code. You should be able to embed the code into your site.

Top

Embed the Google Sheet

For the Registerees Google Sheet tab, you will only want to embed that sheet and not all the others. To do this go to File > Publish to the web. A popup window will appear. Select the embed option and make sure the tab selected is set to Registerees.

Registeree Google Sheet tab publish to the web

Select PublishA warning will appear asking you if you wish to publish the tab. Click Okay and then you will get your embed code. Copy it and paste it into your website.

<iframe src="https://docs.google.com/spreadsheets/d/e/2PACX-1vRDe7Q1bCsuWLl4rexHRiOSzaPJM-U6r5AyQvy-0mQB-OjGydd7Z8t_jfbVMNMWadLPKAjU2-wBRp7i/pubhtml?gid=1280430975&amp;single=true&amp;widget=true&amp;headers=false"></iframe>

You’ll probably have to adjust the width. and height so it looks good in your page.

<iframe width="100%" height=650 src="https://docs.google.com/spreadsheets/d/e/2PACX-1vRDe7Q1bCsuWLl4rexHRiOSzaPJM-U6r5AyQvy-0mQB-OjGydd7Z8t_jfbVMNMWadLPKAjU2-wBRp7i/pubhtml?gid=1280430975&amp;single=true&amp;widget=true&amp;headers=false"><span data-mce-type="bookmark" style="display: inline-block; width: 0px; overflow: hidden; line-height: 0;" class="mce_SELRES_start"></span></iframe>

You can see another example of embedding Forms and Google Sheets in websites by following this tutorial series:

Creating a Short Google Form Survey and Embedding it into a WordPress Post – Part 1 of Google Forms in WordPress with Live Chart Project

Top

Conclusion

Once you have tested everything, go into your attached sheet. Delete out all the dummy form submissions in the Form responses 1 tab and run the code in the Script editor once so that it is back to the original seating for your seminars.

Using the above set up you should be able to quickly create your own ticketing or seat booking form.

The form as it stands is probably good enough for casual events, extra-curricula seminars for a college or school or a club sign-up. However, it does suffer from a relatively slow update speed on the form.

Top

Some limitations

If you were following along and adding new dummy registerees in quick succession, you would have probably noticed that the form does not always get a chance to update fast enough.

Further, if a user is still filling out a form, another user can start a form. They will have the same seats displayed as the first user. This will become a problem when your remaining seats start to approach zero. You could easily overbook on a busy form before the code has a chance to remove the seminar.

One solution might be to gather the user’s emails. Then reference the Registerees sheet tab. If there is a user on the list past the seating limit, then automatically send them an email to inform them of the unfortunate news.

Top

In the end…

In the end, this is still a pretty solid tool for seat bookings. I have used it on a number of occasions for after-hours seminars.

Give it a go, tell me how you used it. I really enjoy hearing how some of these tools are used from you, my readers.

Happy coding!

 

~Yagi

 

Leave a Reply