Send a personalized renewal reminder series

A retainer that lapses quietly is lost revenue nobody decided to let go of. The client meant to renew, the reminder never came, and by the time anyone notices, the relationship has gone cold. The fix is a gentle drip — a heads-up a month out, a nudge two weeks before, and a final note three days before the date.

Northwind tracks every retainer renewal in the Clients sheet. This script runs once a day, works out how far each client is from their renewal, and sends only the next reminder owed in the sequence — never a duplicate, never two at once. A lastReminder column records the last stage sent, so the script is a safe no-op for anyone with nothing due today.

What you’ll need

• A Clients sheet with columns: name, email, renewalDate, and lastReminder.
• renewalDate holds a real date value (not text) so the script can do date arithmetic on it.
• lastReminder stores the last day-offset sent — 30, 14, 3, or empty for a client who hasn’t been reminded yet.

The script

// The Clients sheet that holds renewal dates.
const CLIENTS_SHEET = '1abcClientsSheetId';

// Reminder stages, in days before the renewal date. Sorted descending
// so the script always picks the earliest stage still owed.
const STAGES = [30, 14, 3];

// Milliseconds in a day, for the days-out calculation.
const MS_PER_DAY = 86400000;

/**
 * Reads the Clients sheet and, for each client, sends the next renewal
 * reminder owed in the sequence — at most one per run.
 */
function sendRenewalReminders() {
  // 1. Read every row of the Clients sheet.
  const sheet = SpreadsheetApp.openById(CLIENTS_SHEET).getSheets()[0];
  const range = sheet.getDataRange();
  const values = range.getValues();
  const [header, ...rows] = values;

  if (!rows.length) {
    Logger.log('No client rows found — nothing to do.');
    return;
  }

  // Map header names to column indexes, and capture today's date once.
  const col = Object.fromEntries(header.map((h, i) => [h, i]));
  const today = new Date();
  let sent = 0;

  rows.forEach((row, i) => {
    // 2. Parse the renewal date; skip rows with no valid date.
    const renewal = new Date(row[col.renewalDate]);
    if (isNaN(renewal)) return;

    // 3. How many whole days until renewal.
    const daysOut = Math.ceil((renewal - today) / MS_PER_DAY);

    // 4. Find the earliest stage that is now due and not yet sent.
    const stage = STAGES.find((s) => daysOut <= s && row[col.lastReminder] !== s);
    if (!stage) return;

    // 5. Send the reminder and record the stage on the in-memory copy.
    sendReminder(row[col.name], row[col.email], daysOut, stage);
    values[i + 1][col.lastReminder] = stage;
    sent++;
  });

  // 6. Write the updated lastReminder values back in one operation.
  range.setValues(values);
  Logger.log(`Sent ${sent} renewal reminder(s).`);
}

/**
 * Sends a single reminder email, with subject and tone matched to the
 * stage (30, 14, or 3 days out).
 */
function sendReminder(name, email, daysOut, stage) {
  const subjects = {
    30: `Heads up: your Northwind retainer renews in ${daysOut} days`,
    14: `Two weeks until your renewal, ${name}`,
    3: `Final reminder — renewal in ${daysOut} days`,
  };
  GmailApp.sendEmail(email, subjects[stage],
    `Hi ${name},\n\nQuick reminder — your retainer renews in ${daysOut} days. ` +
    `Reply if you'd like to adjust the scope.\n\n— Northwind Studios`);
}

How it works

1. sendRenewalReminders reads the whole Clients sheet and bails out early if it is empty. The col map lets the loop reference columns by name, and today is captured once so every row is measured against the same moment.
2. For each row it parses renewalDate into a Date. A blank or malformed cell produces NaN, and that row is skipped.
3. It calculates daysOut — the whole number of days between today and the renewal date.
4. STAGES.find walks the stages from largest to smallest and returns the first one that is both due (daysOut <= s) and not already recorded in lastReminder. Sorting descending means the script always picks the earliest stage still owed, so a client never skips a step even if the trigger missed a day.
5. When a stage is found, sendReminder sends a message whose subject and tone escalate with the stage, and the stage number is written into the in-memory values array. Because the header was sliced off, row i lives at values[i + 1].
6. After the loop, all the lastReminder updates are written back to the sheet in a single setValues call.

Example run

Suppose the script runs on 25 May with this Clients sheet:

name	email	renewalDate	lastReminder
Acme Co	[email protected]	2026-06-08	30
Belltower	[email protected]	2026-06-22	(empty)
Crayfish Ltd	[email protected]	2026-08-01	(empty)

Acme is 14 days out and last received the 30 reminder, so it gets the two-week email and lastReminder becomes 14. Belltower is 28 days out and has had nothing, so it gets the 30 reminder. Crayfish is 68 days out — no stage is due yet, so it is skipped. The log reads Sent 2 renewal reminder(s).

Trigger it

1. In the Apps Script editor, open Triggers and click Add trigger.
2. Function: sendRenewalReminders. Event source: time-based. Type: day timer, around 8am.
3. Save. The script is a no-op for any client with nothing due, so running it daily is cheap and safe.

Watch out for

• renewalDate must be a real date value. If the column is formatted as text, new Date() may misread it or return NaN, and that client silently never gets reminded — store dates as dates.
• lastReminder is the only thing preventing duplicates. If you clear or edit that cell, the client can receive an earlier-stage email again on the next run. Treat the column as script-owned.
• A client added inside three days of renewal still gets the 3-day reminder but skips the 30 and 14 stages — there is no time to send them. That is the intended behaviour, not a bug.
• If the daily trigger fails to run for several days, the find logic still picks the most pressing stage owed, so the client isn’t spammed with every missed reminder at once — they get one, the right one.
• Stages compare with strict !==. If lastReminder is stored as text (e.g. "30") it won’t match the numeric 30 in STAGES, and the stage will fire again. Keep the column as numbers.