How to create a booking system using Google Forms and Sheets

To set up a booking system using Google Forms and Sheets It's one of the fastest and cheapest ways to manage appointments, event slots, or waiting lists without investing in expensive software. With a well-designed form, a spreadsheet to handle the calculations, and a little Google Apps Script, you can have a fairly professional system up and running in a matter of hours.

In this article we're going to look, in detail, at how to combine Google Forms, Google Sheets and Google Apps Script To create a booking system with capacity control, automatic seat updates, an embeddable attendee list on your website, and, if needed, integration with an external booking server via API, following the approach used by Google Action Center. The idea is that you end up with a solid foundation that you can adapt to your business: from a small course to a more advanced appointment system.

Planning the booking system with Google Forms and Sheets

The basis of the entire flow is a simple registration form where the user enters their information and chooses the session or resource they want to book. Each form submission is automatically saved in a Google Sheets spreadsheet, where we calculate how many places are occupied, how many remain, and who is registered for each session.

Google Forms, by itself, does not allow block places or hide sold-out optionsThis is where Google Apps Script comes in. With a script associated with the form or spreadsheet, you can dynamically update the text of the options (for example, adding "(25 places remaining)") or even remove sessions that are already full from the list.

When all sessions are full, the script can also automatically close the form so that no one else can submit requests. This entire process is coordinated with the data from the "control" sheet prepared by Google Sheets, where the number of attendees is counted and the remaining capacity is calculated in each case.

Furthermore, if you want a more advanced system or already work with your own backend, you can implement a booking server via REST API that receives requests from Google (for example, from Actions Center), creates or updates reservations in your system and returns statuses, errors and normalized data, ensuring idempotence and secure authentication.

Design the booking form in Google Forms

The first step is to create a minimalist but well-designed form. It's normal to include at least the name of the person making the reservation and the session or activity you want to book. You can add more questions (email, phone, comments, etc.), but it's best to start with the basics to focus on the logic of availability.

In Google Drive, create a new form and give it a clear title. Add a question of the type Short answer for name and another of the “Multiple Choice” type for the available sessions. Each option in the multiple choice will represent a session or resource to be booked.

When you add short text, true/false, or multiple-choice questions, Google Forms asks for a title and options; in the world of Google Apps Script, all of this is considered a “item” of the formIt is important to keep this concept in mind because later, when programming, we will need to locate the specific item that lists the sessions.

Imagine you're offering four seminars. You could initially define the options with text that includes the schedule and total capacity, for example: “Seminar 1: 10:00-12:00 (100 places remaining)”. This will be the information the first user who opens the form sees, and the script will automatically reduce the number of places in that text.

If you want, you could integrate this booking block as part of a more extensive form (for example, a preliminary survey and at the end the reservation block), but to fully understand the logic it is more convenient to work with a simple form focused solely on reserving places.

Connect Google Forms with Google Sheets

For the system to work, you need each form submission to end in a linked spreadsheetFrom the "Responses" tab of Google Forms you will see an icon with the typical green Sheets logo; when you click it, the assistant will allow you to create a new sheet or link the form to an existing sheet.

The easiest way to start is to create a new sheet. On that sheet, you'll see a tab called something like "Form responses 1" where all the rows with the data people submit will appear: timestamp, registrant name, and chosen seminar, along with any other fields you've added.

Before proceeding with formulas or scripts, it is very useful to send a few test shipments directly from the form (using the preview button). This way you can ensure the column structure is as expected and adjust formulas without fear of making a mistake.

Don't forget to check that the form is in mode “Accepting responses”Otherwise, neither you nor your users will be able to make test shipments, and you'll think something is broken when in reality you just needed to activate that option.

Create a readable list of attendees per session

Working solely with the responses tab isn't very convenient if you want a clear overview of who's attending each session. Therefore, a good trick is to create an auxiliary tab that displays, in an organized way, a list of attendees per seminar which you can then even embed in your website.

Create a new sheet within the same Sheets document and name it, for example, “Registerees” or “Attendees”. Use the first few rows for a nice title and some introductory text, but the important part starts a few rows down with the headers.

In the times header row, you can define columns like “Hour”, “10:00-12:00”, “12:00-14:00”, etc. Below, in another row, put the exact names of the seminars exactly as they appear on the form, but without the text "remaining places". This text match will be key to locating who each reservation belongs to.

In the first column (A), starting from the bottom row, number the rows 1, 2, 3… up to your maximum capacity. These rows will represent the possible places for each seminar, so each cell horizontally will contain the name of a person who has signed up for that session.

The key to automatically filling in these names is to combine several Sheets functions: FILTER, REGEXMATCH, UNIQUE and IFERRORWe start with FILTER, which allows you to show only those rows that meet a condition based on another column, for example "show only the names whose seminar matches the header of this column".

However, in your form, the text for the seminar option will include the number of remaining places, which is constantly updated, so it will never exactly match the static text in your header. To solve this, we use REGEXMATCH, which allows you to check if a cell It contains a specific text. anywhere in the content, even if there are other characters around it.

The typical pattern would be something like: REGEXMATCH(seminar_column, header). If REGEXMATCH returns TRUE, it means that the seminar selected in that response includes the header text, and FILTER keeps that row. Therefore, even if the response shows “Seminar X (85 places remaining)”, searching for “Seminar X” will yield a valid match.

There is another common problem: some people may try to sign up twice to the same seminar (either by mistake or with malicious intent). To prevent duplicates from appearing in the attendee list, the FILTER function is wrapped with UNIQUE, so that each name that meets the condition is only displayed once.

Finally, if no one has yet signed up for a specific seminar, FILTER usually returns a #N/A error. To avoid this unsightly message in a spreadsheet you're going to embed on your website, you use IFERROR around the entire formula, so the error is hidden and the cell remains empty when there are no registered participants.

Calculate how many places are occupied and how many are available

Once you have a more or less legible list of attendees, it's time to create the capacity control sheetwhich is what the script will use to know if there is space left in each session and how much to show in the text of the form options.

Create another tab in the same Sheets document and give it a clear name, such as "Count" or "Capacity". In column A, list all the seminars with the exact same names as in the form, but this time you can also include the times if you prefer.

Elsewhere on the sheet (for example, a highlighted cell in the upper right) define a value that represents the maximum capacity of each session (for example, 100 places). This reference will be what you use to calculate how many places remain after subtracting the confirmed reservations.

In column B of the control sheet, define a formula that essentially does the same thing as the attendee formula, but wrapped in COUNTA to count the number of records. By combining COUNTA with FILTER, REGEXMATCH, UNIQUE, and IFERROR, you get the total number of unique people registered for each seminar without duplicates or #N/A errors.

In column C, the logic is simple: subtract the number of attendees (column B) at maximum capacity (the cell you defined for capacity). This returns, for each row in column A (each seminar), a number representing the remaining spaces. This matrix will be used by your script to decide which options to display and with what text.

This control sheet updates automatically every time a new response is received in the forms tab. You don't need to do anything manually: the formulas will do the counting for you in the background while the system continues to accept bookings.

Automate the form with Google Apps Script

The component that makes this assembly a true dynamic reservation system It's Google Apps Script. From the script editor, associated with the form or spreadsheet, you can schedule the updating of options and the closing of the form when the capacity reaches zero.

To open the editor, in the form go to the three vertical dots menu and select “Script Editor”. From the spreadsheet, you'll find it under “Tools > Script Editor”. In both cases, the same Apps Script project linked to the document will open.

The idea is to create a main function that fires every time a response is sent, something like this: onFormSubmit()This function will have to read the data matrix from the control tab (“Count”), filter the seminars with places, update the text of the form options and, if there are none left, disable the acceptance of responses.

At the beginning of the script it is advisable to declare a series of global variables: the maximum capacity of places, the ID of the form, the ID of the session item, the ID of the spreadsheet, the name of the tab with the capacity data and the specific range of cells that contain the name of the seminar, occupied places and remaining places.

To obtain the session item ID (For multiple-choice questions), a small helper function can be written that iterates through the form items, prints their titles and IDs to the log, and thus locates the correct value. Once located, that number is copied and used in the corresponding global variable.

There's one important practical detail: when you submit a form, Google needs a small margin to Write the answer on the sheetThat's why many developers include a custom "sleep" function that waits a few hundred or thousands of milliseconds before starting to read data from the sheet, ensuring that the new row is already available.

Within `onFormSubmit()`, the typical flow will be: open the sheet by ID, open the control tab by name, read the seminar data range, and save the result to a two-dimensional JavaScript array. Then, open the form by ID and access the sessions item using the ID we discovered earlier.

With the data array loaded, a filter is applied in JavaScript to keep only the rows where the number of remaining slots is greater than zero. This set will be the list of sessions that are still available. If the result of the filter is an empty array, it means that All sessions are full.

If there are no available slots, the script can close the form using `FormApp.setAcceptingResponses(false)` and display custom text informing the user that the booking period has ended. From the user's perspective, the form will appear as "closed" with your notification message.

If there are still sessions with available spaces, the script iterates through the filtered rows and builds an array of strings that combines the session name with the number of available spaces, for example: “Seminar X – 32 spaces remaining”. This array is then passed to the setChoiceValues ​​method of the multipleChoiceItem so that the form updates instantly.

Finally, to ensure that the onFormSubmit() function runs every time there is a submission, a trigger From the "Edit > Current Project Triggers" menu in the Apps Script editor, you can select the function, the event source (from the form), and the event type (when the form is submitted). This way, your logic will automatically trigger every time a user makes a reservation.

Embed the form and attendee list on your website

Once the system is working, ideally you should integrate it into your website or intranet So that users don't have to leave your site to book. Google Forms and Google Sheets make this much easier using embed code (iframe).

To embed the form, from the editing screen click "Submit" and select the embed icon ("<>"). The dialog box will display an iframe snippet with the form's URL, as well as fields to adjust the width and height. Copy this code and paste it into the HTML of the page where you want the form to appear.

In the case of the attendee sheet, you'll probably only want to show the tab where you have the list sorted by seminarsTo do this, go to "File > Publish to the web" in Sheets, select "Embed", choose the correct tab, and confirm the publication. Google will generate another iframe that you can paste into your website.

You may need to manually adjust the width and height of the iframe so it looks good within your page design (for example, 100% width and a generous height to avoid excessive scrolling). While not required, it looks cleaner if you embed the list of attendees. in a separate section from the page, visually distinct from the booking form. You can also synchronize bookings with Google Calendar for notifications and reminders.

In some projects, this embedding is combined with custom CSS styles so that both the form and the embedded sheet integrate better with the site's corporate image, even though the content remains hosted on Google.

Implement a booking server using a REST API

Beyond the purely internal use of Forms and Sheets, Google offers the possibility of integrating your bookings with Actions Center or other external systems through your own booking server that exposes a REST API. This approach is ideal if you already have a backend or want a more scalable architecture.

The idea is that Google acts as an interface to the user (on mobile, web, or other channels), and when the user requests a reservation, Google calls your booking server to create or update appointments in real time. There are two main variants: the standard implementation (normal appointments/reservations) and the waiting list implementation, intended for a specific pilot program.

The standard implementation works with two key resources: the "Space" (Slot or inventory resource: a table, a time slot, a service) and the "Reservation" (the specific appointment for that space). Your server exposes methods such as CreateBooking and UpdateBooking, among others, so that Google can manage the reservation lifecycle on your behalf.

When a user creates a reservation from the Google interface, the platform sends you data such as name, surname, telephone number and email addressFrom your system's point of view, this is a guest purchase or booking, since Actions Center does not have access to your platform's internal accounts, and you should not assume that this user already exists in your database.

Your responsibility is to ensure that the booking registered by your backend is consistent with all other bookings generated through your own channels (website, app, phone, etc.). If your internal bookings generate confirmation emails, locator numbers, or specific statuses, the booking created through Google should also be consistent. follow exactly the same logic to avoid inconsistencies.

Before going into production, it is essential to create a test server or sandbox connect it to the Actions Center test environment. Only after you have verified that all methods respond correctly (including error situations) should you configure the production endpoint and expose it to real users.

Google publishes service definitions in proto format that you can download to speed up API implementation, whether in Node.js, Java, or other supported languages. There are also sample server schemas that simulate the necessary REST methods, which you can use as a template.

To summarize, you can set up a [system/platform] using free Google tools surprisingly powerful reservation system: a form that shows only sessions with available spaces, a spreadsheet that counts attendees and calculates remaining capacity, a script that updates the options and closes the form when it is full, and, if needed, a REST booking server that communicates with Google Actions Center securely, idempotently and with fine error handling, bringing you quite close to what many specialized commercial platforms offer.