Auto-flag stale rows that need review

Northwind’s sales team works the Leads sheet by hand, and leads go cold quietly. A row sits untouched for three weeks, nobody notices, and a warm prospect drifts away — not because anyone dropped the ball on purpose, but because a long list gives no visual cue about which rows have gone stale.

This script gives the sheet that cue. Once a day it reads the lastTouched date on every lead, and any row that has not moved in more than 14 days gets a soft amber background. Fresh rows are cleared back to no fill, so the colour always reflects today’s reality. The team scans the sheet, sees the amber band, and knows exactly where to spend the morning.

What you’ll need

• A Google Sheet of leads, with a header row in row 1 and one lead per row below it.
• A column headed exactly lastTouched holding a real date value (not text) — this is the date the lead was last actioned.
• The spreadsheet ID, which you will paste into the config below.

The script

// The spreadsheet that holds your leads.
const LEADS_SHEET_ID = '1abcLeadsId';

// A lead counts as stale once it has been untouched for this many days.
const STALE_DAYS = 14;

// Background colour applied to stale rows. Fresh rows are cleared to no fill.
const STALE_COLOUR = '#fff4d6';

/**
 * Scans the Leads sheet and highlights every row whose lastTouched date
 * is older than STALE_DAYS. Fresh rows have their background cleared.
 */
function flagStaleLeads() {
  const sheet = SpreadsheetApp.openById(LEADS_SHEET_ID).getSheets()[0];

  // 1. Read the whole sheet — the header row plus every lead below it.
  const [header, ...rows] = sheet.getDataRange().getValues();
  if (!rows.length) {
    Logger.log('No leads to check — nothing to do.');
    return;
  }

  // 2. Map header names to column indexes so we can read lastTouched by name.
  const col = Object.fromEntries(header.map((name, i) => [name, i]));
  if (col.lastTouched === undefined) {
    Logger.log('No "lastTouched" column found — check the header row.');
    return;
  }

  // 3. Work out the cutoff: anything older than this timestamp is stale.
  const cutoff = Date.now() - STALE_DAYS * 86400000;

  // 4. Walk each lead and set or clear the row background accordingly.
  rows.forEach((row, i) => {
    const last = row[col.lastTouched];
    const isStale = last instanceof Date && last.getTime() < cutoff;
    const background = isStale ? STALE_COLOUR : null;

    // Row i+2: skip the header (row 1) and convert the zero-based index.
    sheet.getRange(i + 2, 1, 1, header.length).setBackground(background);
  });

  Logger.log('Checked ' + rows.length + ' leads for staleness.');
}

How it works

1. flagStaleLeads opens the leads spreadsheet and reads the entire data range in one call — far faster than reading row by row.
2. It splits the data into the header row and the rows of leads. If there are no leads, it logs a message and stops.
3. It builds a col lookup that maps each header name to its column index, so the rest of the script can refer to col.lastTouched instead of a fragile hard-coded number. If that column is missing, it bails out early.
4. It calculates a cutoff timestamp — the current time minus 14 days’ worth of milliseconds.
5. It loops over every lead. A row is stale only if its lastTouched value is a real Date and falls before the cutoff. Stale rows get the amber fill; every other row is cleared to no fill, so a lead that was actioned today loses its highlight on the next run.

Example run

Say the Leads sheet looks like this when the script runs on 6 December:

name	stage	lastTouched
Acme Corp	Proposal	2025-12-04
Belmont Ltd	Discovery	2025-11-15
Crestview	Negotiation	2025-12-01
Dunmore & Co	Discovery	2025-11-02

After the run, Belmont Ltd (21 days) and Dunmore & Co (34 days) are highlighted amber. Acme Corp and Crestview stay white — they were touched within the last fortnight. The team opens the sheet and the two rows that need chasing are impossible to miss.

Trigger it

This is a daily background job, so set a time-driven trigger:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose function flagStaleLeads, event source Time-driven, type Day timer, and pick an early-morning hour so the sheet is ready before the team starts.
4. Save and approve the authorisation prompt the first time.

Watch out for

• The lastTouched column must hold real date values. A date typed as plain text will never match instanceof Date, so the row will never be flagged — format the column as Date and re-enter any text dates.
• The script highlights the row width based on the header length. If your sheet has trailing blank columns in the header, the fill will extend across them too; trim the header range if that looks untidy.
• Highlighting overwrites any existing background colour on a row. If you use cell colours for something else in this sheet, this script will clear them.
• A 14-day threshold is a guess. Adjust STALE_DAYS to match how your team actually works — a fast pipeline might want 7, a slow one 30.
• Setting backgrounds one row at a time is fine for a few hundred leads. For a very large sheet, collect the colours into an array and apply them with a single setBackgrounds call to stay within the runtime limit.