Build a quarterly review deck generator

Every quarter, someone at Northwind rebuilds the same review deck — one slide per metric, the latest number typed in, last quarter’s deck duplicated and edited. It takes an afternoon, the numbers occasionally get mistyped, and the deck is out of date the moment a figure in the Sheet changes.

This automation builds the deck from the metrics Sheet instead. It creates a fresh presentation, writes the quarter onto the title slide, and adds one title-and-body slide per metric with the value for the chosen quarter pulled straight from the data. A review that took an afternoon becomes a single function call.

What you’ll need

• A Quarterly metrics Sheet with a header row and one row per metric. Columns: metric, q1, q2, q3, q4. Copy the Sheet ID from its URL.
• Sample data — one row per figure you want on its own slide, for example Revenue, Retention, Billable hours.
• Permission to create files in your Drive (the script makes a new Slides file on every run).

The script

buildQuarterlyDeck takes a quarter label like Q2, reads the Sheet, and assembles the deck. readSheet is the small helper that turns rows into objects.

// The Sheet of quarterly metrics. Copy the ID from its URL.
const METRICS_SHEET_ID = '1abcMetricsId';

/**
 * Builds a review deck for one quarter: a title slide plus one slide per
 * metric, each showing that metric's value for the chosen quarter.
 *
 * @param {string} quarterLabel A quarter label such as "Q1" or "Q3".
 * @returns {string} The URL of the new presentation.
 */
function buildQuarterlyDeck(quarterLabel) {
  // Guard: the label drives which column we read, so it must be supplied.
  if (!quarterLabel) {
    throw new Error('Pass a quarter label, e.g. buildQuarterlyDeck("Q2").');
  }

  // 1. Read the metrics Sheet into an array of row objects.
  const metrics = readSheet(METRICS_SHEET_ID);
  if (!metrics.length) {
    Logger.log('No metrics in the Sheet — nothing to build.');
    return;
  }

  // 2. Create a fresh deck and write the quarter onto the title slide.
  const deck = SlidesApp.create(`${quarterLabel} review`);
  deck.getSlides()[0].getPlaceholders()[0].asShape().getText()
    .setText(quarterLabel);

  // 3. The quarter label maps to a column name: "Q2" -> "q2".
  const column = quarterLabel.toLowerCase();

  // 4. Add one title-and-body slide per metric.
  for (const m of metrics) {
    const slide = deck.appendSlide(SlidesApp.PredefinedLayout.TITLE_AND_BODY);
    slide.getPlaceholders()[0].asShape().getText().setText(m.metric);
    slide.getPlaceholders()[1].asShape().getText()
      .setText(`${quarterLabel}: ${m[column]}`);
  }

  Logger.log(`Built a ${metrics.length}-metric deck: ${deck.getUrl()}`);
  return deck.getUrl();
}

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

How it works

1. buildQuarterlyDeck is called with a quarter label such as Q2. It bails out early if no label is passed, because the label decides which column to read.
2. readSheet opens the metrics Sheet and turns every data row into an object keyed by the header — so a row becomes { metric: 'Revenue', q1: ..., q2: ... }.
3. If the Sheet has no data rows, the script logs a message and stops rather than creating an empty deck.
4. It creates a new presentation named after the quarter and writes the quarter label onto the first placeholder of the default title slide.
5. It lower-cases the quarter label so Q2 matches the q2 column key.
6. For each metric it appends a title-and-body slide, putting the metric name in the title placeholder and Q2: <value> in the body.
7. It logs and returns the deck URL so you can open the finished review.

Example run

The Quarterly metrics Sheet holds:

metric	q1	q2	q3	q4
Revenue	180000	205000
Retention	91%	93%
Billable hours	1240	1310

Running buildQuarterlyDeck('Q2') produces a deck with four slides:

• Title slide: Q2
• Slide 2: title Revenue, body Q2: 205000
• Slide 3: title Retention, body Q2: 93%
• Slide 4: title Billable hours, body Q2: 1310

Next quarter, call buildQuarterlyDeck('Q3') and the same Sheet produces the Q3 deck — no slides touched by hand.

Run it

This is a once-per-quarter job, so run it on demand:

1. In the Apps Script editor, open the script and add a temporary line such as buildQuarterlyDeck('Q2'), or call it from a custom menu.
2. Select the function and click Run.
3. Approve the authorisation prompt the first time.
4. Open the deck URL from the execution log.

To let non-editors trigger it, add a custom menu to the metrics Sheet:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Review tools')
    .addItem('Build Q2 deck', 'buildQ2')
    .addToUi();
}

function buildQ2() {
  buildQuarterlyDeck('Q2');
}

Watch out for

• Placeholder order is layout-dependent. This script assumes the title is placeholder [0] and the body is [1]. If you swap to a custom template, check the indexes or match placeholders by type instead.
• The default title slide must exist. SlidesApp.create gives you one blank slide; the script writes the quarter onto it. If you delete that slide first, getSlides()[0] will point at the wrong place.
• Empty cells render as blank. A metric with no value for the chosen quarter produces a slide reading Q2: with nothing after it. Filter those rows out first if you would rather skip them.
• A new file every run. Each call creates a separate deck and they pile up in Drive. Move finished decks into a quarter folder, or have the script reuse a deck by ID instead of calling SlidesApp.create.
• Number formatting is not carried over. The Sheet’s display format (currency, percentage) is lost — getValues returns the raw value. Format the string in the script if you need $205,000 rather than 205000.