Email a weekly "what changed" report from a Sheet

A shared sheet is a living document — rows added, rows closed, statuses nudged along — and that constant motion is exactly what makes it hard to follow. Open the Projects sheet on a Friday and you see the current state, but not the story: what is new, what dropped off, what moved. The week’s changes are invisible because nothing keeps the previous version to compare against.

Northwind’s Projects sheet changes daily, and the team wants a Friday email that answers one question: what changed this week? This script keeps that question answerable. Each week it saves a snapshot of the sheet to Drive, then compares the current state against last week’s snapshot and emails the team a plain diff — rows added, rows removed, and statuses that moved. The first run has nothing to compare against, so it just stores the baseline and tells the team the diff starts next week.

What you’ll need

• A Google Sheet with a Projects tab. Row 1 must hold headers; the script relies on columns named client, project, and status.
• A Drive folder to hold the weekly JSON snapshots, and its folder ID from the URL.
• The team email address that should receive the report.
• The spreadsheet’s ID, from its URL. All three IDs go in the config below.

The script

// The spreadsheet that holds the Projects tab.
const PROJECTS_SHEET_ID = '1abcProjectsSheetId';

// The Drive folder where weekly JSON snapshots are stored.
const SNAPSHOT_FOLDER_ID = '1abcSnapshotsFolderId';

// Who receives the weekly diff email.
const TEAM = '[email protected]';

// Script Property key that points at the most recent snapshot file.
const LAST_SNAPSHOT_KEY = 'LAST_SNAPSHOT_ID';

/**
 * Snapshots the Projects sheet, diffs it against last week's snapshot,
 * and emails the team what changed. Designed to run on a weekly trigger.
 */
function emailWeeklyDiff() {
  // 1. Read the sheet as it is now, and load last week's snapshot.
  const current = currentRows();
  const previous = lastSnapshot();

  // 2. Bail out early if the sheet is empty — nothing to snapshot.
  if (current.length === 0) {
    Logger.log('Projects sheet is empty — nothing to report.');
    return;
  }

  // 3. Save this week's state before doing anything else, so a later
  //    failure can't lose the snapshot.
  saveSnapshot(current);

  // 4. No previous snapshot: this is the first run. Store the baseline
  //    and tell the team the diff starts next week.
  if (!previous) {
    GmailApp.sendEmail(
      TEAM,
      'Weekly Projects snapshot',
      'First snapshot stored. Diff will start next week.'
    );
    return;
  }

  // 5. Compare the two weeks and email the formatted diff.
  const diff = compare(previous, current);
  GmailApp.sendEmail(TEAM, 'Projects — week-over-week changes', formatDiff(diff));
  Logger.log('Weekly diff emailed to the team.');
}

/**
 * Reads the Projects sheet and returns one plain object per row,
 * keyed by the header names in row 1.
 */
function currentRows() {
  const [headers, ...rows] = SpreadsheetApp.openById(PROJECTS_SHEET_ID)
    .getSheets()[0]
    .getDataRange()
    .getValues();
  return rows.map((row) =>
    Object.fromEntries(headers.map((key, i) => [key, row[i]]))
  );
}

/**
 * Loads last week's snapshot from Drive, or null if there isn't one
 * yet (the first ever run).
 */
function lastSnapshot() {
  const id = PropertiesService.getScriptProperties()
    .getProperty(LAST_SNAPSHOT_KEY);
  if (!id) return null;
  return JSON.parse(DriveApp.getFileById(id).getBlob().getDataAsString());
}

/**
 * Writes the current rows to a dated JSON file in the snapshot folder
 * and records its ID so next week's run can find it.
 */
function saveSnapshot(rows) {
  const stamp = new Date().toISOString().slice(0, 10);
  const blob = Utilities.newBlob(
    JSON.stringify(rows),
    'application/json',
    `projects-${stamp}.json`
  );
  const file = DriveApp.getFolderById(SNAPSHOT_FOLDER_ID).createFile(blob);
  PropertiesService.getScriptProperties()
    .setProperty(LAST_SNAPSHOT_KEY, file.getId());
}

/**
 * Compares two weeks of rows, keyed by client + project, and returns
 * the rows added, removed, and changed in status.
 */
function compare(prev, curr) {
  const key = (r) => `${r.client}|${r.project}`;
  const prevMap = new Map(prev.map((r) => [key(r), r]));
  const currMap = new Map(curr.map((r) => [key(r), r]));

  const added = curr.filter((r) => !prevMap.has(key(r)));
  const removed = prev.filter((r) => !currMap.has(key(r)));
  const statusChanged = curr.filter(
    (r) => prevMap.has(key(r)) && prevMap.get(key(r)).status !== r.status
  );
  return { added, removed, statusChanged };
}

/**
 * Turns a diff object into a readable plain-text email body. Sections
 * with no rows are left out entirely.
 */
function formatDiff(d) {
  const section = (label, rows, fn) =>
    rows.length
      ? `\n── ${label} (${rows.length}) ──\n${rows.map(fn).join('\n')}\n`
      : '';
  return (
    [
      section('Added', d.added, (r) => `+ ${r.client} / ${r.project}`),
      section('Removed', d.removed, (r) => `- ${r.client} / ${r.project}`),
      section(
        'Status changed',
        d.statusChanged,
        (r) => `~ ${r.client} / ${r.project}: → ${r.status}`
      ),
    ].join('') || 'No changes this week.'
  );
}

How it works

1. emailWeeklyDiff reads the sheet’s current state with currentRows and loads last week’s snapshot with lastSnapshot.
2. If the sheet is empty it logs a message and stops, so an empty sheet never produces a misleading “everything was removed” report.
3. It saves this week’s snapshot before doing anything else. Saving first means that even if a later step fails, next week still has a snapshot to diff against.
4. On the very first run there is no previous snapshot. The script stores the baseline and emails a short note that the diff begins next week.
5. currentRows reads the whole sheet and turns each row into an object keyed by the header names, so the rest of the code can refer to r.client and r.status instead of column numbers.
6. lastSnapshot looks up the stored file ID in Script Properties and parses the JSON back into objects, or returns null if there is no ID yet.
7. saveSnapshot writes the rows to a dated JSON file in the Drive folder and records the new file’s ID, so each week’s snapshot is both archived and findable.
8. compare builds a lookup keyed by client|project for each week, then finds rows that exist only this week (added), only last week (removed), or in both with a different status (status changed).
9. formatDiff renders the diff as plain text, dropping any section that has no rows, and falls back to “No changes this week.” when nothing moved.

Example run

Suppose last week’s snapshot and this week’s sheet differ like so: a new project started for Crane Co, the Belmont rebrand wrapped up and was deleted, and the Acme website moved from in progress to review. The Friday email reads:

── Added (1) ──
+ Crane Co / Q3 campaign

── Removed (1) ──
- Belmont / Rebrand

── Status changed (1) ──
~ Acme / Website: → review

A week with no edits produces a one-line body: No changes this week. Either way, the team gets the story instead of having to spot it themselves.

Trigger it

This is a weekly report — run it at the end of the working week:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose emailWeeklyDiff, event source Time-driven, type Week timer, set the day to Friday, and pick the 5pm–6pm slot.
4. Save, and approve the authorisation prompt the first time it runs.

Watch out for

• The diff is keyed on client|project. If either value is edited between snapshots, the row reads as one removed and one added rather than a single change. Treat those two fields as stable identifiers.
• Only status changes are tracked. A change to a deadline, an owner, or a note will not appear in the report — extend compare if you need to watch other columns.
• The snapshot is keyed off a single Script Property. If that property is cleared or the snapshot file is deleted, the next run treats itself as a first run and the week’s diff is lost.
• Snapshots accumulate in the Drive folder, one JSON file per week. They are small, but tidy the folder occasionally or add a step to delete old files.
• The script depends on the header names client, project, and status in row 1. Rename a column in the sheet and the matching part of the diff silently stops working.
• The comparison is only as frequent as the trigger. If the weekly run is skipped, the next run diffs against an older snapshot, so the report covers more than one week without saying so.