Sign-up form with limited places: full sessions disappear

This guide builds that form with Google Forms™ and Google Sheets™. A spreadsheet counts the sign-ups for each session, and the form only offers the sessions that still have places. When a session is full, it leaves the form. When a place frees up, it comes back.

Time needed: about 15 minutes. You need a Google account; a free Gmail account is enough.

1. Create the form

Create a form with your usual questions (name, email) and a question such as Which session? Set its type to Dropdown or Multiple choice. Avoid checkboxes here: one checkbox answer can hold several sessions, and the count below would miss them. Leave the choices as they are; they will be replaced.

2. Copy the template spreadsheet

Download the template (.xlsx)

In Google Drive™: New, File upload, choose the file. Open it with Google Sheets, then File, Save as Google Sheets. This creates a copy next to the .xlsx file: from now on, use that copy (green Google Sheets icon). In the Sessions tab, type your sessions in column A and the number of places in column B.

3. Send the responses to this spreadsheet

In the form, open the Responses tab, click Link to Sheets, choose Select existing spreadsheet, and pick your copy of the template. Google adds a tab named Form Responses 1 that fills up with each answer. Columns C, D and E now update on their own.

On the right of the Sessions tab, under Responses tab, a status line shows ✓, the tab being counted and the number of responses so far. If it shows ✗, your responses tab has another name, for example Form Responses 2 if the form was linked before, or a name in another language: type its exact name in the yellow cell.

Choose Select existing spreadsheet: the option offered first, Create a new spreadsheet, would send the responses to another file, and nothing would be counted here.

This link is not the one you will create in Quietools Lists at step 4. Here, Google Forms writes each response into the spreadsheet; there, Quietools Lists reads the spreadsheet to update the form. Both are needed.

Test it: submit one response from the form’s link (the preview doesn’t save responses), and the Booked count of that session goes up by one.

4. Make the form follow column E

The spreadsheet now knows which sessions are open. The last step is to copy that list into the form after each response. There are two ways to do it.

Option A, free: a short script

1. In the form, click the three-dot menu, then Script editor.
2. Replace the code with the script below. Paste the spreadsheet ID (the long part of its address, between /d/ and /edit) and, if needed, the exact title of your question.
3. Select the setup function, click Run, and accept the permissions.

const SPREADSHEET_ID = 'PASTE_THE_SPREADSHEET_ID';
const RANGE = 'Sessions!E2:E';
const QUESTION = 'Which session?';

// Run once: updates the list now and after every response.
function setup() {
  ScriptApp.newTrigger('updateChoices')
    .forForm(FormApp.getActiveForm())
    .onFormSubmit()
    .create();
  updateChoices();
}

function updateChoices() {
  Utilities.sleep(5000); // gives the spreadsheet time to record the response
  const form = FormApp.getActiveForm();
  const open = SpreadsheetApp.openById(SPREADSHEET_ID)
    .getRange(RANGE).getDisplayValues()
    .flat().filter(String);
  if (open.length === 0) {           // everything is full
    form.setAcceptingResponses(false);
    return;
  }
  const item = form.getItems().find(i => i.getTitle() === QUESTION);
  const question = String(item.getType()) === 'LIST'
    ? item.asListItem()
    : item.asMultipleChoiceItem();
  question.setChoiceValues(open);
  form.setAcceptingResponses(true);
}

What to know about this option: Google asks the script for access to all your spreadsheets, not only this one. If the script fails one day, for example because a tab was renamed, Google only sends you a failure summary by email, by default once a day, and you maintain the code yourself.

Option B, no code: Quietools Lists

Quietools Lists is the add-on we make for exactly this. It reads only the spreadsheet you pick, and it keeps the current choices if the spreadsheet can’t be read.

1. Install Quietools Lists. In your form, click the puzzle-piece icon, then Quietools Lists, then Open.
2. Click Add a link, choose your question, then Choose a spreadsheet and pick your copy of the template.
3. In Range to read, type Sessions!E2:E. Click Continue: the preview lists the open sessions. Click Replace the choices.
4. Under Update after each response, click Turn on. The sidebar then shows the time of the last automatic update.
5. Under When the range is empty, choose to show a message such as “Full: no places left”, or to close the form. A closed form receives no responses, so it reopens when you free a place and click Update now.

Updating after each response and closing the form are part of the full version. The free version updates the list when you click Update now.

Good to know

• Someone who opened the form before the last place was taken can’t take it anyway. If they pick that session, Google Forms refuses the answer with “The question has changed. Please review your response.”, and they choose another one. Only two people who send the last place within the same few seconds can both get in, before the list is updated; Places left then shows −1: contact the second one and offer another session.
• To free a place, delete the response row in the Form Responses 1 tab, or raise the number of places in column B. The session comes back at the next update: the next response, or Update now in the sidebar. If the form was closed because everything was full, click Update now: it reopens.
• Don’t rename a session once people have signed up. Their answers keep the old name and no longer count. Add a new line instead.
• To add a session, type it on a new line with its number of places. It appears in the form at the next update.