{"data":{"slug":"small-business-crm-in-google-sheets","title":"Run a small-business CRM in Google Sheets with AI-written Apps Script","summary":"For a 1–5 person services business: a structured Google Sheet plus a short Apps Script (written and debugged with Claude or ChatGPT) gives you a pipeline, follow-up reminders and a daily email digest without a paid CRM seat. Try HubSpot's free CRM first if you don't want to maintain anything.","outcome":"Track leads and deals, never miss a follow-up, and see the pipeline at a glance — without paying per seat for a CRM.","capabilities":["crm-contacts","sales-pipeline","database-spreadsheet","code-generation"],"replaces":[{"tool":"pipedrive","scope":"full"},{"tool":"hubspot","scope":"full"},{"tool":"salesforce","scope":"partial"}],"requiredTools":[{"tool":"google-workspace","purpose":"Google Sheets holds the data; Apps Script adds the timestamps, follow-up view and digest; MailApp sends the email. A free Gmail account also works, with lower email quotas.","optional":false},{"tool":"claude","purpose":"Writes, adapts and debugs the Apps Script and formulas for your exact columns.","optional":true},{"tool":"chatgpt","purpose":"Alternative to Claude for writing and debugging the script.","optional":true}],"additionalMonthlyUSD":0,"difficulty":"moderate","skill":"basic","setupTime":"3–5 hours (sheet design, script install, one week of parallel use)","illustrative":true,"evidenceStatus":"editorial","lastReviewed":"2026-10-13","links":{"page":"/workflows/small-business-crm-in-google-sheets","api":"/api/v1/workflows/small-business-crm-in-google-sheets","markdown":"/workflows/small-business-crm-in-google-sheets.md"},"additionalCost":{"monthlyUSD":0,"explanation":"Google Sheets and Apps Script are included with Google Workspace and with free Google accounts. Using an AI assistant you already pay for adds nothing. The real cost is your time: about half a day to set up and ~15 minutes a month of upkeep."},"timePerRun":"Daily digest arrives automatically; logging a deal takes under a minute","goodFitFor":["Agencies, consultants, studios, trades and other services firms with 1–5 people selling","Fewer than a few thousand open contacts and a few dozen open deals at a time","Teams already living in Google Workspace who want follow-up discipline more than automation","Founders who outgrew a to-do list but find a CRM's per-seat price hard to justify"],"notFitFor":["More than ~5 people selling at once — concurrent edits, ownership rules and permissions get messy in a shared sheet","Teams that rely on automatic email/calendar sync, open/click tracking or sales sequences — a sheet has none of these","Anyone who needs forecasting, weighted pipeline by rep, quotas or multi-currency reporting out of the box","Regulated businesses that need a tamper-evident audit trail, field-level permissions or data-retention controls (a sheet's version history is not a compliance audit log)","People who will not maintain a script — if nobody owns it, start with HubSpot's free CRM instead"],"before":"Each person pays for a CRM seat (HubSpot Starter, Pipedrive or a light Salesforce edition). Deals live in a pipeline board; the CRM reminds you of follow-ups and logs email automatically. In practice a small team uses maybe 20% of it: a list of deals, a stage, a next step and a reminder.","after":"One shared Google Sheet with Deals, Companies, Activities and Lists tabs, dropdown validation for stages and owners, and a 150-line Apps Script. Changing a deal's stage stamps the time and writes to a Stage log. Overdue rows turn red, due-today rows amber, a 'Follow-ups due' tab lists everything that needs action, and every morning each owner gets an email of their overdue follow-ups. An AI assistant writes any changes to the script or formulas when you need them.","steps":[{"title":"Decide whether you should build this at all","body":"Before building, check two cheaper options:\n\n- **HubSpot's free CRM** is free with no expiry and covers **up to 2 users and 1,000 contacts** (per HubSpot's product page), including deal pipelines and email tracking. If you're two people with under 1,000 contacts, it's the no-maintenance choice.\n- **Your current CRM's export.** Export deals and contacts to CSV now (every major CRM supports it). You need this file either way.\n\nBuild the sheet if you're 3–5 people, you've hit the free tier's limits, or you want something you fully control."},{"title":"Create the sheet structure","body":"Create a new Google Sheet called **CRM** and add the tabs from the *Sheet schema* artifact: **Deals**, **Companies**, **Activities**, **Lists**.\n\n1. Type the Deals headers **exactly** as listed (the script finds columns by header name, so order doesn't matter but spelling does).\n2. Fill **Lists** with your stages, lead sources and team emails. Keep `Won` and `Lost` spelled exactly.\n3. Add data validation (**Data → Data validation**):\n   - `Deals!F2:F` → *Dropdown (from a range)* `Lists!A2:A`, *Reject the input*\n   - `Deals!E2:E` → dropdown from `Lists!C2:C`\n   - `Deals!I2:I` → *Is valid date*\n   - `Deals!D2:D` → *Text is valid email*\n4. **View → Freeze → 1 row** on every tab, and turn on a filter (**Data → Create a filter**) on Deals."},{"title":"Import your existing data","body":"Open your CRM export in a separate tab, then map it into Deals:\n\n- Paste each column under the matching header. Use `=PROPER(TRIM(B2))` style helpers to clean names, then *Paste special → Values only*.\n- Make sure **Stage** values match the Lists tab exactly. Find mismatches with `=FILTER(F2:F, ISNA(MATCH(F2:F, Lists!A2:A, 0)), F2:F<>\"\")`.\n- Convert follow-up dates to real dates (they should right-align). If they're text, use `=DATEVALUE()` or ask your AI assistant for a formula that handles your export's format.\n- Leave **Stage changed at** and **Created at** blank — the script fills them from now on. If you want history, paste the old CRM's stage-change dates there once.\n\nKeep the raw export in a hidden tab called `_import` for 90 days in case you need to check something."},{"title":"Install the Apps Script","body":"1. **Extensions → Apps Script**, delete the placeholder, paste the *Apps Script* artifact, and **Save**.\n2. **Project Settings** (gear icon) → set the **time zone** to yours. The digest runs at `DIGEST_HOUR` in that zone.\n3. Reload the spreadsheet. A **CRM** menu appears.\n4. Run **CRM → Set up (run once)** and approve the permissions Google asks for (Sheets, sending email as you, running triggers).\n5. Test: change a deal's Stage. Within a second or two **Stage changed at** fills in and a row appears in **Stage log**.\n6. Run **CRM → Send follow-up digest now** with one overdue test deal assigned to yourself.\n\nWhy two kinds of trigger: `onEdit` is a *simple trigger*, so it runs without authorisation but **cannot send email**, is limited to 30 seconds and **does not fire for edits made by scripts or the API**. The digest uses an *installable* time-driven trigger, which can send mail."},{"title":"Adapt it with an AI assistant instead of hand-coding","body":"When you want a change (an extra column, a weekly pipeline summary, a different stage list), paste the whole script plus your header row into Claude or ChatGPT with the **Modify the CRM script** prompt. Rules that keep this safe:\n\n- Always give the assistant your **exact header row** — most bugs are mismatched column names.\n- Ask for the **complete file** back, not a fragment, and keep the previous version — the simplest habit is pasting the old script into a dated Google Doc before replacing it.\n- Test on a **copy** of the spreadsheet (**File → Make a copy** copies the script too) before changing the live one.\n- If you get an error, paste the exact message from **Executions** (left sidebar in the Apps Script editor) using the **Debug an Apps Script error** prompt."},{"title":"Run both systems in parallel for one week, then cancel","body":"Keep the old CRM read-only for a week while the team works only in the sheet. Every day, check the digest against what the old CRM would have reminded you about. When the verification checklist passes, export a final full backup from the old CRM, store it in Drive, then downgrade or cancel. Put a monthly 10-minute calendar reminder to archive Won/Lost deals older than 12 months to an `Archive` tab so the sheet stays fast."}],"prompts":[{"title":"Modify the CRM script","purpose":"Safely change the Apps Script for a new column, rule or report.","text":"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.\n\nHeader row (tab-separated, exact spelling):\n{{header_row}}\n\nCurrent script:\n```javascript\n{{current_script}}\n```\n\nChange request: {{change_request}}\n\nRules:\n- Return the COMPLETE updated script in one code block, not a diff.\n- Find columns by header name, never by hard-coded column number.\n- Keep onEdit as a simple trigger: no MailApp, GmailApp or other services that need authorisation inside it.\n- Stay within Apps Script quotas (6 minutes per execution; MailApp recipient limits) and read/write ranges in bulk where possible.\n- 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.","variables":["sheet_name","header_row","current_script","change_request"]},{"title":"Debug an Apps Script error","purpose":"Turn an error from the Executions log into a fix.","text":"My Google Apps Script for a Sheets CRM fails. Explain the cause in plain English, then give the corrected function(s).\n\nFunction that failed: {{function_name}}\nTrigger type: {{trigger_type}}\nExact error message and line from Executions:\n{{error_message}}\n\nHeader row of the tab it reads:\n{{header_row}}\n\nRelevant code:\n```javascript\n{{code_snippet}}\n```\n\nIf 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.","variables":["function_name","trigger_type","error_message","header_row","code_snippet"]},{"title":"Weekly pipeline review from a CSV export","purpose":"Get a short pipeline review without a CRM reporting module.","text":"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):\n\n{{deals_csv}}\n\nToday is {{today}}. Our stages in order are: {{stage_order}}.\n\nProduce:\n1. Open pipeline value and deal count by stage (table).\n2. Deals with no Next follow-up date, or a follow-up more than 7 days overdue (table: Company, Owner, days overdue).\n3. Deals that have sat in the same stage for more than {{stale_days}} days, using Stage changed at.\n4. Won and Lost this week, with lost reasons.\n5. Three specific actions for the team, each naming a deal.\nUse only the data provided. If a value is missing or ambiguous, say so instead of guessing.","variables":["deals_csv","today","stage_order","stale_days"]}],"artifacts":[{"title":"Sheet schema (tabs, columns, validation)","kind":"schema","content":"SPREADSHEET: \"CRM\" (one Google Sheets file, shared with the team as Editors)\n\nTAB 1 — Deals  (one row per opportunity; row 1 = headers, exact names matter for the script)\n| Col | Header            | Type / validation                                                      |\n|-----|-------------------|------------------------------------------------------------------------|\n| A   | Deal ID           | Text. Formula in A2 (optional): =IF(B2=\"\",\"\",\"D-\"&TEXT(ROW()-1,\"0000\")) |\n| B   | Company           | Text. Data validation: dropdown from a range = Companies!A2:A           |\n| C   | Contact name      | Text                                                                   |\n| D   | Contact email     | Text. Data validation: \"Text is valid email\"                            |\n| E   | Owner             | Email of the teammate who owns the deal. Dropdown from Lists!C2:C       |\n| F   | Stage             | Dropdown from Lists!A2:A (reject invalid input)                         |\n| G   | Value             | Number, format Currency. Validation: number >= 0                        |\n| H   | Next step         | Text — a verb phrase: \"Send revised quote\", \"Call re: start date\"       |\n| I   | Next follow-up    | Date. Validation: \"is valid date\"                                       |\n| J   | Stage changed at  | Date-time. Filled by the script — do not type here                      |\n| K   | Created at        | Date-time. Filled by the script on first stage selection                |\n| L   | Source            | Dropdown from Lists!B2:B                                                |\n| M   | Lost reason       | Text (fill when Stage = Lost)                                           |\n| N   | Notes             | Text — short; long history goes in the Activities tab                   |\n\nTAB 2 — Companies\n| A Company | B Website | C Industry | D City | E Size (employees) | F Notes |\n\nTAB 3 — Activities  (append-only log; use a Google Form or type directly)\n| A Date | B Deal ID | C Type (Call / Email / Meeting / Note) | D Summary | E Logged by |\nTip: =FILTER(Activities!A:E, Activities!B:B = A2) in a side panel shows a deal's history.\n\nTAB 4 — Lists  (single source for dropdowns)\n| A Stage          | B Source        | C Owner              |\n|------------------|-----------------|----------------------|\n| Lead             | Referral        | you@yourfirm.com     |\n| Qualified        | Website form    | colleague@yourfirm.com |\n| Proposal sent    | Event           |                      |\n| Negotiation      | Outbound        |                      |\n| Won              | Existing client |                      |\n| Lost             |                 |                      |\nKeep \"Won\" and \"Lost\" spelled exactly like this — the script treats them as closed.\n\nTAB 5 — Follow-ups due   (created by the script; formula-driven, do not edit by hand)\nTAB 6 — Stage log        (created by the script; one row per stage change)\n\nPROTECTION: Data > Protect sheets and ranges → protect \"Follow-ups due\", \"Stage log\" and\nDeals!J:K with \"Show a warning when editing this range\" so nobody overwrites them by accident.\n"},{"title":"Apps Script: stage timestamps, follow-ups view, daily digest","kind":"script","language":"javascript","content":"/**\n * Small-business CRM helpers for Google Sheets.\n * Install: Extensions > Apps Script > paste this file > Save.\n * Reload the spreadsheet, then run CRM > \"Set up (run once)\" and approve the permissions.\n *\n * Adds:\n *  1. onEdit timestamp: when Stage changes, writes \"Stage changed at\" (and \"Created at\" if blank)\n *     and appends a row to the \"Stage log\" tab.\n *  2. A \"Follow-ups due\" tab (live FILTER formula) plus red/amber highlighting on Deals.\n *  3. A daily email digest of overdue / due-today follow-ups, one email per Owner, via MailApp.\n */\nconst CONFIG = {\n  DEALS_SHEET: 'Deals',\n  DUE_SHEET: 'Follow-ups due',\n  LOG_SHEET: 'Stage log',\n  CLOSED_STAGES: ['Won', 'Lost'],\n  DIGEST_HOUR: 7,        // hour of day in the script time zone (Project Settings)\n  FALLBACK_EMAIL: '',    // gets deals with an empty Owner; blank = the person who ran setup\n};\n\nconst COL = {\n  ID: 'Deal ID', COMPANY: 'Company', CONTACT: 'Contact name', EMAIL: 'Contact email',\n  OWNER: 'Owner', STAGE: 'Stage', VALUE: 'Value', NEXT_STEP: 'Next step',\n  FOLLOW_UP: 'Next follow-up', STAGE_CHANGED: 'Stage changed at', CREATED: 'Created at',\n};\n\nfunction onOpen() {\n  SpreadsheetApp.getUi()\n    .createMenu('CRM')\n    .addItem('Set up (run once)', 'setup')\n    .addItem('Rebuild follow-ups view', 'buildFollowUpsView')\n    .addItem('Send follow-up digest now', 'sendFollowUpDigest')\n    .addToUi();\n}\n\n/** Maps header text -> 1-based column number. Throws if a required header is missing. */\nfunction headerMap_(sheet) {\n  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];\n  const map = {};\n  headers.forEach(function (h, i) { if (h !== '') map[String(h).trim()] = i + 1; });\n  Object.keys(COL).forEach(function (k) {\n    if (!map[COL[k]]) throw new Error('Missing column \"' + COL[k] + '\" on tab ' + sheet.getName());\n  });\n  return map;\n}\n\nfunction colLetter_(n) {\n  let s = '';\n  while (n > 0) { const m = (n - 1) % 26; s = String.fromCharCode(65 + m) + s; n = Math.floor((n - 1) / 26); }\n  return s;\n}\n\n/* ---------- 1. Stage-change timestamps (simple trigger) ---------- */\nfunction onEdit(e) {\n  if (!e || !e.range) return;\n  const sheet = e.range.getSheet();\n  if (sheet.getName() !== CONFIG.DEALS_SHEET || e.range.getLastRow() < 2) return;\n  let cols;\n  try { cols = headerMap_(sheet); } catch (err) { e.source.toast(err.message, 'CRM script'); return; }\n  const stageCol = cols[COL.STAGE];\n  if (stageCol < e.range.getColumn() || stageCol > e.range.getLastColumn()) return;\n\n  const now = new Date();\n  const singleCell = e.range.getNumRows() === 1 && e.range.getNumColumns() === 1;\n  const editor = (e.user && e.user.getEmail && e.user.getEmail()) || '';\n  const log = e.source.getSheetByName(CONFIG.LOG_SHEET);\n  const firstRow = Math.max(2, e.range.getRow());\n\n  for (let r = firstRow; r <= e.range.getLastRow(); r++) {\n    const stage = sheet.getRange(r, stageCol).getValue();\n    sheet.getRange(r, cols[COL.STAGE_CHANGED]).setValue(now);\n    const created = sheet.getRange(r, cols[COL.CREATED]);\n    if (created.getValue() === '') created.setValue(now);\n    if (log) {\n      log.appendRow([\n        now,\n        sheet.getRange(r, cols[COL.ID]).getValue(),\n        sheet.getRange(r, cols[COL.COMPANY]).getValue(),\n        singleCell ? (e.oldValue || '') : '(bulk edit)',\n        stage,\n        editor,\n      ]);\n    }\n  }\n}\n\n/* ---------- 2. Follow-ups view + highlighting ---------- */\nfunction buildFollowUpsView() {\n  const ss = SpreadsheetApp.getActive();\n  const deals = ss.getSheetByName(CONFIG.DEALS_SHEET);\n  const cols = headerMap_(deals);\n  const lastCol = colLetter_(deals.getLastColumn());\n  const f = colLetter_(cols[COL.FOLLOW_UP]);\n  const s = colLetter_(cols[COL.STAGE]);\n  const src = \"'\" + CONFIG.DEALS_SHEET + \"'!\";\n\n  const conditions = [src + f + '2:' + f + '<>\"\"', src + f + '2:' + f + '<=TODAY()']\n    .concat(CONFIG.CLOSED_STAGES.map(function (st) { return src + s + '2:' + s + '<>\"' + st + '\"'; }));\n  const formula = '=IFERROR(SORT(FILTER(' + src + 'A2:' + lastCol + ', ' + conditions.join(', ') +\n    '), ' + cols[COL.FOLLOW_UP] + ', TRUE), \"Nothing due. Nice.\")';\n\n  const due = ss.getSheetByName(CONFIG.DUE_SHEET) || ss.insertSheet(CONFIG.DUE_SHEET);\n  due.clear();\n  const headers = deals.getRange(1, 1, 1, deals.getLastColumn()).getValues();\n  due.getRange(1, 1, 1, headers[0].length).setValues(headers).setFontWeight('bold');\n  due.getRange('A2').setFormula(formula);\n  due.setFrozenRows(1);\n}\n\nfunction addHighlighting_() {\n  const deals = SpreadsheetApp.getActive().getSheetByName(CONFIG.DEALS_SHEET);\n  const cols = headerMap_(deals);\n  const f = '$' + colLetter_(cols[COL.FOLLOW_UP]) + '2';\n  const s = '$' + colLetter_(cols[COL.STAGE]) + '2';\n  const open = CONFIG.CLOSED_STAGES.map(function (st) { return s + '<>\"' + st + '\"'; }).join(',');\n  const overdue = '=AND(' + f + '<>\"\",' + f + '<TODAY(),' + open + ')';\n  const today = '=AND(' + f + '<>\"\",' + f + '=TODAY(),' + open + ')';\n  const range = deals.getRange(2, 1, deals.getMaxRows() - 1, deals.getLastColumn());\n\n  const rules = deals.getConditionalFormatRules().filter(function (r) {\n    const c = r.getBooleanCondition();\n    const v = c && c.getCriteriaValues()[0];\n    return v !== overdue && v !== today; // drop our old copies so re-running setup doesn't duplicate\n  });\n  rules.push(SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied(overdue)\n    .setBackground('#f4c7c3').setRanges([range]).build());\n  rules.push(SpreadsheetApp.newConditionalFormatRule().whenFormulaSatisfied(today)\n    .setBackground('#fce8b2').setRanges([range]).build());\n  deals.setConditionalFormatRules(rules);\n}\n\n/* ---------- 3. Daily digest (installable time-driven trigger) ---------- */\nfunction sendFollowUpDigest() {\n  const ss = SpreadsheetApp.getActive();\n  const deals = ss.getSheetByName(CONFIG.DEALS_SHEET);\n  const cols = headerMap_(deals);\n  const values = deals.getDataRange().getValues();\n  const fallback = CONFIG.FALLBACK_EMAIL || Session.getEffectiveUser().getEmail();\n\n  const startOfToday = new Date(); startOfToday.setHours(0, 0, 0, 0);\n  const startOfTomorrow = new Date(startOfToday.getTime() + 86400000);\n  const byOwner = {};\n\n  for (let i = 1; i < values.length; i++) {\n    const row = values[i];\n    const due = row[cols[COL.FOLLOW_UP] - 1];\n    const stage = String(row[cols[COL.STAGE] - 1]);\n    if (!(due instanceof Date) || CONFIG.CLOSED_STAGES.indexOf(stage) !== -1) continue;\n    if (due.getTime() >= startOfTomorrow.getTime()) continue;\n    const dueDay = new Date(due); dueDay.setHours(0, 0, 0, 0);\n    const owner = String(row[cols[COL.OWNER] - 1] || fallback).trim();\n    (byOwner[owner] = byOwner[owner] || []).push({\n      company: row[cols[COL.COMPANY] - 1], contact: row[cols[COL.CONTACT] - 1],\n      stage: stage, next: row[cols[COL.NEXT_STEP] - 1], value: row[cols[COL.VALUE] - 1],\n      daysLate: Math.round((startOfToday - dueDay) / 86400000), row: i + 1,\n    });\n  }\n\n  const url = ss.getUrl() + '#gid=' + deals.getSheetId();\n  const tz = ss.getSpreadsheetTimeZone();\n  Object.keys(byOwner).forEach(function (owner) {\n    if (MailApp.getRemainingDailyQuota() < 1) { console.warn('MailApp daily quota used up'); return; }\n    const items = byOwner[owner].sort(function (a, b) { return b.daysLate - a.daysLate; });\n    const rowsHtml = items.map(function (d) {\n      return '<tr><td>' + esc_(d.company) + '</td><td>' + esc_(d.contact) + '</td><td>' + esc_(d.stage) +\n        '</td><td>' + esc_(d.next) + '</td><td style=\"text-align:right\">' + esc_(d.value) + '</td><td>' +\n        (d.daysLate === 0 ? 'today' : d.daysLate + ' day(s) late') + '</td><td>row ' + d.row + '</td></tr>';\n    }).join('');\n    const html = '<p>' + items.length + ' follow-up(s) due as of ' +\n      Utilities.formatDate(startOfToday, tz, 'EEE d MMM yyyy') + ':</p>' +\n      '<table border=\"1\" cellpadding=\"6\" style=\"border-collapse:collapse;font-family:sans-serif;font-size:13px\">' +\n      '<tr><th>Company</th><th>Contact</th><th>Stage</th><th>Next step</th><th>Value</th><th>Due</th><th>Where</th></tr>' +\n      rowsHtml + '</table><p><a href=\"' + url + '\">Open the CRM</a></p>';\n    const text = items.map(function (d) {\n      return '- ' + d.company + ' (' + d.stage + '): ' + d.next + ' [' + (d.daysLate === 0 ? 'today' : d.daysLate + 'd late') + ']';\n    }).join('\\n') + '\\n\\n' + url;\n    MailApp.sendEmail({ to: owner, subject: 'CRM: ' + items.length + ' follow-up(s) due', body: text, htmlBody: html });\n  });\n}\n\nfunction esc_(v) {\n  return String(v === null || v === undefined ? '' : v)\n    .replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;').replace(/\"/g, '&quot;');\n}\n\n/* ---------- One-time setup ---------- */\nfunction setup() {\n  const ss = SpreadsheetApp.getActive();\n  if (!ss.getSheetByName(CONFIG.LOG_SHEET)) {\n    ss.insertSheet(CONFIG.LOG_SHEET)\n      .appendRow(['Changed at', 'Deal ID', 'Company', 'From stage', 'To stage', 'Changed by']);\n  }\n  buildFollowUpsView();\n  addHighlighting_();\n  ScriptApp.getProjectTriggers().forEach(function (t) {\n    if (t.getHandlerFunction() === 'sendFollowUpDigest') ScriptApp.deleteTrigger(t);\n  });\n  ScriptApp.newTrigger('sendFollowUpDigest').timeBased().everyDays(1).atHour(CONFIG.DIGEST_HOUR).create();\n  ss.toast('CRM set up: follow-ups view, highlighting and daily digest at ~' + CONFIG.DIGEST_HOUR + ':00.', 'CRM', 8);\n}\n"}],"limitations":["No email or calendar sync: emails and meetings are not logged automatically; people must add a line to Activities or the Notes column.","No sequences, open/click tracking or meeting-booking links — those need a CRM or a separate tool.","Simple onEdit triggers do not fire for changes made by scripts, the Sheets API, Zapier-style integrations or form submissions, so those rows get no stage timestamp.","Shared-sheet permissions are all-or-nothing per tab/range: anyone with edit access can see and change every deal.","MailApp limits: 100 email recipients/day on a free Google account and 1,500/day on Google Workspace; triggers share a daily runtime quota (90 min consumer, 6 h Workspace).","Reporting is whatever you build: pivot tables, charts or a Looker Studio dashboard — there's no forecasting engine.","The digest's date logic uses the script's time zone; teammates in other time zones receive it at that zone's hour."],"risks":[{"type":"functionality_loss","severity":"medium","note":"You lose automatic activity capture, mobile CRM apps, email tracking and built-in reports. Follow-up discipline depends on people updating the sheet."},{"type":"reliability","severity":"medium","note":"If someone renames a header, the script stops (it shows a toast and the Executions log records the error). The digest silently stops if the trigger owner leaves the company or their account is suspended — reinstall the trigger from a shared admin account."},{"type":"security","severity":"medium","note":"Customer contact details sit in a spreadsheet that can be shared or downloaded with a click. Restrict sharing to named people, disable 'Viewers and commenters can download, print and copy', and never share by link."},{"type":"compliance","severity":"low","note":"Sheets version history is not a tamper-proof audit log, and deletion/retention requests (e.g. GDPR erasure) must be handled manually across tabs, the Stage log and version history."},{"type":"migration_complexity","severity":"low","note":"Importing from a CRM is mostly copy-paste, but activity history and attachments rarely export cleanly. Keep the final CRM export archived."}],"verificationChecklist":["All open deals from the old CRM are in Deals: row count matches the old CRM's open-deal count, and total open Value matches within rounding.","Changing a Stage in the sheet fills 'Stage changed at' within a few seconds and appends a row to 'Stage log' showing the old and new stage.","A test deal with yesterday's follow-up date turns red, one dated today turns amber, and both appear on the 'Follow-ups due' tab; a Won deal with an old date does not appear.","'CRM → Send follow-up digest now' delivers an email to each owner listing only their overdue/due-today deals, with a working link to the sheet.","The time-driven trigger shows in Apps Script → Triggers, and the digest arrived unprompted on at least 3 consecutive mornings.","Every teammate has logged at least one deal and one activity without help, and nobody needed the old CRM during a full week of parallel running.","A complete CSV export of the old CRM (contacts, companies, deals, notes) is saved in a restricted Drive folder before cancelling."],"savingsMethodology":"Savings = (CRM price per seat per month × number of paid seats) − incremental cost of the replacement. The replacement's incremental cost is $0 because Sheets, Apps Script and MailApp come with Google accounts you already have, and an AI assistant you already pay for. We do not count setup time (≈half a day) or maintenance (≈15 min/month) as dollars; if your time is billable, subtract it. If your current plan is billed annually, savings only start at renewal unless the vendor refunds unused time.","example":{"scenario":"A 3-person design studio on a paid CRM plan moves its pipeline to Google Sheets.","currentMonthlyUSD":120,"newMonthlyUSD":0,"assumptions":["ILLUSTRATIVE: 3 paid CRM seats at an assumed $40 per seat per month. We could not verify current Pipedrive or HubSpot paid-plan prices on the vendors' own pages today (Pipedrive's pricing page blocked automated access; HubSpot's pricing page did not show prices) — use the amount on your own invoice.","The studio already pays for Google Workspace and an AI assistant, so nothing new is bought.","HubSpot's free CRM (up to 2 users, 1,000 contacts — verified on HubSpot's product page) would also cost $0 but doesn't fit 3 users."]},"evidence":{"status":"editorial","sources":[{"url":"https://www.hubspot.com/products/crm","title":"HubSpot free CRM product page","accessedAt":"2026-10-13","note":"Free with no expiration; up to two users and 1,000 contacts; includes deal pipelines and email tracking."},{"url":"https://developers.google.com/apps-script/guides/triggers","title":"Apps Script simple triggers — restrictions","accessedAt":"2026-10-13","note":"Simple triggers can't use services requiring authorisation (e.g. sending email), 30-second limit, not fired by script/API edits."},{"url":"https://developers.google.com/apps-script/guides/services/quotas","title":"Apps Script quotas","accessedAt":"2026-10-13","note":"Email recipients/day: 100 consumer, 1,500 Workspace; trigger runtime 90 min/day consumer, 6 h/day Workspace; 6 min per execution."},{"url":"https://www.pipedrive.com/en/pricing","title":"Pipedrive pricing","accessedAt":"2026-10-13","note":"Returned HTTP 403 to our fetch; prices not verified."},{"url":"https://www.hubspot.com/pricing/sales","title":"HubSpot Sales Hub pricing","accessedAt":"2026-10-13","note":"Page loaded without visible prices; paid-plan prices not verified."}],"verifiedBy":"agent:claude-research","notes":"Script written for this workflow and reviewed for Apps Script trigger/quotas rules as documented today; test on a copy before using on live data. Paid CRM prices in the example are illustrative."}},"meta":{"apiVersion":"v1","dataVersion":"4f1b9f55d2a1","dataGeneratedAt":"2026-10-13T14:29:58.201Z","docs":"/docs/api","evidenceLegend":"/docs/data-model#evidence","disclaimer":"Prices and capabilities change. Check each item's evidence status and verification date. Savings are estimates, not guarantees."}}