Chase unsigned contracts automatically

A contract that sits unsigned is a deal that has stalled, and the reason is almost never refusal — it is a busy inbox and a missing nudge. But chasing by hand is its own problem: someone has to remember who was sent what, when, and whether they have already been reminded. That memory work quietly slips, and agreements drift for weeks.

Northwind sends contracts through a DocuSign-style flow and records each one in a Contracts sheet — when it went out and whether it has come back signed. This script reads that sheet every morning and sends a friendly follow-up to anyone whose contract has been open for 3, 7, or 14 days, escalating gently as time passes. It writes the chase stage back to the sheet so nobody is ever nudged twice for the same milestone.

What you’ll need

• A Google Sheet whose first tab is named for your contracts, with a header row containing exactly these columns: client, email, contract, sentAt, signed, lastChase.
• sentAt should hold a real date (the day the contract was sent). signed is left blank until the contract comes back, then filled with anything truthy. lastChase is managed by the script — leave it empty to start.
• The Sheet’s ID, set as CONTRACTS_SHEET in the config below.

The script

// The Sheet whose first tab tracks sent contracts.
const CONTRACTS_SHEET = '1abcContractsSheetId';

// Days-open milestones at which to send a reminder.
const STAGES = [3, 7, 14];

// Milliseconds in a day — used to convert a date gap into whole days.
const MS_PER_DAY = 86400000;

/**
 * Reads the Contracts sheet and emails a follow-up for any unsigned contract
 * that has just crossed a 3-, 7- or 14-day milestone.
 */
function chaseUnsignedContracts() {
  const sheet = SpreadsheetApp.openById(CONTRACTS_SHEET).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [header, ...rows] = values;

  // 1. Bail out early if the sheet has a header but no contract rows.
  if (!rows.length) {
    Logger.log('No contracts to chase — nothing to do.');
    return;
  }

  // 2. Map column names to indexes so the code never depends on column order.
  const col = Object.fromEntries(header.map((h, i) => [h, i]));
  const today = new Date();

  let chased = 0;
  rows.forEach((row, i) => {
    // 3. Skip anything already signed.
    if (row[col.signed]) return;

    // 4. Skip rows with a missing or unparseable sent date.
    const sentAt = new Date(row[col.sentAt]);
    if (isNaN(sentAt)) return;

    // 5. Work out how many whole days the contract has been open, then find
    //    the highest milestone reached that has not already been chased.
    const daysOpen = Math.floor((today - sentAt) / MS_PER_DAY);
    const stage = STAGES.find(
      (s) => daysOpen >= s && row[col.lastChase] !== s,
    );
    if (!stage) return;

    // 6. Send the nudge and record the stage so it is never repeated.
    GmailApp.sendEmail(
      row[col.email],
      'Quick nudge on ' + row[col.contract],
      'Hi ' + row[col.client] + ',\n\n'
        + 'Following up on the contract sent ' + daysOpen + ' days ago. '
        + "Let me know if anything's blocking.\n\n"
        + '— Awadesh, Northwind',
    );
    values[i + 1][col.lastChase] = stage;
    chased++;
  });

  // 7. Write the updated lastChase values back in one operation.
  sheet.getDataRange().setValues(values);
  Logger.log('Sent ' + chased + ' chase email(s).');
}

How it works

1. chaseUnsignedContracts opens the contracts sheet and reads everything in one call, splitting off the header row from the contract rows.
2. If there are no contract rows, it logs a message and stops.
3. It builds a name-to-index map from the header, so the script keeps working even if the columns are reordered later.
4. For each row it skips anything already marked signed, and skips any row whose sentAt is missing or not a valid date.
5. It calculates how many whole days the contract has been open, then picks the first milestone in STAGES that the contract has reached but lastChase does not already record. find returns the earliest unmet stage, so a contract that has been open 10 days but last chased at day 3 fires the day-7 reminder next, not the day-14 one.
6. It sends a short, friendly email and updates that row’s lastChase in the in-memory copy of the sheet.
7. After the loop it writes the whole grid back once, which is far faster than updating cells one at a time.

Example run

Say the Contracts sheet looks like this on the morning of a run (today being 25 May):

client	email	contract	sentAt	signed	lastChase
Priya Shah	[email protected]	Acme retainer 2026	2026-05-22
Tom Reid	[email protected]	Borealis pilot	2026-05-18		3
Lena Voss	[email protected]	Delta renewal	2026-05-01	yes	7

The run sends two emails:

• Priya — open 3 days, no prior chase, so the day-3 reminder goes out and lastChase becomes 3.
• Tom — open 7 days, already chased at day 3, so the day-7 reminder goes out and lastChase becomes 7.

Lena’s row is skipped entirely because her contract is signed.

Trigger it

This should run once a day, early, so reminders land before the recipient’s inbox fills up:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose chaseUnsignedContracts, event source Time-driven, type Day timer, time of day 9am to 10am.
4. Save and approve the authorisation prompt.

Watch out for

• Mark contracts signed promptly. The script only knows a deal is done when the signed column is filled — if that lags, a client who has already signed will keep getting chased.
• The day-7 and day-14 reminders use the same wording as day 3. If you want the tone to escalate, branch the email body on stage and write firmer copy for the later milestones.
• A run only fires one milestone per contract per day. A contract that jumps several milestones at once (for example if the trigger was paused) catches up one stage per day until it is current.
• GmailApp.sendEmail counts against the daily sending quota — 100 messages on consumer accounts, 1,500 on Workspace. A normal contract pipeline stays well under this, but a one-off bulk import of old contracts could trip it.
• Make sure sentAt cells are real dates, not text. A date typed as a string that the sheet does not recognise will fail the isNaN check and that contract will never be chased.