Automating Weekly Reporting with Claude and Google Sheets

A weekly report nobody trusts is worse than no report at all. Every team has the same Monday ritual: open five dashboards, copy the numbers into a slide, ship it. It's the most automatable task in the building — and the one people are most afraid to automate. Because the moment a figure looks wrong, "the script computed it" loses the room, and everyone quietly goes back to copying by hand.

The instinct is to make the script smarter. That's the wrong move. The thing that makes an automated report survive contact with a skeptical meeting isn't cleverness — it's ordering. Get the order of operations right and every number on the page traces back to a row you can point at. Get it wrong and you've built a faster way to publish figures nobody can defend.

The ordering that matters

Three rules, in this exact sequence:

• Write every raw number to a Google Sheet first, with a timestamp. That row is your system of record. Nothing else happens until it exists.
• Only then forward downstream — to a dashboard, an analytics endpoint, an email — and write the response code back into the same row.
• Let the model write the narrative last, from the rows you can already see. Never from a live API.

Each rule exists because of a specific failure I've watched happen. Skip the first and a downstream hiccup erases the record silently. Skip the second and you can't tell a delivered number from a dropped one. Skip the third and you hand a language model live, ambiguous data and let it invent the parts it can't reach.

How it's actually built

The spine is boring on purpose: Google Sheets as the store, Google Apps Script as the scheduler and glue, Claude for the prose at the end. No new infrastructure, no server to babysit, and it runs inside Google's free quotas for anything short of enterprise volume.

A time-driven Apps Script trigger fires once a week. For each source, it appends a timestamped row to a raw tab before it forwards anything. This is the whole trick — log before send — and it's four lines:

function logThenSend(sheet, source, value, sendFn) {
  // 1) system of record FIRST — a timestamped row that always exists
  const row = [new Date(), source, value, "", ""];
  sheet.appendRow(row);
  const r = sheet.getLastRow();

  // 2) now forward downstream, and write the result back into the row
  const res = sendFn(value);           // e.g. GA4 Measurement Protocol
  sheet.getRange(r, 4).setValue(res.getResponseCode());
  sheet.getRange(r, 5).setValue(res.getResponseCode() === 204 ? "OK" : "CHECK");
}

Notice the number in that last line. The GA4 Measurement Protocol returns 204, not 200, on a successful send. Assert on 200 and every success reads as a failure — you'll spend an afternoon chasing a bug that was never there. It's a small thing that has cost more debugging hours than any hard problem in the pipeline, so it earns its own guardrail column in the sheet.

Once the raw tab is populated and every row carries its delivery status, the report writes itself — literally. Claude reads the sheet, not the live sources, and is asked for one thing: a plain-English summary of the rows in front of it. "Sessions up 12% week over week, driven by the two Field Notes published Tuesday; one GA4 event shows CHECK — investigate before trusting the conversions line." The model narrates; it never computes and never fetches. Every figure it mentions is already sitting in a cell with a timestamp next to it.

Why the model goes last

Language models are excellent at turning a table into a paragraph and terrible at being a source of truth. Give one a live API and a vague question and it will helpfully fill the gaps it can't actually see. Give it a frozen, timestamped table and ask only for prose, and the failure mode disappears — there's nothing left to hallucinate, because the numbers were decided before the model ever ran.

This is also what makes the report defensible. When someone asks where a number came from, you don't re-run anything or trust a black box. You open the sheet, find the row by its timestamp, and read the response code that proves it was delivered. A questioned figure takes seconds to settle instead of an afternoon.

Costs and failure modes

The honest accounting. Building this is a few hours of work, not a project — the hard part is resisting the urge to add features. It runs on free Apps Script triggers and a few cents of Claude API per weekly run. The ongoing cost is near zero; the ongoing value is that the report keeps its credibility.

The failure modes are all failures of discipline, not technology:

• Forwarding before logging. The most tempting shortcut and the most expensive. A downstream 500 error now loses the record with no trace. Log first, always.
• Asserting on the wrong status code. The 204-vs-200 trap. Your monitoring lies to you and you learn to ignore it — which is worse than having none.
• Letting the model touch live data. The moment the narrative step reads an API instead of the sheet, you've reintroduced the exact untrustworthiness you built the pipeline to remove.

One honest limit: this makes a report auditable, not correct. If the source feeding the sheet is wrong, the number is still wrong — the audit trail only guarantees you can find where it came from, fast. That's a smaller promise than "accurate," and it's the one that actually survives a meeting.

If your team's weekly report is a manual copy-paste ritual that nobody fully trusts, that distrust is the real problem — and it's exactly the shape this pattern fixes. The numbers were never the hard part.