Pull event RSVPs from emails into a Sheet

Every quarter Northwind hosts a client lunch, and every quarter the head count is a guess. The invite goes out to a shared [email protected] address, replies trickle back as plain emails — “yes, see you there”, “sorry, can’t make it this time” — and someone ends up reading the inbox and keeping a tally on a notepad. Numbers drift, names get missed, and the caterer gets a rough estimate.

This script turns that mailbox into a clean attendance list. It scans replies to the RSVP address, works out whether each one is a yes, a no, or a maybe from the wording, and appends a row to an RSVPs sheet. Each respondent is counted once, and a quick formula on the sheet gives you the live head count.

What you’ll need

• A Google Sheet with an RSVPs tab. Row 1 must be a header with the columns email, name, status, respondedAt — the script appends rows beneath it.
• A Gmail account that receives the RSVP replies — that is, the account behind [email protected].
• Nothing else. The script creates the rsvp/counted label itself on first run.

The script

// The spreadsheet that holds the RSVP list.
const RSVPS_SHEET_ID = '1abcRsvpsSheetId';

// The address invitees reply to.
const RSVP_ADDRESS = '[email protected]';

// The label applied to threads already counted, so they are skipped.
const COUNTED_LABEL = 'rsvp/counted';

/**
 * Scans recent replies to the RSVP address, classifies each as
 * yes / no / maybe, and appends one row per new respondent.
 */
function tallyRsvps() {
  // 1. Find replies sent to the RSVP address in the last 30 days that
  //    have not already been counted.
  const threads = GmailApp.search(
    'to:' + RSVP_ADDRESS + ' newer_than:30d -label:' + COUNTED_LABEL
  );
  if (!threads.length) {
    Logger.log('No new RSVP emails to process.');
    return;
  }

  // 2. Get (or create) the label used to mark counted threads.
  const counted = GmailApp.getUserLabelByName(COUNTED_LABEL)
    || GmailApp.createLabel(COUNTED_LABEL);

  // 3. Read the addresses already on the sheet so each person is
  //    counted only once, even if they reply twice.
  const sheet = SpreadsheetApp.openById(RSVPS_SHEET_ID).getSheets()[0];
  const known = new Set(sheet.getRange('A2:A').getValues().flat());

  // 4. Build one row per new respondent whose reply we can classify.
  const rows = [];
  for (const thread of threads) {
    const msg = thread.getMessages()[0];
    const from = msg.getFrom();

    // Extract the bare address from a "Name <addr>" From header.
    const email = (from.match(/<(.+?)>/) || [, from])[1].toLowerCase();
    if (known.has(email)) continue;

    // Work out yes / no / maybe from the wording; skip if unclear.
    const status = parseStatus(msg.getPlainBody());
    if (!status) continue;

    // Pull the display name out of the From header.
    const name = from.split('<')[0].replace(/"/g, '').trim();
    rows.push([email, name, status, new Date()]);
    thread.addLabel(counted);
  }

  // 5. Append all the new rows in a single write.
  if (rows.length) {
    sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 4).setValues(rows);
    Logger.log('Recorded ' + rows.length + ' RSVP(s).');
  } else {
    Logger.log('No new classifiable RSVPs in this batch.');
  }
}

/**
 * Classifies an email body as "yes", "no" or "maybe" by looking for
 * common reply phrasings. Returns null if nothing matches.
 */
function parseStatus(body) {
  if (/\b(yes|coming|count me in|i'?ll be there)\b/i.test(body)) return 'yes';
  if (/\b(no|can'?t make it|sorry)\b/i.test(body)) return 'no';
  if (/\b(maybe|tentative|might)\b/i.test(body)) return 'maybe';
  return null;
}

How it works

1. tallyRsvps searches Gmail for messages sent to the RSVP address in the last 30 days, excluding anything already labelled rsvp/counted. If there is nothing new, it stops.
2. It fetches the rsvp/counted label, creating it on the first run.
3. It reads column A of the sheet into a Set of addresses already recorded. This is the deduplication guard — a second reply from the same person is skipped.
4. For each thread it parses the bare email address out of the From header (which usually looks like Jordan Lee <[email protected]>). If that address is already known, it moves on.
5. parseStatus reads the body and matches it against three sets of phrases. A clear yes, no, or maybe produces a row; anything ambiguous returns null and the thread is left unlabelled for a human to read.
6. Classified replies become four-field rows — address, name, status, timestamp — and the thread is labelled so the next run skips it.
7. All new rows are appended to the sheet in a single setValues call.

Example run

Two replies land in the RSVP mailbox:

From: Jordan Lee <[email protected]>
Subject: Re: Northwind client lunch

Yes! Count me in, looking forward to it.

From: Sam Cole <[email protected]>
Subject: Re: Northwind client lunch

Sorry, can't make it this quarter.

After the run, the RSVPs sheet has gained two rows:

email	name	status	respondedAt
[email protected]	Jordan Lee	yes	2026-05-25 10:14
[email protected]	Sam Cole	no	2026-05-25 10:14

A formula like =COUNTIF(C2:C, "yes") then gives the live head count for the caterer.

Trigger it

Run this on a time-driven trigger during the RSVP window:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose function tallyRsvps, event source Time-driven, type Hour timer, every hour.

Hourly is plenty — RSVPs are not time-critical, and an hourly poll keeps the sheet current without burning quota. Turn the trigger off once the event has passed.

Watch out for

• Wording is messy. “No problem, I’ll be there” contains the word “no” before “I’ll be there” — the regex checks yes patterns first, so this is read correctly, but unusual phrasings will be missed. Leave the rsvp/counted label off unclassified threads so a human can catch them.
• Only the first message is read. If someone replies “maybe” then later “yes” in the same thread, the thread is already counted as maybe. A late change of mind needs a fresh email or a manual edit.
• Quoted text counts. A reply that quotes the original invite (“…let us know if you can make it”) may match a phrase you did not intend. If false matches appear, strip quoted lines (those beginning with >) before calling parseStatus.
• One row per address forever. Once someone is on the sheet, later replies are ignored. Clear the sheet and the rsvp/counted label before the next quarter’s event so the count starts fresh.
• The From-header parsing assumes a standard format. A header with no display name still works, but an unusual one could leave a slightly odd name value — harmless, but worth knowing.