Send escalating reminders for missing responses

The Northwind weekly check-in form is meant to take five minutes. In practice, a third of the team forgets every week, and the project leads end up nudging people one by one on Slack. The chases get awkward — same wording, same people, every Monday — and somebody always gets missed.

This script does the nudging on schedule, with increasing politeness. It looks at when each teammate last submitted, picks the right stage (gentle at day three, firmer at day five, “now overdue” at day seven), and sends one email. It remembers which stage it sent so nobody gets the same reminder twice in the same cycle.

What you’ll need

• A Roster sheet with columns email, lastSubmitted, and lastNudged. lastSubmitted is a date — wire it up from your form-submit handler, or paste it manually for now. lastNudged starts empty and the script manages it.
• The form’s public link as FORM_URL. The script just pastes this into the email body, so a forms.gle short link is fine.
• The roster spreadsheet ID, pasted into the openById call at the top of chaseMissing.

The script

// Public URL of the weekly check-in form. forms.gle short links work too.
const FORM_URL = 'https://forms.gle/WEEKLY_FORM';

// Roster sheet ID — the long string in the spreadsheet's URL.
const ROSTER_SHEET_ID = '1abcRosterId';

// One day in milliseconds, used to convert a date difference to whole days.
const DAY_MS = 86400000;

// Reminder stages, in order. afterDays is the threshold; body is the
// email copy. The script picks the latest matching stage that hasn't
// already been sent this cycle.
const STAGES = [
  { afterDays: 3, body: 'Quick reminder — your weekly check-in is open.' },
  { afterDays: 5, body: 'Second reminder — we still don\'t have your check-in.' },
  { afterDays: 7, body: 'Final reminder — your check-in is now overdue.' },
];

/**
 * Time-driven entry point. Walks the roster and sends at most one
 * reminder per teammate per run.
 */
function chaseMissing() {
  const sheet = SpreadsheetApp.openById(ROSTER_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;

  // 1. Build a header->column index so the script keeps working when
  //    columns are reordered in the sheet.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));
  if (col.email === undefined || col.lastSubmitted === undefined || col.lastNudged === undefined) {
    Logger.log('Roster is missing one of: email, lastSubmitted, lastNudged.');
    return;
  }

  const today = new Date();

  // 2. For each teammate, work out how many days since their last
  //    submission and which reminder stage they're due.
  rows.forEach((r, i) => {
    const last = r[col.lastSubmitted] instanceof Date ? r[col.lastSubmitted] : new Date(0);
    const days = Math.floor((today - last) / DAY_MS);

    // Pick the latest stage they qualify for that we haven't already
    // sent this cycle. Iterating in reverse means escalation, not
    // re-sending the gentle one.
    const stage = [...STAGES].reverse().find(
      (s) => days >= s.afterDays && r[col.lastNudged] !== s.afterDays
    );
    if (!stage) return;

    // 3. Send the email and record which stage we just sent. Recording
    //    the threshold (not the body) keeps the marker stable if you
    //    rewrite the copy.
    GmailApp.sendEmail(r[col.email], 'Check-in needed', stage.body + '\n\n' + FORM_URL);
    values[i + 1][col.lastNudged] = stage.afterDays;
    Logger.log('Nudged ' + r[col.email] + ' at stage ' + stage.afterDays + ' (days: ' + days + ')');
  });

  // 4. One write at the end keeps the script under the read/write quota.
  sheet.getDataRange().setValues(values);
}

/**
 * Hook for your form-submit handler — call this from the form's
 * onFormSubmit trigger to reset lastSubmitted and lastNudged for the
 * submitting teammate.
 *
 * @param {string} email The submitter's email address.
 */
function markSubmitted(email) {
  const sheet = SpreadsheetApp.openById(ROSTER_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;
  const col = Object.fromEntries(h.map((k, i) => [k, i]));
  const i = rows.findIndex((r) => r[col.email] === email);
  if (i < 0) return;
  values[i + 1][col.lastSubmitted] = new Date();
  values[i + 1][col.lastNudged] = '';
  sheet.getDataRange().setValues(values);
}

How it works

1. chaseMissing opens the roster and reads every row into memory in one call — cheap, and avoids hitting the cell-by-cell read quota.
2. It builds a header->index map so the column order in the sheet can change without breaking the script. A missing header logs and exits cleanly.
3. For each teammate, it calculates whole days since lastSubmitted. A blank cell becomes new Date(0), which yields a very large number of days and correctly fires the final reminder.
4. It picks the latest stage the teammate qualifies for that hasn’t already been sent this cycle. Iterating STAGES in reverse means the day-7 email wins over the day-5 email if both apply.
5. It stores stage.afterDays (a stable number) in lastNudged, not the body text. That way you can edit the copy without re-triggering already-sent reminders.
6. The whole sheet is written back in one setValues call after the loop.
7. markSubmitted is a helper for your form-submit trigger to call: it updates lastSubmitted and clears lastNudged, starting a fresh cycle.

Example run

The roster on a Friday morning:

email	lastSubmitted	lastNudged
[email protected]	Mon (4 days ago)	(empty)
[email protected]	last Thu (8 days ago)	3
[email protected]	yesterday	(empty)

After the script runs:

• Alex gets the day-3 reminder (“Quick reminder — your weekly check-in is open.”). lastNudged becomes 3.
• Sam gets the day-7 reminder (“Final reminder — your check-in is now overdue.”), even though they already received a day-3 nudge — the reverse search skipped past 3 and picked 7. lastNudged becomes 7.
• Jo gets nothing. They submitted yesterday and aren’t due for a chase.

Trigger it

Run it once a day in the morning, before stand-up:

1. In the Apps Script editor open Triggers (the clock icon).
2. Add trigger, choose chaseMissing, event source Time-driven, type Day timer, time of day 8am–9am.
3. Approve the authorisation prompt — it needs Sheets and Gmail scopes.
4. Optionally, add an installable form-submit trigger on the response sheet that calls markSubmitted(e.namedValues.Email[0]) to reset the roster when someone submits.

Watch out for

• The reverse-pick means stages always escalate. Switch to a forward iteration if you’d rather re-send the gentle reminder on day five for people who never opened the first.
• lastNudged is reset by markSubmitted. If you don’t wire that up, a teammate who submitted yesterday will never get a fresh cycle of nudges next time they go quiet.
• Daily quotas are real: 100 Gmail sends a day on free accounts, 1500 on Workspace. A roster of 30 with three stages is comfortably within either, but for a 500-person company batch into a digest instead.
• The script writes the entire sheet back at the end. If two triggers ever run at once (manual + scheduled) they will race. Wrap the body in LockService.getDocumentLock() if that concerns you.
• Reminders chase the people on the roster, not respondents. Take leavers off the sheet, or they will keep getting “final reminder” emails forever.