Track meeting attendance and no-shows

Northwind runs a lot of internal meetings — production briefs, client calls, the Monday all-hands — and the calendar is full of RSVPs that turn out to mean almost nothing. Half the “yes” replies never show up, the “maybe” column is quietly the largest, and nobody has the data to push back on a chronic no-shower without sounding accusatory.

This script logs every guest on yesterday’s events and their RSVP status to a spreadsheet. Run it daily and within a fortnight you have a real record — who accepted, who declined, who left it on “maybe” until the last minute. It does not measure attendance directly, but combined with a single “showed up” checkbox column it gives you the evidence to have the conversation.

What you’ll need

• A Google Sheet to act as the RSVP log. Note its ID from the URL and paste it into the RSVP_LOG_ID constant below. Headers in row 1 of the first tab: Start, Event, Guest, RSVP status.
• Access to the calendar you want to track. The script reads the user’s default calendar via CalendarApp.getDefaultCalendar().
• Nothing else — no API keys, no add-ons.

The script

// The spreadsheet that holds the RSVP log. The first tab gets appended to.
const RSVP_LOG_ID = '1abcRsvpLogId';

// Milliseconds in a day — used to walk back from "now" to "yesterday".
const ONE_DAY_MS = 24 * 60 * 60 * 1000;

/**
 * Logs every guest on yesterday's calendar events, with their RSVP status,
 * as one row per guest in the RSVP log spreadsheet.
 */
function logRsvps() {
  const today = new Date();
  const yesterday = new Date(today.getTime() - ONE_DAY_MS);

  // 1. Pull every event that started in the last 24 hours.
  const events = CalendarApp.getDefaultCalendar().getEvents(yesterday, today);
  const rows = [];

  // 2. Flatten each event into one row per guest — start time, title,
  //    guest email and their RSVP status (YES / NO / MAYBE / INVITED).
  for (const event of events) {
    for (const guest of event.getGuestList()) {
      rows.push([
        event.getStartTime(),
        event.getTitle(),
        guest.getEmail(),
        guest.getGuestStatus(),
      ]);
    }
  }

  // 3. Nothing to append? Bail out before opening the spreadsheet.
  if (!rows.length) {
    Logger.log('No events with guests yesterday — nothing logged.');
    return;
  }

  // 4. Append all rows in one setValues call — much faster than row-by-row.
  const sheet = SpreadsheetApp.openById(RSVP_LOG_ID).getSheets()[0];
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 4).setValues(rows);
  Logger.log('Logged ' + rows.length + ' RSVP rows.');
}

How it works

1. logRsvps builds a 24-hour window ending at the current moment, which is “yesterday” for a script that runs early each morning.
2. It calls getEvents(yesterday, today) on the default calendar to fetch every event that overlapped that window.
3. For each event, it walks the guest list and pushes one row per guest: start time, event title, guest email, and the RSVP status string.
4. If the window had no events with guests, it logs a message and returns — no need to open the spreadsheet for an empty append.
5. Otherwise it opens the RSVP log, finds the next empty row with getLastRow() + 1, and writes every new row in a single setValues call.

Example run

After a Monday with two meetings, the log looks like this:

Start	Event	Guest	RSVP status
2025-09-01 09:00	Production brief	[email protected]	YES
2025-09-01 09:00	Production brief	[email protected]	MAYBE
2025-09-01 09:00	Production brief	[email protected]	INVITED
2025-09-01 14:00	Client call: Fabrikam	[email protected]	YES
2025-09-01 14:00	Client call: Fabrikam	[email protected]	NO

A fortnight of rows lets you pivot by guest and count YES versus MAYBE versus INVITED. The pattern shows up quickly — the people who reply, the people who never click the button, and the chronic “maybes” who clog up your headcount.

Trigger it

Run this once a day, early, so it covers the previous 24 hours before anyone starts editing today’s calendar:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Add a trigger for logRsvps, time-driven, day timer, between 5am and 6am.
3. Save and approve the calendar and spreadsheet authorisation prompts.

Watch out for

• RSVP status is what guests clicked, not whether they actually attended. Add a Showed up column to the log and tick it manually (or with a second script that reads attendance from another source) if you want the full picture.
• A daily window can miss long-running events that started two days ago and ended yesterday. getEvents returns events that overlap the window, so this is rare in practice — but if your team books week-long blocks, widen the window to seven days and de-duplicate on event ID.
• getGuestStatus() returns INVITED for guests who never responded. That is useful signal in its own right; treat it as “no reply” rather than a no.
• The script appends — it never deduplicates. If you run it twice on the same day you will get duplicate rows. A simple guard is to record the event ID too and skip rows already present in the log.