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 (click to expand the image):

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

 

36 thoughts on “Create a seat booking form with Google Forms, Google Sheets and Google Apps Script”

  1. 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?

    1. 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

    2. 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!!

  2. When Adding a Trigger I did not get the option of choose From form for Select Event Source. Any ideas?

  3. 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!

    1. Thanks for the kind words, Breanna.

      It’s great to hear how you made use of the tutorial.

      Live long and prosper, mate.

      ~Yagi.

  4. 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.

    1. 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

  5. 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?

    1. 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.

    2. 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!

      1. 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?

  6. ReferenceError: FORM_ID is not defined (line 8, file “test”)Dismiss
    this is what I get after trying to find the item ID

  7. 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.

  8. 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?

  9. 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

  10. 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?

    1. 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

  11. 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?

  12. 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.

    1. 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

    1. 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

  13. 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.

    1. 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

      1. 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.

        1. 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

  14. 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

    1. 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

  15. 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

      1. 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.

        1. Hahaaa! Everytime I post, I end up figuring it out… all’s well over here!
          Thanks for the awesome tutorial!

Leave a Reply