Generate a weekly report Doc from Sheet data

Every Friday someone at Northwind copies last week’s report Doc, swaps in the new date, and retypes the numbers off a metrics Sheet. It is ten minutes of careful copy-paste, and the careful part is the problem — a mistyped figure or a stale week heading slips through more often than anyone admits.

This script removes the copy-paste entirely. It duplicates a report template, fills in this week’s date, reads the metrics Sheet, and writes each metric as a line with an up, down, or flat arrow. Schedule it for Friday afternoon and the week’s report Doc is waiting before anyone asks for it.

What you’ll need

• A Metrics sheet with a header row and three columns: metric, value, weekOverWeek (a percentage as a plain number — 12 for +12%, -4 for -4%).
• A report template Doc containing two placeholders, {{week}} and {{metrics}}, wherever you want the date and the metric lines to appear.
• A Drive folder where the finished reports are saved.
• The IDs of all three — fill them into the config block at the top of the script.

The script

// The report template Doc, copied fresh for every report.
const TEMPLATE = '1abcReportTemplateId';

// The Metrics sheet the report is built from.
const METRICS = '1abcMetricsId';

// The Drive folder finished reports are saved into.
const REPORTS_FOLDER = '1abcReportsFolderId';

/**
 * Copies the report template, fills in this week's date and metric
 * lines, and saves the finished Doc to the reports folder.
 */
function generateWeeklyReport() {
  // 1. Today's date, formatted as the week label.
  const week = Utilities.formatDate(new Date(), 'GMT', 'yyyy-MM-dd');

  // 2. Read the metrics first — if there are none, skip the Doc entirely.
  const metrics = readSheet(METRICS);
  if (!metrics.length) {
    Logger.log('No metrics found — skipping this week\'s report.');
    return;
  }

  // 3. Copy the template into the reports folder under a dated name.
  const copy = DriveApp.getFileById(TEMPLATE).makeCopy(
    `Weekly report — ${week}`,
    DriveApp.getFolderById(REPORTS_FOLDER));
  const doc = DocumentApp.openById(copy.getId());
  const body = doc.getBody();

  // 4. Fill in the week placeholder.
  body.replaceText('{{week}}', week);

  // 5. Turn each metric into a line with a direction arrow.
  const lines = metrics.map((m) => {
    const arrow = m.weekOverWeek > 0 ? '▲'
      : m.weekOverWeek < 0 ? '▼'
      : '–';
    return `${m.metric}: ${m.value} (${arrow} ${Math.abs(m.weekOverWeek)}%)`;
  });

  // 6. Drop the metric lines into the Doc and save.
  body.replaceText('{{metrics}}', lines.join('\n'));
  doc.saveAndClose();
  Logger.log('Wrote report for week ' + week);
}

/**
 * Reads the first tab of a spreadsheet and returns its rows as objects,
 * using the header row as keys.
 */
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]])));
}

How it works

1. generateWeeklyReport formats today’s date as a yyyy-MM-dd string — the label used both in the Doc and in its filename.
2. It reads the Metrics sheet with readSheet, which returns each row as an object keyed by the header. If the sheet is empty, it logs a message and stops before creating an empty Doc.
3. It copies the template Doc into the reports folder, naming the copy Weekly report — <date>, and opens that copy for editing.
4. It replaces the {{week}} placeholder with the date.
5. It maps every metric to a line of text, choosing ▲ when weekOverWeek is positive, ▼ when negative, and – when flat. Math.abs keeps the displayed percentage unsigned because the arrow already shows direction.
6. It joins the lines with newlines, replaces the {{metrics}} placeholder with them, and saves the Doc.

Example run

Suppose the Metrics sheet holds:

metric	value	weekOverWeek
New signups	142	12
Churned accounts	9	-4
Support tickets	56	0

And the template Doc reads:

Northwind weekly report — {{week}}

{{metrics}}

After a run, the new Doc in the reports folder reads:

Northwind weekly report — 2025-10-05

New signups: 142 (▲ 12%) Churned accounts: 9 (▼ 4%) Support tickets: 56 (– 0%)

One dated Doc per week, with the numbers lifted straight from the Sheet and the direction shown at a glance.

Trigger it

This is a scheduled job — set it to run every Friday afternoon:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add trigger.
3. Choose the generateWeeklyReport function, a Time-driven source, a Week timer, Friday, and the 4pm–5pm slot.
4. Save and approve the authorisation prompt.

Watch out for

• replaceText only swaps text it can find. If a placeholder is split across styled runs in the template — say half of {{metrics}} is bold — the match fails silently. Type each placeholder in one go, in one style.
• The metric lines go in as one block of text joined by newlines. They inherit whatever paragraph style sits on the {{metrics}} line in the template, so style that line the way you want every metric to look.
• weekOverWeek must be a number, not text. A value like "12%" makes the > 0 comparison unreliable and prints the percent sign twice. Store plain numbers and let the script add the %.
• The script copies the template every run, so a long-lived schedule fills the reports folder over time. Add a cleanup step or archive old reports if the folder grows unwieldy.
• The date uses the GMT timezone. If Northwind runs the trigger close to midnight in another timezone, the week label may read a day off — set the format’s timezone to the team’s own.