Send tiered deadline countdown reminders

A single “one week until deadline” email rarely lands well. Sent too early and people file it; sent too late and there is no time left to act. The fix at Northwind is tiered nudges — a heads-up a week out, a check-in three days out, a real “tomorrow” alert the day before — each to the right owner, with no duplicates.

This script reads the team’s Deadlines sheet and sends those staged reminders. It tracks which stage each row last received so a deadline only generates one email per tier — no morning floods, no escalating noise.

What you’ll need

• A Deadlines sheet with these headers in row 1: task, owner, due, lastReminded. Paste the spreadsheet ID into DEADLINES_SHEET_ID.
• owner should be a single email address — the person responsible.
• due must be a real date (format the column as Date) — strings are ignored by the instanceof Date guard.
• Leave lastReminded blank for new rows. The script fills it in.

The script

// The spreadsheet that holds the Deadlines list.
const DEADLINES_SHEET_ID = '1abcDeadlinesId';

// Reminder stages in days. Earliest first so the find() picks the largest
// stage the row still qualifies for.
const STAGES = [7, 3, 1];

// Milliseconds in a day, used to convert (due - today) into whole days.
const ONE_DAY_MS = 24 * 60 * 60 * 1000;

/**
 * Reads the Deadlines sheet and sends a reminder email to the owner of
 * any row whose due date has just crossed a 7/3/1-day threshold. Tracks
 * the last stage sent so each tier fires exactly once per row.
 */
function tieredCountdown() {
  const sheet = SpreadsheetApp.openById(DEADLINES_SHEET_ID).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  if (values.length < 2) {
    Logger.log('No deadline rows — nothing to do.');
    return;
  }

  // 1. Split off the header and build a name -> column-index map.
  const [header, ...rows] = values;
  const col = Object.fromEntries(header.map((k, i) => [k, i]));
  const today = new Date();

  let sent = 0;

  rows.forEach((r, i) => {
    // 2. Skip rows without a real due date.
    if (!(r[col.due] instanceof Date)) return;

    // 3. Days remaining, rounded up so an item due at 6pm today still
    //    counts as "1 day out" if we run this morning.
    const days = Math.ceil((r[col.due] - today) / ONE_DAY_MS);

    // 4. Find the largest stage the row qualifies for that we have not
    //    already sent. STAGES is [7, 3, 1] — we walk that order so a
    //    fresh week-out row picks 7, a three-day row picks 3, etc.
    const stage = STAGES.find((s) => days <= s && r[col.lastReminded] !== s);
    if (!stage) return;

    // 5. Send the reminder and stamp the lastReminded column so the next
    //    run can move on to the next tier without re-sending this one.
    GmailApp.sendEmail(
      r[col.owner],
      `${days}d to deadline: ${r[col.task]}`,
      `Reminder: "${r[col.task]}" is due in ${days} day${days !== 1 ? 's' : ''}.`
    );
    values[i + 1][col.lastReminded] = stage;
    sent++;
  });

  // 6. Persist any updated lastReminded values in a single write.
  sheet.getDataRange().setValues(values);
  Logger.log('Sent ' + sent + ' reminder(s).');
}

How it works

1. tieredCountdown reads the whole Deadlines sheet and bails if there are no data rows.
2. It builds a col map from the header so the rest of the script reads r[col.due] instead of r[2].
3. For each row it computes days remaining as (due - today) / 86,400,000, rounded up so a deadline at the end of today still counts as “1 day”.
4. It uses Array.find over STAGES ([7, 3, 1]) to pick the highest tier the row currently qualifies for that has not already been sent. The stored lastReminded value gates against repeats.
5. It sends the email and writes the new stage back into the in-memory grid.
6. After the loop, a single setValues call persists every updated lastReminded cell.

Example run

Suppose today is Monday and the sheet looks like this:

task	owner	due	lastReminded
Edit reel for Fabrikam	[email protected]	2025-10-07 (7d)
Brochure copy to client	[email protected]	2025-10-03 (3d)	7
Site launch checklist	[email protected]	2025-10-01 (1d)	3
Brief next quarter	[email protected]	2025-11-15 (49d)

After a run:

• Row 1 crosses 7 days → email to Alex, lastReminded becomes 7.
• Row 2 crosses 3 days (already had 7) → email to Priya, lastReminded becomes 3.
• Row 3 crosses 1 day (already had 3) → email to Sam, lastReminded becomes 1.
• Row 4 is 49 days out, so the find() returns nothing and no email goes out.

Tomorrow’s run sends nothing for rows 1–3 unless they cross another tier.

Trigger it

Run this once a day, early enough to land before the workday starts:

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

Watch out for

• If you change STAGES, the existing lastReminded values may no longer line up. The script will simply pick the next matching stage on the next run, but you can clear the column to restart cleanly.
• A row that was already past its first tier on the day you set it up will fire all earlier tiers at once. Pre-fill lastReminded for those rows to the largest tier that has already passed if you want to skip them.
• The script sends one email per row. A row with five owners in the owner cell (comma-separated) works because Gmail accepts that, but bouncing addresses still cost a quota slot — keep the column clean.
• A skipped run (quota outage, trigger pause) does not lose data — rows still progress on the next run. They just arrive a day late.