Build an AI weekly-report narrator

Northwind tracks its weekly numbers in a spreadsheet, and the spreadsheet is correct, current, and almost never read. The partners want the story — what moved, why it matters, what to watch — and a grid of figures does not tell a story. Someone usually ends up writing that paragraph by hand on a Friday.

This script does the writing. It reads the last seven rows of the metrics sheet, hands them to Claude with a brief to write a short executive summary that leads with the most important shift, and emails the result to the partners. The numbers stay in the sheet; what lands in the inbox is prose.

What you’ll need

• A Google Sheet of weekly metrics, one row per period, with a header row so each column has a name (revenue, bookings, churn, and so on).
• An Anthropic API key saved as ANTHROPIC_API_KEY in Script Properties — see Store API keys and secrets securely.
• A recipient address for the summary email.

The script

// The spreadsheet that holds the weekly metrics.
const METRICS_SHEET_ID = '1abcMetricsId';

// Where the written summary is sent.
const PARTNERS_EMAIL = '[email protected]';

// How many recent rows of metrics to feed into the summary.
const WEEKS_OF_CONTEXT = 7;

/**
 * Reads recent Northwind metrics, asks Claude to write an executive
 * summary, and emails it to the partners.
 */
function narrateWeeklyMetrics() {
  // 1. Read the metrics sheet and keep only the most recent rows.
  const metrics = readSheet(METRICS_SHEET_ID).slice(-WEEKS_OF_CONTEXT);

  if (!metrics.length) {
    Logger.log('No metrics to summarise — nothing to do.');
    return;
  }

  // 2. Brief Claude: lead with the biggest shift, keep it plain.
  const prompt =
    'Write a 200-word executive summary for the Northwind partners from ' +
    'these weekly metrics. Lead with the most important shift. Avoid jargon.' +
    '\n\n' + JSON.stringify(metrics);

  // 3. Sonnet does the reasoning — it has to spot which number matters.
  const narrative = callClaude(prompt, 'claude-sonnet-4-6', 600);

  // 4. Email the finished summary to the partners.
  GmailApp.sendEmail(PARTNERS_EMAIL, 'Weekly summary', narrative);
  Logger.log('Weekly summary sent to ' + PARTNERS_EMAIL + '.');
}

/**
 * Reads a sheet into an array of plain objects, keyed by the header row.
 * @param {string} id The spreadsheet ID to read.
 * @return {Object[]} One object per data row.
 */
function readSheet(id) {
  const [h, ...rows] = SpreadsheetApp.openById(id)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((r) => Object.fromEntries(h.map((k, i) => [k, r[i]])));
}

/**
 * Minimal Anthropic API call. The key lives in Script Properties — it
 * is never pasted into the code.
 */
function callClaude(prompt, model, maxTokens) {
  const key = PropertiesService.getScriptProperties()
    .getProperty('ANTHROPIC_API_KEY');
  const res = UrlFetchApp.fetch('https://api.anthropic.com/v1/messages', {
    method: 'post',
    contentType: 'application/json',
    headers: { 'x-api-key': key, 'anthropic-version': '2023-06-01' },
    payload: JSON.stringify({
      model,
      max_tokens: maxTokens,
      messages: [{ role: 'user', content: prompt }],
    }),
    muteHttpExceptions: true,
  });
  return JSON.parse(res.getContentText()).content[0].text.trim();
}

How it works

1. narrateWeeklyMetrics calls readSheet to load the metrics spreadsheet, then keeps only the last WEEKS_OF_CONTEXT rows — enough history for Claude to see a trend without flooding the prompt.
2. If there are no rows, it logs a message and stops — no wasted API call.
3. It builds a prompt that hands Claude the recent metrics as JSON and asks for a 200-word summary that leads with the most important shift.
4. It calls Claude Sonnet, which is worth the cost here because deciding which number matters is a judgement call, not a lookup.
5. It emails the finished narrative to the partners with GmailApp.sendEmail.
6. readSheet turns each data row into a named object, so the metrics arrive at Claude as readable key-value pairs rather than a bare grid.

Example run

Say the last two rows of the metrics sheet look like this:

week	revenue	bookings	churn
2026-05-11	18,400	22	3
2026-05-18	24,900	31	1

The email that lands in the partners’ inbox reads something like:

Revenue jumped 35% week on week to £24,900, the strongest week of the quarter, driven by nine extra bookings. Churn fell to a single account, down from three the week before. The booking surge is worth a closer look — if it holds, capacity planning needs revisiting before June…

The partners read three sentences instead of decoding a grid.

Trigger it

This is a weekly job, so let a time-driven trigger run it:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose narrateWeeklyMetrics, event source Time-driven, type Week timer, and pick a day and hour — first thing Monday works well.
4. Save. Approve the authorisation prompt the first time it runs.

Watch out for

• The summary is only as good as the metrics. If a row has a data-entry slip, Claude will narrate the slip with full confidence — sanity-check the sheet before the trigger fires.
• Claude decides which shift leads. Most weeks that matches your instinct; if it consistently fixates on the wrong metric, name your priority metrics in the prompt.
• The email is sent the moment the script runs, with no review step. If you want a human to glance at it first, write the draft to a Doc instead and send it manually.
• GmailApp.sendEmail counts against your daily quota — fine for one weekly message, but worth knowing if you extend this to many recipients.