Weekly KPI Digest from Google Sheets with Source-Cited Numbers
An agent reads your defined metrics from Google Sheets, compares them to last week and targets, and drafts a plain-English digest where every number cites its cell. The owner approves before it goes to Slack or email.
The problem
Most small businesses already track their numbers in a spreadsheet, but turning that sheet into a weekly update people actually read is a chore. Someone has to open several tabs, copy figures, work out the week-over-week change, notice what looks off, and write a summary. When that person is busy, the update slips or becomes a pasted screenshot nobody interprets. There is also a real risk in letting an AI write the summary: language models can produce confident numbers that are not in your data. This recipe solves both problems. The agent only works from a defined set of cells, every figure in the digest carries its source range, anomalies are flagged against rules you set, and nothing is shared with the team until the owner has checked and approved the draft.
Who it's for
- Owners and managers who already maintain weekly numbers in Google Sheets
- Teams of 3 to 30 who want a consistent Monday update without building a BI dashboard
- Businesses with clear targets for a handful of metrics such as revenue, orders, leads, or fulfillment time
- Anyone who has been burned by numbers in a report that did not match the source
Not a fit when
- Businesses whose data is not yet in a consistent sheet; fix the data pipeline first
- Financial reporting for investors, lenders, or tax purposes, which needs accountant review and formal controls
- Real-time monitoring needs; a weekly digest is too slow for alerting on outages or fraud
Tools
| Google Sheets | Source of truth for metrics, targets, and the prior-week snapshot; free with a Google account |
| Google Apps Script or Zapier/Make | Scheduled job that reads defined ranges and sends them to the agent |
| Claude or another LLM | Writes the plain-English summary using only the supplied values |
| Validation script (Apps Script or a small Python check) | Confirms every number in the draft matches a supplied cell value |
| Gmail or Slack DM | Delivers the draft privately to the owner for approval |
| Slack channel or email list | Final destination for the approved digest |
Inputs you need first
- A Google Sheet with one row per metric per week, or a clear weekly summary tab
- A metric definitions table: name, cell or range, unit, owner, target, and which direction is good
- Anomaly rules you are comfortable with, for example flag any change over 20 percent week over week
- Edit access to the sheet for an automation account, ideally read-only on data tabs
- The Slack channel or email list that should receive the final digest
- The name of the person who approves the digest each week
Step-by-step build
Define the metric register
Create a tab called Metrics with columns: metric_name, sheet_tab, cell_range, unit, target, good_direction (up or down), owner, notes. Limit the first version to 5 to 10 metrics that matter, such as weekly revenue, orders, average order value, new leads, support backlog, and on-time shipping. Each metric must point to one specific cell or range. If a metric needs a calculation, do it in the sheet with a formula so the agent reads a finished number instead of computing it.
Create a weekly snapshot tab
Add a Snapshot tab where a scheduled script copies this week's values as static numbers with a date stamp, keeping prior weeks below. Comparing against the snapshot prevents surprises when someone edits historical data. Columns: week_ending, metric_name, value, source_range. The prior-week value and the week-over-week change are then simple formulas in the sheet, so the arithmetic is done by Sheets, not by the language model.
Set anomaly rules
In the Metrics tab, add columns for change_threshold and target_gap_threshold. Example rules you adjust: flag if the week-over-week change exceeds 20 percent in either direction, flag if a metric is more than 10 percent below target, and flag if any value is blank or zero when it is normally positive. Compute a flag column in the sheet with formulas. The agent describes flags; it does not decide what counts as an anomaly.
Build the data extract
Write a short Apps Script or a Zapier/Make step that runs Monday morning, reads the Metrics and Snapshot rows for the latest week, and produces a compact table: metric, this_week, last_week, change, target, flag, source_range. Send only this table to the agent, not the whole spreadsheet. Fewer inputs mean fewer opportunities for confusion and lower model cost.
Write the digest prompt
Use the prompt below. Require a fixed structure: a three-sentence headline summary, a section per flagged metric, a short 'on track' list, and a 'questions for the owner' section. Every number must appear exactly as supplied and be followed by its source range in brackets, for example 'Orders were 412 [Snapshot!C14]'. The agent may describe direction and size but may not speculate about causes unless a notes field supplies one.
Add an automatic number check
Before the draft reaches the owner, run a validation step that extracts every number from the draft and confirms it appears in the supplied table, and that each has a source range. If any number fails, mark the draft 'VALIDATION FAILED' at the top and list the unmatched figures. This is a simple text match, easy to build in Apps Script, and it catches the most dangerous failure: invented figures.
Route the draft for approval
Send the validated draft to the owner by Gmail or Slack DM with two links: the source sheet and an approval form or reply keyword. The owner checks the headline numbers against the sheet, edits wording, adds context the data cannot show, and approves. Record approver, time, and any edits in a Digest Log tab.
Test with past weeks
Run the workflow on four to six past weeks of data before going live, sending drafts only to yourself. For each, check that every number matches the sheet, flags match your rules, and the summary does not imply causes it was not given. Deliberately blank out a cell and change a value by 50 percent to confirm the flags and validation work.
Go live and publish
Once the owner approves, the workflow posts the digest to the team Slack channel or sends it to the email list, with a link to the sheet. Keep the source ranges in the published version so anyone can verify a figure. If the owner has not approved by a set time, the digest simply waits; it never publishes by default.
Maintain monthly
Once a month, review the Metrics tab: retire metrics nobody discusses, adjust thresholds that flag too often or never, and confirm cell ranges still point to the right place after any sheet restructuring. Broken ranges are the most common failure, so the validation step should also alert you when a source cell is empty or contains text instead of a number.
Example agent instructions
Adapt this to your business. Placeholders in braces are filled by your automation tool.
You write the weekly KPI digest for {business_name}. Use only the data table provided below. Never introduce a number, percentage, or date that is not in the table. Every number you write must be copied exactly from the table and followed by its source range in square brackets, for example 412 [Snapshot!C14]. Do not round or recalculate. Structure: 1) Headline: three sentences on the most important movements. 2) Flagged metrics: for each row where flag is not blank, state this week, last week, change, and target with sources, then describe the flag in plain words. 3) On track: one line per unflagged metric. 4) Questions for the owner: list anything that looks unusual but where the table gives no explanation. Do not guess at causes; if a notes field explains a change, you may repeat it and attribute it to the notes. Use a calm, factual tone with no hype. If any value is missing or not a number, say so under Questions rather than filling a value. Week ending: {week_ending}. Data table: {metrics_table}.
Human sign-off points
- Owner approves the metric register, targets, and anomaly thresholds before the first run
- Owner reviews and approves each weekly draft before it is posted to Slack or emailed
- Any draft marked VALIDATION FAILED must be corrected and re-approved by the owner; it cannot be published as is
- Metric owners confirm any change to a cell range or definition for their metric
Guardrails
- No invented numbers: every figure must match a supplied cell value, checked by an automated validation step
- Every figure in the digest includes its source cell or range so readers can verify it
- All arithmetic such as change and target gap is computed by sheet formulas, not by the model
- The agent receives only the compact metrics table, never the full spreadsheet or unrelated tabs
- The digest never publishes without explicit owner approval; missed approval means it waits
- The agent may not speculate about causes unless supplied in a notes field, and must attribute them
- The automation account has read-only access to data tabs and write access only to the log tab
Expected time saved
Estimate: if assembling and writing the weekly update currently takes about 60 to 90 minutes, and reviewing an agent draft with cited sources takes 15 to 20 minutes, you may save roughly an hour a week. Initial setup of the metric register and validation step might take a few hours. Results depend on how tidy your sheet is and how many metrics you track.
How to tell it's working
- Number of validation failures per month, which should trend toward zero
- Owner review time per draft
- Whether the digest went out on schedule each week
- Team engagement, such as questions or replies prompted by flagged items
FAQ
Why not let the AI read the whole spreadsheet and figure it out?
The more data a model sees, the more chances it has to pick the wrong cell or combine numbers incorrectly. Defining exact ranges and doing the math in the sheet keeps the agent's job to writing, which it does well, while the numbers stay deterministic. It also makes the validation check simple and reliable.
Can this work with Excel or Airtable instead of Google Sheets?
Yes, the pattern is the same: a register of metrics pointing to exact locations, a weekly snapshot, formula-based comparisons, and a validation check. The connection method changes. Airtable and Excel online both work with Zapier or Make, and you would reference field names or table cells instead of Sheets ranges in the source citations.
What if the owner is away on Monday?
Name a backup approver in the Metrics tab and route the draft to them if the owner has not responded by a set time. The rule that someone must approve before publishing stays in place. A late digest is far less harmful than one with unchecked numbers going to the whole team.
Want this set up for you?
Build it yourself with the steps above, or tell us what you need. We scope the work with you and send a written quote before anything starts.
Sources
Written by Agents Lookup. Official pages for the tools this recipe uses:
- Zapier MCP (official page): zapier.com/mcp (checked 2026-11-09)
- Make MCP server (official page): developers.make.com/mcp-server (checked 2026-11-09)
- Claude (official page): claude.ai (checked 2026-11-09)
- Gmail (official page): developers.google.com/workspace/gmail (checked 2026-11-19)