Generate a deck from each row of a Sheet

Northwind’s sales team keeps a tidy Case studies Sheet — one row per project, with the client name, the brief, and the outcome. What they really want, before a pitch, is a one-deck case study they can attach to an email: same layout, same fonts, just the right project’s details swapped in. Today that means duplicating a template, find-and-replacing three fields, and re-naming the file. For fifty projects, it eats an afternoon.

This script takes the same template, walks the Sheet, and produces one deck per row — each saved into a dedicated folder, named after the client and project. Run it once and the whole library appears. Add a new row tomorrow and re-run it; the new row gets its own deck and the old ones are untouched (as long as they already exist).

What you’ll need

• A Slides template containing the layout for one case study. Anywhere the client, project, or outcome should appear, write {{client}}, {{project}}, and {{outcome}}.
• A Case studies Sheet with headers client, project, outcome in row 1 and one row per project beneath.
• A Drive folder where the generated decks should be saved. Keep it separate from the template so a future “delete everything in the output folder” tidy-up never touches the master.

The script

// The Slides template — the layout every generated deck is cloned from.
const TEMPLATE = '1abcCaseStudyTemplateId';

// The Sheet that lists every project to turn into a deck.
const PROJECTS = '1abcCaseStudiesSheetId';

// The Drive folder where generated decks land.
const DECKS_FOLDER = '1abcDecksFolderId';

/**
 * Reads the projects Sheet and produces one deck per row, copied from
 * the template and saved into the output folder.
 */
function generateDecksPerRow() {
  // 1. Read the Sheet. Row 1 holds the headers used to look up columns.
  const [h, ...rows] = SpreadsheetApp.openById(PROJECTS).getSheets()[0]
    .getDataRange().getValues();
  const col = Object.fromEntries(h.map((k, i) => [k, i]));
  if (!rows.length) {
    Logger.log('No projects in the Sheet — nothing to generate.');
    return;
  }

  const folder = DriveApp.getFolderById(DECKS_FOLDER);

  // 2. For each row, copy the template into the output folder and replace
  //    every placeholder across every slide. The name combines client and
  //    project so listings sort sensibly.
  let made = 0;
  for (const r of rows) {
    const client = r[col.client];
    const project = r[col.project];
    if (!client || !project) continue; // skip half-empty rows

    const copy = DriveApp.getFileById(TEMPLATE)
      .makeCopy(`${client} — ${project}`, folder);
    const deck = SlidesApp.openById(copy.getId());

    for (const slide of deck.getSlides()) {
      slide.replaceAllText('{{client}}', client);
      slide.replaceAllText('{{project}}', project);
      slide.replaceAllText('{{outcome}}', r[col.outcome] || '');
    }
    made++;
  }

  Logger.log(`Generated ${made} deck(s) into ${folder.getName()}.`);
}

How it works

1. generateDecksPerRow reads the entire Case studies Sheet in one getDataRange().getValues() call — far faster than reading row by row when the Sheet grows.
2. It builds a col lookup mapping header name to column index. That means reordering Sheet columns or inserting a new one between existing columns does not require a code change.
3. If the Sheet has no project rows, it logs and exits without touching Drive.
4. It opens the output folder once, outside the loop, so the script does not re-resolve the folder for every project.
5. For each row, it skips records that are missing the client or project name — those are usually half-typed drafts — and otherwise copies the template into the output folder with a meaningful name like Acme — Brand refresh.
6. It iterates every slide in the new deck and runs replaceAllText for each placeholder. Running across all slides means the template can have multiple pages of layout and they all get filled in.
7. It logs a count so you can confirm the run produced the expected number of decks.

Example run

A Case studies Sheet with these rows:

client	project	outcome
Acme Ltd	Brand refresh	Conversions up 38%
Boreal Bank	Mobile redesign	App-store rating 3.2 → 4.6
Carve Coffee	Loyalty programme	Repeat orders up 22%

Produces three decks in the output folder:

• Acme Ltd — Brand refresh
• Boreal Bank — Mobile redesign
• Carve Coffee — Loyalty programme

Each deck is a copy of the template with the placeholders swapped for that row’s values. Open the Acme one and the outcome slide reads “Conversions up 38%” instead of “{{outcome}}”.

Run it

This is an on-demand job, usually triggered after a quarterly review when several new projects have been added to the Sheet:

1. In the Apps Script editor, select generateDecksPerRow and click Run.
2. Approve the authorisation prompt the first time.
3. Open the output folder in Drive to find the new decks, named after each project.

Watch out for

• Re-running the script makes a fresh copy every time, even for projects already in the folder. The originals are not deleted, so you end up with duplicates named Acme — Brand refresh and Acme — Brand refresh (1). Add a check that calls folder.getFilesByName(name).hasNext() before copying, or write a deck URL back to the Sheet and skip rows that already have one (see Create personalized pitch decks at scale).
• The script reads the Sheet in one go, which is fast, but makeCopy and openById are network calls — fifty rows means a hundred round trips and a couple of minutes of execution time. For very large catalogues expect to hit the six-minute execution limit and split the work into batches.
• replaceAllText is case-sensitive and exact. Keep your placeholders in lowercase and avoid extra spaces inside the braces — {{ client }} will not match {{client}}.
• Special characters in client or project names appear verbatim in the Drive filename. Slashes and colons get sanitised by Drive, but very long names can be awkward to scan in a folder listing — keep names tight.