Run a small-business CRM in Google Sheets with AI-written Apps Script

You are an experienced Google Apps Script developer. Below is my CRM spreadsheet's current Apps Script and the exact header row of my {{sheet_name}} tab.

Header row (tab-separated, exact spelling):
{{header_row}}

Current script:
```javascript
{{current_script}}
```

Change request: {{change_request}}

Rules:
- Return the COMPLETE updated script in one code block, not a diff.
- Find columns by header name, never by hard-coded column number.
- Keep onEdit as a simple trigger: no MailApp, GmailApp or other services that need authorisation inside it.
- Stay within Apps Script quotas (6 minutes per execution; MailApp recipient limits) and read/write ranges in bulk where possible.
- After the code, list: (1) what changed, (2) any new permission Google will ask for, (3) a 3-step manual test I can run on a copy of the sheet.

My Google Apps Script for a Sheets CRM fails. Explain the cause in plain English, then give the corrected function(s).

Function that failed: {{function_name}}
Trigger type: {{trigger_type}}
Exact error message and line from Executions:
{{error_message}}

Header row of the tab it reads:
{{header_row}}

Relevant code:
```javascript
{{code_snippet}}
```

If the cause is a permissions, quota or simple-trigger restriction rather than a code bug, say so and tell me what to change in the setup instead.

Here is this week's export of my Deals tab as CSV (columns include Stage, Value, Owner, Next follow-up, Stage changed at, Created at):

{{deals_csv}}

Today is {{today}}. Our stages in order are: {{stage_order}}.

Produce:
1. Open pipeline value and deal count by stage (table).
2. Deals with no Next follow-up date, or a follow-up more than 7 days overdue (table: Company, Owner, days overdue).
3. Deals that have sat in the same stage for more than {{stale_days}} days, using Stage changed at.
4. Won and Lost this week, with lost reasons.
5. Three specific actions for the team, each naming a deal.
Use only the data provided. If a value is missing or ambiguous, say so instead of guessing.

SPREADSHEET: "CRM" (one Google Sheets file, shared with the team as Editors)

TAB 1 — Deals  (one row per opportunity; row 1 = headers, exact names matter for the script)
| Col | Header            | Type / validation                                                      |
|-----|-------------------|------------------------------------------------------------------------|
| A   | Deal ID           | Text. Formula in A2 (optional): =IF(B2="","","D-"&TEXT(ROW()-1,"0000")) |
| B   | Company           | Text. Data validation: dropdown from a range = Companies!A2:A           |
| C   | Contact name      | Text                                                                   |
| D   | Contact email     | Text. Data validation: "Text is valid email"                            |
| E   | Owner             | Email of the teammate who owns the deal. Dropdown from Lists!C2:C       |
| F   | Stage             | Dropdown from Lists!A2:A (reject invalid input)                         |
| G   | Value             | Number, format Currency. Validation: number >= 0                        |
| H   | Next step         | Text — a verb phrase: "Send revised quote", "Call re: start date"       |
| I   | Next follow-up    | Date. Validation: "is valid date"                                       |
| J   | Stage changed at  | Date-time. Filled by the script — do not type here                      |
| K   | Created at        | Date-time. Filled by the script on first stage selection                |
| L   | Source            | Dropdown from Lists!B2:B                                                |
| M   | Lost reason       | Text (fill when Stage = Lost)                                           |
| N   | Notes             | Text — short; long history goes in the Activities tab                   |

TAB 2 — Companies
| A Company | B Website | C Industry | D City | E Size (employees) | F Notes |

TAB 3 — Activities  (append-only log; use a Google Form or type directly)
| A Date | B Deal ID | C Type (Call / Email / Meeting / Note) | D Summary | E Logged by |
Tip: =FILTER(Activities!A:E, Activities!B:B = A2) in a side panel shows a deal's history.

TAB 4 — Lists  (single source for dropdowns)
| A Stage          | B Source        | C Owner              |
|------------------|-----------------|----------------------|
| Lead             | Referral        | you@yourfirm.com     |
| Qualified        | Website form    | colleague@yourfirm.com |
| Proposal sent    | Event           |                      |
| Negotiation      | Outbound        |                      |
| Won              | Existing client |                      |
| Lost             |                 |                      |
Keep "Won" and "Lost" spelled exactly like this — the script treats them as closed.

TAB 5 — Follow-ups due   (created by the script; formula-driven, do not edit by hand)
TAB 6 — Stage log        (created by the script; one row per stage change)

PROTECTION: Data > Protect sheets and ranges → protect "Follow-ups due", "Stage log" and
Deals!J:K with "Show a warning when editing this range" so nobody overwrites them by accident.

/**
 * Small-business CRM helpers for Google Sheets.
 * Install: Extensions > Apps Script > paste this file > Save.
 * Reload the spreadsheet, then run CRM > "Set up (run once)" and approve the permissions.
 *
 * Adds:
 *  1. onEdit timestamp: when Stage changes, writes "Stage changed at" (and "Created at" if blank)
 *     and appends a row to the "Stage log" tab.
 *  2. A "Follow-ups due" tab (live FILTER formula) plus red/amber highlighting on Deals.
 *  3. A daily email digest of overdue / due-today follow-ups, one email per Owner, via MailApp.
 */
const CONFIG = {
  DEALS_SHEET: 'Deals',
  DUE_SHEET: 'Follow-ups due',
  LOG_SHEET: 'Stage log',
  CLOSED_STAGES: ['Won', 'Lost'],
  DIGEST_HOUR: 7,        // hour of day in the script time zone (Project Settings)
  FALLBACK_EMAIL: '',    // gets deals with an empty Owner; blank = the person who ran setup
};

const COL = {
  ID: 'Deal ID', COMPANY: 'Company', CONTACT: 'Contact name', EMAIL: 'Contact email',
  OWNER: 'Owner', STAGE: 'Stage', VALUE: 'Value', NEXT_STEP: 'Next step',
  FOLLOW_UP: 'Next follow-up', STAGE_CHANGED: 'Stage changed at', CREATED: 'Created at',
};

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('CRM')
    .addItem('Set up (run once)', 'setup')
    .addItem('Rebuild follow-ups view', 'buildFollowUpsView')
    .addItem('Send follow-up digest now', 'sendFollowUpDigest')
    .addToUi();
}

/** Maps header text -> 1-based column number. Throws if a required header is missing. */
function headerMap_(sheet) {
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const map = {};
  headers.forEach(function (h, i) { if (h !== '') map[String(h).trim()] = i + 1; });
  Object.keys(COL).forEach(function (k) {
    if (!map[COL[k]]) throw new Error('Missing column "' + COL[k] + '" on tab ' + sheet.getName());
  });
  return map;
}

function colLetter_(n) {
  let s = '';
  while (n > 0) { const m = (n - 1) % 26; s = String.fromCharCode(65 + m) + s; n = Math.floor((n - 1) / 26); }
  return s;
}

/* ---------- 1. Stage-change timestamps (simple trigger) ---------- */
function onEdit(e) {
  if (!e || !e.range) return;
  const sheet = e.range.getSheet();
  if (sheet.getName() !== CONFIG.DEALS_SHEET || e.range.getLastRow() < 2) return;
  let cols;
  try { cols = headerMap_(sheet); } catch (err) { e.source.toast(err.message, 'CRM script'); return; }
  const stageCol = cols[COL.STAGE];
  if (stageCol < e.range.getColumn() || stageCol > e.range.getLastColumn()) return;

  const now = new Date();
  const singleCell = e.range.getNumRows() === 1 && e.range.getNumColumns() === 1;
  const editor = (e.user && e.user.getEmail && e.user.getEmail()) || '';
  const log = e.source.getSheetByName(CONFIG.LOG_SHEET);
  const firstRow = Math.max(2, e.range.getRow());

  for (let r = firstRow; r <= e.range.getLastRow(); r++) {
    const stage = sheet.getRange(r, stageCol).getValue();
    sheet.getRange(r, cols[COL.STAGE_CHANGED]).setValue(now);
    const created = sheet.getRange(r, cols[COL.CREATED]);
    if (created.getValue() === '') created.setValue(now);
    if (log) {
      log.appendRow([
        now,
        sheet.getRange(r, cols[COL.ID]).getValue(),
        sheet.getRange(r, cols[COL.COMPANY]).getValue(),
        singleCell ? (e.oldValue || '') : '(bulk edit)',
        stage,
        editor,
      ]);
    }
  }
}

/* ---------- 2. Follow-ups view + highlighting ---------- */
function buildFollowUpsView() {
  const ss = SpreadsheetApp.getActive();
  const deals = ss.getSheetByName(CONFIG.DEALS_SHEET);
  const cols = headerMap_(deals);
  const lastCol = colLetter_(deals.getLastColumn());
  const f = colLetter_(cols[COL.FOLLOW_UP]);
  const s = colLetter_(cols[COL.STAGE]);
  const src = "'" + CONFIG.DEALS_SHEET + "'!";

  const conditions = [src + f + '2:' + f + '<>""', src + f + '2:' + f + '<=TODAY()']
    .concat(CONFIG.CLOSED_STAGES.map(function (st) { return src + s + '2:' + s + '<>"' + st + '"'; }));
  const formula = '=IFERROR(SORT(FILTER(' + src + 'A2:' + lastCol + ', ' + conditions.join(', ') +
    '), ' + cols[COL.FOLLOW_UP] + ', TRUE), "Nothing due. Nice.")';

  const due = ss.getSheetByName(CONFIG.DUE_SHEET) || ss.insertSheet(CONFIG.DUE_SHEET);
  due.clear();
  const headers = deals.getRange(1, 1, 1, deals.getLastColumn()).getValues();
  due.getRange(1, 1, 1, headers[0].length).setValues(headers).setFontWeight('bold');
  due.getRange('A2').setFormula(formula);
  due.setFrozenRows(1);
}

function addHighlighting_() {
  const deals = SpreadsheetApp.getActive().getSheetByName(CONFIG.DEALS_SHEET);
  const cols = headerMap_(deals);
  const f = '$' + colLetter_(cols[COL.FOLLOW_UP]) + '2';
  const s = '$' + colLetter_(cols[COL.STAGE]) + '2';
  const open = CONFIG.CLOSED_STAGES.map(function (st) { return s + '<>"' + st + '"'; }).join(',');
  const overdue = '=AND(' + f + '<>"",' + f + '<TODAY(),' + open + ')';
  const today = '=AND(' + f + '<>"",' + f + '=TODAY(),' + open + ')';
  const range = deals.getRange(2, 1, deals.getMaxRows() - 1, deals.getLastColumn());

  const rules = deals.getConditionalFormatRules().filter(function (r) {
    const c = r.getBooleanCondition();
    const v = c && c.getCriteriaValues()[0];
    return v !== overdue && v !== today; // drop our old copies so re-running setup doesn't duplicate
  });
  rules.push(SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied(overdue)
    .setBackground('#f4c7c3').setRanges([range]).build());
  rules.push(SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied(today)
    .setBackground('#fce8b2').setRanges([range]).build());
  deals.setConditionalFormatRules(rules);
}

/* ---------- 3. Daily digest (installable time-driven trigger) ---------- */
function sendFollowUpDigest() {
  const ss = SpreadsheetApp.getActive();
  const deals = ss.getSheetByName(CONFIG.DEALS_SHEET);
  const cols = headerMap_(deals);
  const values = deals.getDataRange().getValues();
  const fallback = CONFIG.FALLBACK_EMAIL || Session.getEffectiveUser().getEmail();

  const startOfToday = new Date(); startOfToday.setHours(0, 0, 0, 0);
  const startOfTomorrow = new Date(startOfToday.getTime() + 86400000);
  const byOwner = {};

  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    const due = row[cols[COL.FOLLOW_UP] - 1];
    const stage = String(row[cols[COL.STAGE] - 1]);
    if (!(due instanceof Date) || CONFIG.CLOSED_STAGES.indexOf(stage) !== -1) continue;
    if (due.getTime() >= startOfTomorrow.getTime()) continue;
    const dueDay = new Date(due); dueDay.setHours(0, 0, 0, 0);
    const owner = String(row[cols[COL.OWNER] - 1] || fallback).trim();
    (byOwner[owner] = byOwner[owner] || []).push({
      company: row[cols[COL.COMPANY] - 1], contact: row[cols[COL.CONTACT] - 1],
      stage: stage, next: row[cols[COL.NEXT_STEP] - 1], value: row[cols[COL.VALUE] - 1],
      daysLate: Math.round((startOfToday - dueDay) / 86400000), row: i + 1,
    });
  }

  const url = ss.getUrl() + '#gid=' + deals.getSheetId();
  const tz = ss.getSpreadsheetTimeZone();
  Object.keys(byOwner).forEach(function (owner) {
    if (MailApp.getRemainingDailyQuota() < 1) { console.warn('MailApp daily quota used up'); return; }
    const items = byOwner[owner].sort(function (a, b) { return b.daysLate - a.daysLate; });
    const rowsHtml = items.map(function (d) {
      return '<tr><td>' + esc_(d.company) + '</td><td>' + esc_(d.contact) + '</td><td>' + esc_(d.stage) +
        '</td><td>' + esc_(d.next) + '</td><td style="text-align:right">' + esc_(d.value) + '</td><td>' +
        (d.daysLate === 0 ? 'today' : d.daysLate + ' day(s) late') + '</td><td>row ' + d.row + '</td></tr>';
    }).join('');
    const html = '<p>' + items.length + ' follow-up(s) due as of ' +
      Utilities.formatDate(startOfToday, tz, 'EEE d MMM yyyy') + ':</p>' +
      '<table border="1" cellpadding="6" style="border-collapse:collapse;font-family:sans-serif;font-size:13px">' +
      '<tr><th>Company</th><th>Contact</th><th>Stage</th><th>Next step</th><th>Value</th><th>Due</th><th>Where</th></tr>' +
      rowsHtml + '</table><p><a href="' + url + '">Open the CRM</a></p>';
    const text = items.map(function (d) {
      return '- ' + d.company + ' (' + d.stage + '): ' + d.next + ' [' + (d.daysLate === 0 ? 'today' : d.daysLate + 'd late') + ']';
    }).join('\n') + '\n\n' + url;
    MailApp.sendEmail({ to: owner, subject: 'CRM: ' + items.length + ' follow-up(s) due', body: text, htmlBody: html });
  });
}

function esc_(v) {
  return String(v === null || v === undefined ? '' : v)
    .replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/"/g, '&quot;');
}

/* ---------- One-time setup ---------- */
function setup() {
  const ss = SpreadsheetApp.getActive();
  if (!ss.getSheetByName(CONFIG.LOG_SHEET)) {
    ss.insertSheet(CONFIG.LOG_SHEET)
      .appendRow(['Changed at', 'Deal ID', 'Company', 'From stage', 'To stage', 'Changed by']);
  }
  buildFollowUpsView();
  addHighlighting_();
  ScriptApp.getProjectTriggers().forEach(function (t) {
    if (t.getHandlerFunction() === 'sendFollowUpDigest') ScriptApp.deleteTrigger(t);
  });
  ScriptApp.newTrigger('sendFollowUpDigest').timeBased().everyDays(1).atHour(CONFIG.DIGEST_HOUR).create();
  ss.toast('CRM set up: follow-ups view, highlighting and daily digest at ~' + CONFIG.DIGEST_HOUR + ':00.', 'CRM', 8);
}