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.
Contents
- Creating the Basic Form
- Getting a total count of all users for each seminar on Google Sheets
- The Code
- Embedding the Form and the Registerees sheet tab
- Conclusion
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.
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.
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.
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.
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.
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.
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:
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!!!).
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:
Go ahead and apply a bit of formatting to make it look all pretty.
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.
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:
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.
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.
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:
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?)
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:
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 |
/*#### Seat Booking Form #### * * Used on a multiple choice item in Google Forms, this can update the number of seats * after each booking. * * Requires: A Google Form with a connecting Google Sheet. * */ //#### GLOBALS #### var SEATS = 100; //Your seating capacity var FORM_ID = "1nzWf5MG9xKMoC1w4klyu18kKFxkavRkSTcE0gT8WQkc";//Add your form ID var SESSION_ITEM_ID = 1492288992; //Use findItemId function in Test.gs var SS_ID = "1E-jlB1uSiGscv-VOeF0HfKCUtF5z9FE2b5YPhi1Fg4Q"; //Add your Spreadsheet ID var SHEET_NAME = "Count"; //Add your Sheet tab name var SESSION_DATA_RANGE = "A2:C5"; //Add the range of booking items your selecte sheet tab /* ################################################################### * Seat booking function * * Requires: Set trigger Edit>Current project's triggers > select onSubmit * * After the form is submitted, it check the seating information from the * sessions and then updates the form with the remaining seats. * If seats are full for 1 session, that session is removed. * If all seats are full for all sessions, the form is closed. */ function onFormSubmit() { //Wait a little for the form to submit the results to the Google Sheets sleep(2000); var sessionData = SpreadsheetApp .openById(SS_ID) .getSheetByName(SHEET_NAME) .getRange(SESSION_DATA_RANGE) .getValues(); var form = FormApp.openById(FORM_ID) var sessionID = form.getItemById(SESSION_ITEM_ID); //Filter item data by seats remaining < SEATS var remainingSessionData = sessionData.filter(function(item){ return item[2] > 0; }); if(remainingSessionData.length == 0){ // Close the form. form.setAcceptingResponses(false); form.setCustomClosedFormMessage( "Registration is now closed." ); }else{ var itemList = remainingSessionData.map(function(item){ var itemCombined = item[0] + " (" + item[2] + " seats remaining)" return itemCombined; }); sessionID.asMultipleChoiceItem().setChoiceValues(itemList) } }; /* ################################################################### * Sleep function * * Paused the processing of the form data for an assigned period of time. * * @param {number} a number in milliseconds */ function sleep(milliseconds) { var start = new Date().getTime(); for (var i = 0; i < 1e7; i++) { if ((new Date().getTime() - start) > milliseconds){ break; } } } |
Note! Use the pop-out function for the code so you can reference it beside the tutorial.
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.
Globals
|
1 2 3 4 5 6 7 8 9 10 11 |
//#### GLOBALS #### var SEATS = 100; //Your seating capacity var FORM_ID = "1nzWf5MG9xKMoC1w4klyu18kKFxkavRkSTcE0gT8WQkc";//Add your form ID var SESSION_ITEM_ID = 1492288992; //Use findItemId function in Test.gs var SS_ID = "1E-jlB1uSiGscv-VOeF0HfKCUtF5z9FE2b5YPhi1Fg4Q"; //Add your Spreadsheet ID var SHEET_NAME = "Count"; //Add your Sheet tab name var SESSION_DATA_RANGE = "A2:C5"; //Add the range of booking items your selecte sheet tab /* ################################################################### |
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.
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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
/* ################################################################### * Helper function used to find the Item ID that you wish to apply * the seat booking code to. * It logs the item ID and it's title. * * NOTE!!! Make sure you udate FORM_ID in your Code.gs page. */ function findItemID(){ var form = FormApp.openById(FORM_ID); var items = form.getItems() var item_list = items.map(function(item){ return [item.getTitle(),item.getId()]; }); Logger.log(item_list) }; |
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:
In 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:
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.
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.
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.
Close the G Suite Developer Hub and head back to your Script Editor.
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.
Need help with Google Workspace development?
My team of experts can help you with all of your needs, from custom app development to integrations and security. We have a proven track record of success in helping businesses of all sizes get the most out of Google Workspace.
Schedule a free consultation today to discuss your needs and get started or learn more about our services here.
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 |
/* ################################################################### * Seat booking function * * Requires: Set trigger Edit>Current project's triggers > select onSubmit * * After the form is submitted, it check the seating information from the * sessions and then updates the form with the remaining seats. * If seats are full for 1 session, that session is removed. * If all seats are full for all sessions, the form is closed. */ function onFormSubmit() { //Wait a little for the form to submit the results to the Google Sheets sleep(2000); var sessionData = SpreadsheetApp .openById(SS_ID) .getSheetByName(SHEET_NAME) .getRange(SESSION_DATA_RANGE) .getValues(); var form = FormApp.openById(FORM_ID) var sessionID = form.getItemById(SESSION_ITEM_ID); //Filter item data by seats remaining < SEATS var remainingSessionData = sessionData.filter(function(item){ return item[2] > 0; }); if(remainingSessionData.length == 0){ // Close the form. form.setAcceptingResponses(false); form.setCustomClosedFormMessage( "Registration is now closed." ); }else{ var itemList = remainingSessionData.map(function(item){ var itemCombined = item[0] + " (" + item[2] + " seats remaining)" return itemCombined; }); sessionID.asMultipleChoiceItem().setChoiceValues(itemList) } }; /* ################################################################### * Sleep function * * Paused the processing of the form data for an assigned period of time. * * @param {number} a number in milliseconds */ function sleep(milliseconds) { var start = new Date().getTime(); for (var i = 0; i < 1e7; i++) { if ((new Date().getTime() - start) > milliseconds){ break; } } } |
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.
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.
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.
Finally, we will get the values of each cell in the range. This will create a 2d array of values.
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.
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.
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.
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:
|
1 2 3 4 5 |
var itemList =[ "The history of Terok Nor: 10:00 -12:00hrs: (32 seats remaining)", "DS9 with The Sisco: 14:00 - 16:00hrs: (9 seats remaining)", "The Science of the Bajoran Wormhole: 16:00 - 18:00hrs: (15 seats remaining)" ] |
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!
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.
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.
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.
Select Publish. A 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&single=true&widget=true&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&single=true&widget=true&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:
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.
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.
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
Hi Yagi,
I want to try and use this method for a ticket reservation form I am creating for a school play at my high school. We have three showings and I want parents to be able to reserve multiple tickets. Would it be possible to add reserving multiple tickets to the system that you have described above?
Hi George,
You certainly could adapt this. You will need to create tickets choice in your form and add that to your calculations in your formula. Give it a go! Would love to hear how you went.
~Yagi
Did you manage to create this form to reserve several places? I’m very interested in the method!!!
By the way, thanks for this great tutorial!!
When Adding a Trigger I did not get the option of choose From form for Select Event Source. Any ideas?
Hi Vix,
Did you create the script form-side or sheet-side?
~Yagi
I have same problem as Vix. I created the script sheet-side, and the only option available for ‘Select Event Source’ are From spreadsheet, Time-driven and From calendar.
Hi Amira,
Interesting. What was the result of creating the script form-side? This tutorial was before the new IDE (Editor) so there might be a few differences. You can copy and paste your code into your Form Apps Script file to test.
~Yagi
Thank you so much for this tutorial. As a “never-have-dealt-with-any-code-whatsoever” person I was only confused a couple of times but was able to logically figure out where the hang-ups were. I inadvertently found myself needing exactly what you’ve created here because I volunteered to set up a sign-up sheet for my church. I’m so thankful for the Trekkie on the other end of this comment section!
Thanks for the kind words, Breanna.
It’s great to hear how you made use of the tutorial.
Live long and prosper, mate.
~Yagi.
Hi Yagi,
Thank you for sharing how to set up a booking system. I have used this to set up a booking system myself which sends an email confirmation to the registrants which contains an edit response option. I was wondering if there is a way to have a ‘cancel booking’ option that will only display for those using the edit response option from the confirmation email? At the minute I have to have the option showing on the form for it to work as an option for those using the edit response button in the email.
Any thoughts would be greatly appreciated.
Hi Tracy,
Great usage, Tracy.
You can certainly make some modifications to this code to create a cancellation form that either deletes a row or sets a column value to cancel. That would be my approach.
Cheers,
Yagi
Hi! I’m stuck at getting the Item ID.
the Test script just runs when i hit ctrl+enter.
How long does it take before the item ID appears? What could have gone wrong?
Hi Dawn,
It can take a little while to load now that GAS has moved to the V8 engine.
Try going to view >> Executions
If you still are not getting a value, recheck your form ID.
The Test script didn’t work for me either. I hunted for the ID in the page code. I’m sure he’ll be able to help you, but I wanted you to know that you aren’t alone!
Hi Brenna and Dawn,
I ran the script again and it seem to be running fine.
I did have a late night thought. In your main Code.gs file, did you add the FORM_ID variable?
ReferenceError: FORM_ID is not defined (line 8, file “test”)Dismiss
this is what I get after trying to find the item ID
Hi Sergey, did you update the FORM_ID at the top of the Code.gs file?
Hi Yagi,
Thanks a LOT for the clear and concise tutorial, just what I needed. I’m trying to add a little functionnality : in my forms, users can select a session and also indicate how many users will attend. My goal is for the script to update the seats accordingly (say, if the user indicates 4 attendees, 4 seats should be deducted). I have a feeling I can do it mainly by modifying the formula in Google Sheets ? I’m trying atm but am more familiar with Excel. Any pointers would be appreciated !
Thanks a lot.
Hi! what is the best way to create a form that does this with many more options (around 75-80). I am creating a form that students/parents can use to reserve a specific time slot to retrieve their belongings. We are doing this over the course of 3 days in 15 minute increments (3 students per increment) from 8:30am – 11:30am and 12:30pm to 4:00pm. My initial form was divided into sections based on answer selections of which day they would like to come in. How do I modify the script to update with the # of remaining seats for each section?
Hi Yagi,
Thanks for this awesome guide. I have experienced an issue with some users (8%) who are booking but the time slot is coming empty in the C column. On the form i have made the multiple choice item mandatory. Strangely for some users there is no data and there is no way for me to find out which seminar they booked. Can increasing time in sleep function help?
Regards,
Dungeons
Hi, thanks for the great tutorial! When I add the trigger, I only get the option ‘From Spreadsheet’ and not ‘From Form’. Any ideas on how to overcome this?
Hi Gerrit,
One guess would be that you might have created the script inside the spreadsheet instead of the form.
I’m not too sure what else it would be.
Cheers,
Yagi
Thank you for this great tutorial. Of course, I have a question. For my multiple choice question, I have set “Go to section based on answer”. Here is the issue – as soon as you update the selection text (updated seat count) or remove an option, the “Go to section…” setting is changed back to Go to next section. Is there a programmatic setting I can enable to keep my setup? I cannot find one, just hoping you may have some insight. I think it is a limitation – as I tested with formRanger, and it causes the same issue. Any thoughts on if this is possible?
Yagi,
I really need your help is there anyway you could help me set up the code for my sheet? My form is a little more complex but nothing crazy. Please get back to me.
Hi Tim,
Feel free to share a link to your code or paste it in a reply below. While I can’t promise that I will get a chance to help you out, perhaps someone from the community will.
Cheers,
Yagi
Yagi,
I am trying to make this form into a similar format. https://docs.google.com/forms/d/1fPuv0Mt0vC8DN2HXzs6lo8XQ0y2VjcoveEeMyz5gpWA/edit?usp=sharing
-Tim
My email is cavalryclubpool@gmail.com if anyone can help me it is urgent
Have you tried created multiple Triggers and multiple tabs of code for each individual section of booking you want?
i.e
/*#### Seat Booking Form ####
*
* Used on a multiple choice item in Google Forms, this can update the number of seats
* after each booking.
*
* Requires: A Google Form with a connecting Google Sheet.
*
*/
//#### GLOBALS ####
var SPOTS = 1; //Your seating capacity
var FORM_ID = “1fPuv0Mt0vC8DN2HXzs6lo8XQ0y2VjcoveEeMyz5gpWA”;//Add your form ID
var SESSION_ITEM_ID = 10080129E8; //Use findItemId function in Test.gs
var SS_ID = “1LxXBIUhnBFn8BrLCwP7se7uxBjbie9Z9vHPVwU5AJ40”; //Add your Spreadsheet ID
var SHEET_NAME = “Count”; //Add your Sheet tab name
var SESSION_DATA_RANGE = “2A:4C”; //Add the range of booking items your selecte sheet tab
/* ###################################################################
* Seat booking function
*
* Requires: Set trigger Edit>Current project’s triggers > select onSubmit
*
* After the form is submitted, it check the seating information from the
* sessions and then updates the form with the remaining seats.
* If seats are full for 1 session, that session is removed.
* If all spots are full for all sessions, the form is closed.
*/
function onFormSubmit1() {
//Wait a little for the form to submit the results to the Google Sheets
sleep(2000);
var sessionData = SpreadsheetApp
.openById(SS_ID)
.getSheetByName(SHEET_NAME)
.getRange(SESSION_DATA_RANGE)
.getValues();
var form = FormApp.openById(FORM_ID)
var sessionID = form.getItemById(SESSION_ITEM_ID);
//Filter item data by seats remaining < SEATS
var remainingSessionData = sessionData.filter(function(item){
return item[2] > 0;
});
if(remainingSessionData.length == 0){
// Close the form.
form.setAcceptingResponses(false);
form.setCustomClosedFormMessage(
“Registration is now closed.”
);
}else{
var itemList = remainingSessionData.map(function(item){
var itemCombined = item[0] + ” (” + item[2] + ” seats remaining)”
return itemCombined;
});
sessionID.asMultipleChoiceItem().setChoiceValues(itemList)
}
};
/* ###################################################################
* Sleep function
*
* Paused the processing of the form data for an assigned period of time.
*
* @param {number} a number in milliseconds
*/
function sleep(milliseconds) {
var start = new Date().getTime();
for (var i = 0; i < 1e7; i++) {
if ((new Date().getTime() – start) > milliseconds){
break;
}
}
}
notice how function onFormSubmit1() {
now as a 1 beside it, (change each onFormsubmit in the code to have a different number at the end, e.g onFormsubmit2, onFormsubmit3, onFormsubmit4 etc.)
this will show up on triggers as a separate trigger options now, so all in theory you have to do is change this in the code:
var SESSION_ITEM_ID = 10080129E8; //Use findItemId function in Test.gs
as all other details will still be linked to the sheet and form you have linked up.
I tried to give it a go, but without the sheet format (and having no time for me to do it myself) it can’t be tested for sure.
Thanks,
Karl
Hi Yagi,
Thanks for the great tutorial. I have two questions:
1) I have created a form based on your template. It works fine except with one issue. The issue is, if two people register at the same time for the same number of remaining seats, one person’s data is not captured (observed this issue while registering from two mobile phones). Any way to overcome this issue?
2) Is it possible to display the remaining seat status in a separate section? Like in title and description row? Not to be displayed in the multiple choice section.
Thanks for the help.
Susan.
Hi Susan,
1)
Yes, this tutorial does have its limitations. Overbooking is actually a common problem with this sort of software. You might have noticed it will flight, accommodation or ticketing sites where it might say that your purchase is being processed and we will confirm your ticket only to find someone grabbed it first. There are some sophisticated workarounds, but they are outside the scope of this tutorial.
Having said that, the booking set up in this tutorial should suffice for low traffic low stakes social event bookings. I’ve used it a couple of times to arrange social or extracurricular events for universities.
You could try working with LockService. This will suspend the script or services like Google Forms while another user is using it. However, this will have the negative effect of blocking users from your form white they wait for someone to enter in their booking.
My recommendation would be to create a web app instead and build a custom form. This way you can make regular calls to your Google Sheet and update your form (somewhat)live.
If you are running short on time, I recommend you hire a GAS developer through a job site like Fiverr. They will be able to get a custom web app form built for you in a few days or if you give them a week they will be able to make something really professional for you. You can find Fiverr devs here Fiverr using my sponsored link or this unsporsored link .
2) Yes it is possible to update all parts of a Google Form. Check out the Form Service docs.
Happy coding.
~Yagi
Hi Yagi,
First of all, thanks for the great manual! 😀
I’m having the same problem with some rows having an empty seminar title field both on the form’s answers tab and on the spreadsheet.
Do you think not updating the seminar’s title with the remaining seats (and showing the counters somewhere else) would help with not losing these rows’ data?
Why/how does a web app, instead of a form, help with not losing data on it’s way to our spreadsheet?
Thanks in advance! 😉
Jaume.
Hi Jaume,
Yes, it might be worth putting the numbers in the description alternatively you may want to extend the wait time so that Google Forms has time to process the data before Google Sheets started updating.
Roughly, how many people are using this form at one time?
~Yagi
Hello Yagi! Thanks so much for this tutorial! It’s great!
I’m having some difficulty with the code. Every time I hit ‘run’ it closes my form responses. It doesn’t matter what I set the seating capacity as (100, 30, 5, etc), it just closes the form every time. I don’t have multiple sessions, the whole form is registration for a single event. But I don’t know which part of the code is causing this error. I assume it’s:
var remainingSessionData = sessionData.filter(function(item){
return item[2] > 0;
});
but I have no idea what item[2] is or how the ‘remainingSessionData’ variable can be changed to resolve this when there is no range, just one ‘seats remaining’ number. Right now I just have the item ID as the code for the ‘your name’ question because I don’t have a ‘choose your seminar’ question. That’s the only difference and I assume the source of the problem. I’m not sure what return item[2] is referencing so I don’t understand how to modify it. If there are 29 seats remaining why would it close the responses?
//#### GLOBALS ####
var SEATS = 30; //Your seating capacity
var FORM_ID = “12ij4dMPxifAGb8WSrabRIqgSLMOjMBvZOK9QxIYtKqY”;//Add your form ID
var SESSION_ITEM_ID = 826355120; //Use findItemId function in Test.gs
var SS_ID = “1Mbj6eJrNtk_odgoRv7cwKija4eOpSgjoO0Awa2KRjWM”; //Add your Spreadsheet ID
var SHEET_NAME = “Count”; //Add your Sheet tab name
var SESSION_DATA_RANGE = “A2:C2”; //Add the range of booking items your selecte sheet tab
/* ###################################################################
* Seat booking function
*
* Requires: Set trigger Edit>Current project’s triggers > select onSubmit
*
* After the form is submitted, it check the seating information from the
* sessions and then updates the form with the remaining seats.
* If seats are full for 1 session, that session is removed.
* If all seats are full for all sessions, the form is closed.
*/
function onFormSubmit() {
//Wait a little for the form to submit the results to the Google Sheets
sleep(2000);
var sessionData = SpreadsheetApp
.openById(SS_ID)
.getSheetByName(SHEET_NAME)
.getRange(SESSION_DATA_RANGE)
.getValues();
var form = FormApp.openById(FORM_ID)
var sessionID = form.getItemById(SESSION_ITEM_ID);
//Filter item data by seats remaining < SEATS
var remainingSessionData = sessionData.filter(function(item){
return item[2] > 0;
});
if(remainingSessionData.length == 0){
// Close the form.
form.setAcceptingResponses(false);
form.setCustomClosedFormMessage(
“Event is at capacity and registration is now closed.”
);
}else{
var itemList = remainingSessionData.map(function(item){
var itemCombined = item[0] + ” (” + item[2] + ” seats remaining)”
return itemCombined;
});
sessionID.asMultipleChoiceItem().setChoiceValues(itemList)
}
};
/* ###################################################################
* Sleep function
*
* Paused the processing of the form data for an assigned period of time.
*
* @param {number} a number in milliseconds
*/
function sleep(milliseconds) {
var start = new Date().getTime();
for (var i = 0; i < 1e7; i++) {
if ((new Date().getTime() – start) > milliseconds){
break;
}
}
}
Hi Shannon,
item[2] should be the ‘seats’ remaining. Perhaps the reference to the SESSION_DATA_RANGE is not within the bounds of your data. You can always check to see what a value is by using console.log() e.g. console.log(item[2]).
~ Yagi
I m experiencing difficulties with item id, I got this:
[20-06-27 02:05:55:617 EDT] [[your Name, 1.308716753E9], [sign up for seminar, 1.871199938E9]]
any clues.
Thanks,
Jorge
Hi Jorge,
Try looking at it in the Stack Driver ( View > Stackdriver Logging ). If that doesn’t work, change the result to a string. Change this:
return [item.getTitle(),item.getId()];To this:
return [item.getTitle(),item.getId().toString()];Hope that helps,
Cheers,
Yagi
Hi Yagi! I am trying to make this work as a completely NOOB coder… I keep getting this error “SyntaxError: Invalid or unexpected token (line 10, file “Test.gs”)” when I try to run the test.gs… an ideas?
Thanks
Alison
Hi Alison, Can you paste your Test.gs code?
Thanks for your response. I ended up figuring it out by some strange miracle.
I have to create this type of seat tracking for about 5 different forms. So I am trying to recreate what I did yesterday and once again am running into an issue with finding the item ID. My form ID is in the code.gs but when I run the test.gs code… it seems like it’s running but there’s nothing coming up in my executions.
Hahaaa! Everytime I post, I end up figuring it out… all’s well over here!
Thanks for the awesome tutorial!
Great stuff! Happy coding.
Yagi,
Excellent tutorial and really appreciate your work.
Here’s an example of how I plan to use your tutorial.
Each year we hold an end of season event for our seniors tennis league with around 400-450 players attending. We start the event with 17 courts – each with a tennis pro giving advice and demonstrating different aspects of play and the players get to participate. Players pay $10 for the hot-dogs and burgers at lunch and there is no charge for the group lesson. That is why we attract so many players! Presentation of trophies takes 30 minutes before lunch.
Instead of signing up on the day, I plan to have online sign-up for each court using your routines and method to prevent the scramble to sign-up on the noticeboard on the morning of the event.
Thanks again for demonstrating the depth and flexibility of the Google products.
regards
Allan Thompson
Hi Allan,
This is great. You might have to send a bit of bad news to one or two if they are attempting to sign up and you have only one or two spots left, but I am sure it will save you a bunch of time, particularly on the day.
Thanks for sharing your use-case. Best of luck on your tennis sign up.
Cheers,
~Yagi
The function never finds my log (findItemID) please, help!!!
Hi Julieta,
Did you create the fidItemID in the Test.gs file?
If you did and are still struggling, let me know what the error says when you run the code?
~Yagi
Hello, the numbers in seats remaining does not updating. any help? thanks
Hi Jennie Pearl, it’s difficult to determine what the issue is without seeing the code. Are you getting any accompanying errors? Can you share an example of your work?
Hi Yagi, Requesting help in the situation where form need be created for daily Seminars ( Monday to Friday) and time is also same. Please help in that part how to proceed?
Hi irac28Ira,
I would replace session times with day times in the form and update the corresponding sheet accordingly. Give it a try and if you get stuck feel free to share the issue you are facing.
Happy coding!
~Yagi
Hi
This is a great tutorial – thanks for creating. I have an issue when I try to find the item ID. I have followed all the instructions above and entered my form ID in code.gs. When I run the FindItemId in test.gs I get an error that says
Exception: The given item ID is invalid. (line 10, file “Test”)
I can’t work out what I have done wrong. I have inserted the form ID in code.gs but it just doesn’t work. The only thing different is that the form ID is in red and not green like yours is
Any idea?
Thanks
Colin
This is what the code looks like in code.gs
//#### GLOBALS ####
var SEATS = 100; //Your seating capacity
var FORM_ID = “1FAIpQLSePok73BeafG8iqOQNjzcV_fVFoKEJx8HU6dXJ07x4tZhFMyQ”;//Add your form ID
And this is the code in test.gs
/* ###################################################################
* Helper function used to find the Item ID that you wish to apply
* the seat booking code to.
* It logs the item ID and it’s title.
*
* NOTE!!! Make sure you udate FORM_ID in your Code.gs page.
*/
function findItemID(){
var form = FormApp.openById(FORM_ID);
var items = form.getItems()
var item_list = items.map(function(item){
return [item.getTitle(),item.getId()];
});
Logger.log(item_list)
};
Hi Colin,
Everything looks pretty good in your code. A couple of things I can think of to troubleshoot this issue:
var FORM_ID =being set somewhere. Try doing a search in your project (ctrl + f) and see if there is a duplicate.Let me know how you go.
~Yagi
Hi Yagi
Thanks for the quick response. I have tried both of these and it still isn’t working. I have created a dummy form and get the same error.
Thanks
Colin
Hi Colin,
Hmm okay. I ran a sample of the code on a new Form and it seems to work. The only thing that stood out to me was that your file ID was a little long ( My guess this is modified to obfuscate the actual ID, but I may be wrong. The id you have actually look kinda like the length of the Google Apps Script ID instead. Anyway, just in case this is the issue, try and grab your Form ID from the Google Form it should look like this:
If you are still facing problems send me a message on my contacts form with a gmail email and I will share with you the test script and form.
~Yagi
Hi Yagi,
At first thanks a lot for whole tutorial.
I want to ask you for help. I need to set booking form with about 10 option, every with different capacity (college classes). Problem is that I will post it to all classmates to respond (choose their class) at the same time.
I tried a situation when more (3) people open Form at same time – all choose same option with only one place left – all successfully submitted (capacity in spreadsheet changed from +1 to -2.
Is there any option how to prevent that? Like set “Submit form -> check if chosen option is still avalible -> if ‘true’ submit ; if ‘false’ show message “choose a different one” and refresh, so only avalible options are showed? About 100 people will fill that form at the same time
Hi! I’m having the same question and situation like you. Have you got the solution? Mind share it with me?
Hi Sarah,
Yeah, this approach is really not suited for larger groups accessing the sheet all at once. You could look at LockService, but in a big lecture hall this is going to cause a lot of congestion. Your best bet would be to build a custom web app or see what is available among the Add-ons in the Google Marketplace.
~Yagi
When I was trying to create the trigger it was only coming up with time-driven source or From Calendar it wasn’t allowing me to change it to From Form
What do I need to do?
Hi Megan,
My first guess is that you might have created your project from Google Sheets instead of Google Forms. If this isn’t the case, let me know and we can look further.
~Yagi
I cannot get the same triggers as yous what should i do?
Hi Yogi,
Thanks for your tutorial !
However, I’m facing some problem here
Error
Exception: Invalid conversion for item type: LIST.
onFormSubmit @ Code.gs:64
thank you for your reply
Hi Yagi!
What an incridible tutorial you got here..
It works like magic..
Anyway i have a question, please help me,
Is there any possibilties that i could add “navigationItem” on “sessionID.asMultipleChoiceItem().setChoiceValues(itemList1) ?
I want that the form goes to certain Section based on the answer from Multiplechoices that contains “Seat remaining”
If I can, how can i know the id or name of the created Pagebreak-Item? I tried to read the Google App Script Reference but i am confused. Thank you in advance, Yagi!
Hi Yagi,
If I have difference date & multiple time for the slot booking. Are we still able to use the same way as tutorial guidance?
Exp:
1. Date 01 Oct 21 session :
– Slot 1 : 7.30 am – 8.30 am
– Slot 2 : 8.30 am – 9.30 am
– Slot 3 : 8.30 am – 9.30 am
Date 02 Oct 21 session :
– Slot 1 : 7.30 am – 8.30 am
– Slot 2 : 8.30 am – 9.30 am
– Slot 3 : 8.30 am – 9.30 am
Date 03 Oct 21 session :
– Slot 1 : 7.30 am – 8.30 am
– Slot 2 : 8.30 am – 9.30 am
– Slot 3 : 8.30 am – 9.30 am
And so on…..
Please advise…
Hi MeyXin,
Yes, you could.
The simplest way of handling this would be to add all the sessions by Date and Slot for each row as an option.
Another simple approach would be to offer a Google Form (and Google Sheet connection) for each day.
Adding a dynamic query option for specific days would require a WebApp approach and a significant rework of the code. I haven’t written anything up on this yet (it would be a big project to write), but I have a number of Web App tutorials that would point you in the right direction.
~Yagi
Hola,
Puedo hacer varias sesiones en un mismo formulario?
Ejemplo:
Reserva 1:
* opcion 1
* opcion 2
* opcion 3
Reserva 2:
* opcion 1
* opcion 2
* opcion 3
Por favor tu ayuda
Hi Alexis,
This tutorial doesn’t cover providing multiple sessions for the same talk.
~Yagi
Hi Guys!
i just find this post and it is very useful for me.
however, when I need to add another class program with another date. i have no idea on how to make it on the script.
is anyone can help?