Auto-assemble a weekly all-hands deck

Every Friday, Northwind runs a 30-minute all-hands. The deck is always the same shape — a title slide, then one slide per team with that week’s bullet points — but someone still spends the Thursday afternoon chasing updates and pasting them into Slides. The format never changes, only the words do, which is exactly the kind of job a script should own.

This automation reads a shared Updates sheet where each team drops its weekly note, copies a fixed template deck, and appends one body slide per team that filed an update. By the time anyone opens the meeting, the deck for the current week already exists in Drive — assembled, titled, and consistent.

What you’ll need

• An Updates sheet with three columns and a header row: team, update, and weekStart. Each team adds one row per update; weekStart is the date of that week’s Sunday (or whichever day your week begins).
• A template Slides deck containing a title slide. The title slide must hold the placeholder text {{week}} somewhere on it — the script swaps that for the real date.
• The file IDs of the Updates sheet and the template deck, dropped into the config block at the top of the script.

The script

// The sheet teams write their weekly updates into.
const UPDATES_SHEET_ID = '1abcUpdatesId';

// The template deck — copied fresh for every week's meeting.
const TEMPLATE_DECK_ID = '1abcAllHandsTemplateId';

// Date format used in the deck title and the title-slide placeholder.
const DATE_FORMAT = 'd MMM';

/**
 * Builds this week's all-hands deck: copies the template, stamps the date
 * onto the title slide, and appends one body slide per team that filed an
 * update for the current week.
 */
function buildAllHandsDeck() {
  // 1. Work out which week we are building for.
  const week = currentWeekStart();
  const weekLabel = Utilities.formatDate(week, 'GMT', DATE_FORMAT);

  // 2. Read the Updates sheet and keep only rows for the current week.
  const updates = readSheet(UPDATES_SHEET_ID)
    .filter((u) => sameWeek(u.weekStart, week));

  if (!updates.length) {
    Logger.log('No updates filed for the week of ' + weekLabel + '.');
    return;
  }

  // 3. Copy the template deck and give it a dated name.
  const deck = DriveApp.getFileById(TEMPLATE_DECK_ID)
    .makeCopy('All-hands — ' + weekLabel);
  const slides = SlidesApp.openById(deck.getId());

  // 4. Stamp the date onto the title slide's {{week}} placeholder.
  slides.getSlides()[0].replaceAllText('{{week}}', weekLabel);

  // 5. Group the updates by team so each team gets a single slide.
  const byTeam = new Map();
  for (const u of updates) {
    if (!byTeam.has(u.team)) byTeam.set(u.team, []);
    byTeam.get(u.team).push(u.update);
  }

  // 6. Append one TITLE_AND_BODY slide per team, bullets and all.
  for (const [team, items] of byTeam) {
    const slide = slides.appendSlide(SlidesApp.PredefinedLayout.TITLE_AND_BODY);
    slide.getPlaceholders()[0].asShape().getText().setText(team);
    slide.getPlaceholders()[1].asShape().getText()
      .setText(items.map((i) => '• ' + i).join('\n'));
  }

  Logger.log('Built deck for ' + weekLabel + ' with ' + byTeam.size + ' teams.');
}

/**
 * Returns midnight on the first day of the current week (Sunday).
 */
function currentWeekStart() {
  const d = new Date();
  d.setDate(d.getDate() - d.getDay());
  d.setHours(0, 0, 0, 0);
  return d;
}

/**
 * True if two dates fall within the same day — used to match a row's
 * weekStart against the computed start of the current week.
 */
function sameWeek(a, b) {
  return Math.abs(a - b) < 86400000;
}

/**
 * Reads the first tab of a spreadsheet into an array of objects, keyed by
 * the header 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]])));
}

How it works

1. buildAllHandsDeck calls currentWeekStart to find midnight on this week’s Sunday, then formats it as a short label like 3 Aug.
2. It reads the Updates sheet through readSheet, which turns each row into an object keyed by the header, then keeps only rows whose weekStart matches the current week.
3. If no updates were filed, it logs a message and stops — no empty deck.
4. It copies the template deck via DriveApp.makeCopy, names the copy after the week, and opens the copy with SlidesApp.
5. It replaces {{week}} on the title slide with the real date.
6. It groups the updates into a Map keyed by team, so a team that filed three separate rows still ends up on one slide.
7. For each team it appends a TITLE_AND_BODY slide, sets the first placeholder to the team name and the second to the updates as a bulleted list.

Example run

The Updates sheet for the week of 3 August holds:

team	update	weekStart
Design	Shipped the new onboarding flow	2025-08-03
Design	Started the icon refresh	2025-08-03
Sales	Closed the Acme renewal	2025-08-03
Support	Backlog down to 12 tickets	2025-07-27

After a run, the new deck All-hands — 3 Aug contains the template’s title slide (now reading “3 Aug”) plus two body slides — the Support row is skipped because its weekStart belongs to the previous week:

• Design — • Shipped the new onboarding flow / • Started the icon refresh
• Sales — • Closed the Acme renewal

Trigger it

This should run unattended before the meeting, so set a time-driven trigger:

1. In the Apps Script editor, open Triggers (the clock icon).
2. Click Add Trigger.
3. Choose buildAllHandsDeck, event source Time-driven, type Week timer, and pick the day and hour — for a Friday meeting, a Thursday afternoon slot gives you time to glance over the result.

Watch out for

• The script copies the template every run. If it runs twice for the same week you get two decks — clean up duplicates, or have the script check for an existing file named after the week before copying.
• TITLE_AND_BODY placeholders depend on the template’s layouts. If your template strips or renames the standard layouts, getPlaceholders() can return them in a different order or not at all — test against your real template before trusting it.
• weekStart must be a real date, not text. If teams type the date as a string, sameWeek will never match — format the column as a date in the sheet.
• There is no slide for teams that filed nothing. That is usually what you want, but it means a quiet week produces a thin deck rather than a slide saying “no update”.
• Very long updates overflow the body placeholder rather than resizing the font. Keep updates to a sentence or two, or set autofit on the template’s body placeholder.