Build an RSVP system with live headcounts

Northwind throws a wrap party at the end of every long shoot, and the worst part of the planning is the headcount. People reply across Slack, WhatsApp, and “I’ll just turn up”, and by Thursday the caterer needs a number that does not exist anywhere. The studio ends up over-ordering, under-ordering, or guessing — and the guesses always include the wrong number of vegetarians.

This script makes the headcount the side-effect of the form. Every RSVP appends a row to a Responses tab, then rewrites a small Summary tab with the running totals for Yes, No, and Maybe. Open the summary at any time and the number is current — no exports, no pivot tables, no last-minute spreadsheet acrobatics.

What you’ll need

• A Google Form with three short-answer questions named exactly Name, Attending, and Dietary. The Attending question should be a multiple-choice with three options: Yes, No, Maybe.
• A Google Sheet linked to the form. The first tab holds the responses; the script creates the Summary tab on the first run.
• The form bound to its Apps Script project so the trigger can fire.

The script

// The sheet that holds responses and the rolling summary. Form-linked
// sheets work — the first tab is whatever Forms created.
const RSVP_SHEET_ID = '1abcRsvpId';

// The name of the summary tab. Created on first run if it does not exist.
const SUMMARY_TAB = 'Summary';

// Which answers the script counts. Order here drives the order in the
// summary table, so put the most useful one first.
const STATUSES = ['Yes', 'No', 'Maybe'];

/**
 * Runs on every form submission. Appends the response to the first tab
 * and rewrites the Summary tab so the headcounts always reflect the
 * latest state of the sheet.
 *
 * @param {GoogleAppsScript.Events.FormsOnFormSubmit} e Form submit event.
 */
function onFormSubmit(e) {
  const ss = SpreadsheetApp.openById(RSVP_SHEET_ID);
  const responses = ss.getSheets()[0];

  // 1. Read the three fields we care about. Dietary is optional, so it
  //    defaults to an empty string rather than tripping the script.
  const name = (e.namedValues.Name && e.namedValues.Name[0]) || '';
  const status = (e.namedValues.Attending && e.namedValues.Attending[0]) || '';
  const diet = (e.namedValues.Dietary && e.namedValues.Dietary[0]) || '';

  // 2. Append the row. Form-linked sheets already have the form columns;
  //    this writes to a separate tracking tab if you want one — here we
  //    write back to the first tab so the timestamp and parsed status
  //    sit next to the original answer.
  responses.appendRow([new Date(), name, status, diet]);

  // 3. Read the data range minus the header, then tally each status by
  //    scanning the column once per status. For a few hundred rows that
  //    is cheap; for tens of thousands, switch to a single-pass reduce.
  const rows = responses.getDataRange().getValues().slice(1);
  const counts = STATUSES.map((s) => [s, rows.filter((r) => r[2] === s).length]);

  // 4. Rebuild the Summary tab from scratch. Clearing first means a row
  //    deleted by hand in the responses tab is reflected the next time
  //    the trigger fires.
  const summary = ss.getSheetByName(SUMMARY_TAB) || ss.insertSheet(SUMMARY_TAB);
  summary.clear();
  summary.getRange(1, 1, 1, 2).setValues([['Status', 'Count']]);
  summary.getRange(2, 1, counts.length, 2).setValues(counts);
}

How it works

1. onFormSubmit opens the RSVP spreadsheet by ID and grabs the first tab, which holds the raw responses Forms appends.
2. It reads the three answers, defaulting any missing field to an empty string so partial submissions do not crash the trigger.
3. It appends a row of [timestamp, name, status, diet] to the responses tab. The timestamp is the script’s wall clock, not the Forms timestamp — so a row that arrived “just now” reads as right now.
4. It reads the whole data range, drops the header row, and counts how many rows match each entry in STATUSES. The order of STATUSES controls the order of the summary, so Yes lands at the top.
5. It rebuilds the Summary tab on every run — clearing and rewriting both the header and the totals. That keeps the summary honest if anyone edits the responses tab by hand.

Example run

After three submissions:

Timestamp	Name	Attending	Dietary
2026-05-27 14:02	Priya Shah	Yes	Vegetarian
2026-05-27 14:07	Tom Ellis	Yes	—
2026-05-27 14:11	Lin Park	Maybe	Gluten-free

The Summary tab shows:

Status	Count
Yes	2
No	0
Maybe	1

When Tom changes his mind and a fourth submission arrives with No, the summary repaints to Yes: 2, No: 1, Maybe: 1 automatically — no manual refresh.

Trigger it

Wire the script to the form submission:

1. From the Apps Script project bound to the form, open Triggers.
2. Add a trigger for onFormSubmit, source From form, type On form submit.
3. Approve the Sheets scope on the first run, then test by submitting an RSVP from your own address and checking the Summary tab.

Watch out for

• Reading the full data range on every submission is fine up to a few thousand rows. Past that, switch to a single-pass reduce that counts all statuses in one go, or write the totals to a cell with a COUNTIF formula and skip the Apps Script tally entirely.
• The dietary column is free text. If you want a real count of vegetarians versus vegans, switch the question to multiple-choice and add a second tally block below the main summary.
• The script overwrites the Summary tab from row 1. If anyone adds a chart or notes to that tab, they will be cleared on the next submission — put extras on a third tab named something other than Summary.
• The headcount includes every row in the responses tab, including duplicates. If a guest submits twice, they are counted twice. Dedupe by email if your form collects one — add an email column and filter the rows before the tally.