Google Sheets Lead Tracker with stale-lead flags

A simple lead CRM in Google Sheets. An Apps Script stamps Created / Last Updated times as you type and flags open leads nobody has contacted in 3+ days.

How it flows

What it does

• Leads tab with 13 columns: Lead ID, Created, Name, Phone, Email, Source, Service, Status, Next Step, Last Contact, Last Updated, Stale?, Notes.
• Type in any lead row and the script fills Created (first time), a Lead ID, and Last Updated. Changing Status also sets Last Contact.
• Menu "Lead Tracker" > "Flag stale leads now": any lead not Won/Lost with no contact for more than 3 days gets "STALE (N days)" and a light-red row.
• One click installs a daily 8am check so the flags stay current without you opening the sheet.

Download

Apps Script code (.gs)Sheet template (.xlsx, with Status dropdown)Sheet template (.csv)Install guide (PDF)

Install in short

1. Open Google Drive, click New > File upload, and upload hwa-lead-tracker-template.xlsx. Open it, then File > Save as Google Sheets. (Or: create a blank Google Sheet, File > Import > Upload the .csv, and rename the tab to Leads.)
2. Check the first tab is named exactly Leads and row 1 has the 13 headers. Delete the two example rows when you are ready.
3. Click Extensions > Apps Script. Delete the starter code, paste all of hwa-lead-tracker.gs, and click Save (disk icon).
4. Go back to the sheet and reload the browser tab. A Lead Tracker menu appears after a few seconds.
5. Click Lead Tracker > Flag stale leads now. Google asks you to authorize the script the first time: choose your account, then Advanced > Go to (project name) > Allow. This is your own script in your own sheet.
6. Click Lead Tracker > Install daily stale check (8am). That adds one time trigger (see it in Apps Script > Triggers). Remove it any time with Lead Tracker > Remove daily stale check.
7. Optional: change STALE_DAYS (default 3) or CLOSED_STATUSES at the top of the script, then Save.

Test it

• Type a name in a new row: Created, Lead ID and Last Updated fill in.
• Set a row's Last Contact to a date 5 days ago and Status to New, then run Flag stale leads now: the row turns light red and shows STALE (5 days).
• Change that row's Status to Won and run it again: the flag clears.

Good to know

• Simple triggers (onEdit) run on edits made by people, not on rows added by other automations. The daily check still covers those rows.
• Dates are compared using the sheet's time zone (File > Settings).

Preview: hwa-lead-tracker.gs

/**
 * HWA sample: Google Sheets Lead Tracker (Apps Script)
 * Built with AI assistance, reviewed before delivery. This is a SAMPLE, not a supported product.
 *
 * What it does
 *  - When you type in a lead row on the "Leads" tab, it fills "Created" (first time only)
 *    and updates "Last Updated" with the current date/time.
 *  - When you change "Status", it also sets "Last Contact" to now.
 *  - "Flag stale leads" marks every open lead (Status not Won/Lost) whose Last Contact
 *    (or Created, if no contact yet) is older than STALE_DAYS, and shades that row.
 *  - "Install daily stale check" adds a time trigger that runs the stale check every morning.
 *
 * Setup: Extensions > Apps Script, paste this file, Save, reload the sheet,
 * then use the new "Lead Tracker" menu. See the install PDF for step-by-step setup.
 */

const SHEET_NAME = 'Leads';
const STALE_DAYS = 3;                       // change to suit your follow-up rhythm
const CLOSED_STATUSES = ['Won', 'Lost'];    // rows with these statuses are never flagged
const STALE_COLOR = '#fde2e1';              // light red row shading for stale leads
const HEADERS = ['Lead ID', 'Created', 'Name', 'Phone', 'Email', 'Source', 'Service', 'Status',
                 'Next Step', 'Last Contact', 'Last Updated', 'Stale?', 'Notes'];

/** Adds the "Lead Tracker" menu when the spreadsheet opens. */
function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Lead Tracker')
    .addItem('Flag stale leads now', 'flagStaleLeads')
    .addItem('Install daily stale check (8am)', 'installDailyTrigger')
    .addItem('Remove daily stale check', 'removeDailyTrigger')
    .addToUi();
}

/** Column number (1-based) for a header name, read from row 1. */
function col_(sheet, name) {
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const i = headers.indexOf(name);
  if (i === -1) throw new Error('Missing column "' + name + '" in row 1 of ' + SHEET_NAME);
  return i + 1;
}

/** Simple trigger: runs automatically on every manual edit. */
function onEdit(e) {
  if (!e || !e.range) return;
  const sheet = e.range.getSheet();
  if (sheet.getName() !== SHEET_NAME) return;
  const firstRow = e.range.getRow();
  const numRows = e.range.getNumRows();
  if (firstRow + numRows - 1 < 2) return; // header row only

  const cCreated = col_(sheet, 'Created');
  const cUpdated = col_(sheet, 'Last Updated');
  const cContact = col_(sheet, 'Last Contact');
  const cStatus = col_(sheet, 'Status');
  const cId = col_(sheet, 'Lead ID');
  const editedStatus = e.range.getColumn() <= cStatus && cStatus <= e.range.getLastColumn();
  const now = new Date();

  for (let r = Math.max(firstRow, 2); r < firstRow + numRows; r++) {
    // Ignore edits that only touch our own automatic columns.
    const rowValues = sheet.getRange(r, 1, 1, sheet.getLastColumn()).getValues()[0];
    const hasData = rowValues.some(function (v) { return v !== '' && v !== null; });
    if (!hasData) continue;
    if (!sheet.getRange(r, cCreated).getValue()) sheet.getRange(r, cCreated).setValue(now);
    if (!sheet.getRange(r, cId).getValue()) sheet.getRange(r, cId).setValue('L-' + Utilities.formatDate(now, Session.getScriptTimeZone(), 'yyMMdd') + '-' + r);
    sheet.getRange(r, cUpdated).setValue(now);
    if (editedStatus) sheet.getRange(r, cContact).setValue(now);
  }
}

/** Marks open leads with no contact for more than STALE_DAYS days. Safe to run any time. */
function flagStaleLeads() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(SHEET_NAME);
  if (!sheet) throw new Error('No tab named ' + SHEET_NAME);
  const lastRow = sheet.getLastRow();
  if (lastRow < 2) return 0;
  const lastCol = sheet.getLastColumn();
  const cName = col_(sheet, 'Name');
  const cStatus = col_(sheet, 'Status');
  const cCreated = col_(sheet, 'Created');
  const cContact = col_(sheet, 'Last Contact');
  const cStale = col_(sheet, 'Stale?');
  const data = sheet.getRange(2, 1, lastRow - 1, lastCol).getValues();
  const now = new Date().getTime();
  const dayMs = 24 * 60 * 60 * 1000;
  const flags = [];
  const colors = [];
  let count = 0;

  data.forEach(function (row) {
    const name = row[cName - 1];
    const status = String(row[cStatus - 1] || '').trim();
    const ref = row[cContact - 1] || row[cCreated - 1];
    let flag = '';
    if (name && CLOSED_STATUSES.indexOf(status) === -1 && Object.prototype.toString.call(ref) === '[object Date]') {
      const days = Math.floor((now - ref.getTime()) / dayMs);
      if (days > STALE_DAYS) { flag = 'STALE (' + days + ' days)'; count++; }
    }
    flags.push([flag]);
    colors.push(new Array(lastCol).fill(flag ? STALE_COLOR : null));
  });

  sheet.getRange(2, cStale, flags.length, 1).setValues(flags);
  sheet.getRange(2, 1, colors.length, lastCol).setBackgrounds(colors);
  return count;
}

/** Creates one daily time-driven trigger for flagStaleLeads (removes duplicates first). */
function installDailyTrigger() {
  removeDailyTrigger();
  ScriptApp.newTrigger('flagStaleLeads').timeBased().everyDays(1).atHour(8).create();
  SpreadsheetApp.getActiveSpreadsheet().toast('Daily stale check installed (runs around 8am).', 'Lead Tracker');
}

/** Deletes any time trigger that runs flagStaleLeads. */
function removeDailyTrigger() {
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'flagStaleLeads') ScriptApp.deleteTrigger(t);
  });
}

This is a free sample to copy and adapt, not a managed service; no support or warranty. Test with sample data first.

Built with AI assistance, reviewed before delivery.

All samples · Free Missed-Call Rescue Pack