Create personalized pitch decks at scale

Northwind’s outbound sales team has a master pitch deck and a Prospects Sheet with a few hundred named accounts — industry, opening hook, the works. What they need before each call is a version of the deck with the prospect’s name in the title, the industry-specific framing, and a custom hook on the opening slide. Doing that by hand burns five minutes per prospect and the deck inevitably ends up a quarter out of date.

This script walks the Sheet, generates one tailored deck per prospect, and writes the deck URL back into the same row so re-runs only process new prospects. The output is a folder full of pitch decks named after each account — and a Sheet column that doubles as a CRM-friendly link list.

What you’ll need

• A Slides template containing the pitch layout, with {{prospect}}, {{industry}}, and {{hook}} placeholders wherever the personalised text should appear.
• A Prospects Sheet with headers prospect, industry, hook, and an empty deckUrl column (row 1 is the headers). The script writes the finished deck URL back into deckUrl; rows that already have a URL are skipped on the next run.
• A Drive folder where the generated decks land. Keep it separate from the template.

The script

// The Slides template — the master layout cloned for every prospect.
const TEMPLATE = '1abcPitchTemplateId';

// The Sheet listing prospects. Must include a `deckUrl` column for output.
const PROSPECTS = '1abcProspectsId';

// The Drive folder where generated pitch decks land.
const OUTPUT = '1abcPitchesFolderId';

/**
 * Generates a tailored pitch deck for every prospect that does not
 * already have a deck URL. Writes the URL back into the Sheet.
 */
function personalisePitches() {
  const sheet = SpreadsheetApp.openById(PROSPECTS).getSheets()[0];
  const values = sheet.getDataRange().getValues();
  const [h, ...rows] = values;
  const col = Object.fromEntries(h.map((k, i) => [k, i]));

  // Guard: the script writes URLs back, so the column must exist.
  if (col.deckUrl === undefined) {
    throw new Error('Prospects sheet is missing a `deckUrl` column.');
  }

  const folder = DriveApp.getFolderById(OUTPUT);
  let made = 0;

  // 1. Walk every prospect row. Skip rows that already have a URL —
  //    that is how re-runs stay idempotent.
  rows.forEach((r, i) => {
    if (r[col.deckUrl]) return;
    if (!r[col.prospect]) return; // skip blank rows

    // 2. Copy the template into the output folder under a name that
    //    sorts sensibly alongside the others.
    const copy = DriveApp.getFileById(TEMPLATE)
      .makeCopy(`Pitch — ${r[col.prospect]}`, folder);
    const deck = SlidesApp.openById(copy.getId());

    // 3. Replace placeholders across every slide.
    for (const slide of deck.getSlides()) {
      slide.replaceAllText('{{prospect}}', r[col.prospect]);
      slide.replaceAllText('{{industry}}', r[col.industry] || '');
      slide.replaceAllText('{{hook}}', r[col.hook] || '');
    }

    // 4. Record the URL back into the row so the next run skips it.
    //    +1 accounts for the header row stripped off earlier.
    values[i + 1][col.deckUrl] = copy.getUrl();
    made++;
  });

  // 5. One write at the end is cheaper than writing each cell as we go.
  sheet.getDataRange().setValues(values);
  Logger.log(`Generated ${made} new pitch deck(s).`);
}

How it works

1. personalisePitches reads the whole Sheet in one call. values holds the raw 2D array (so writes are easy), while rows is the data without the header for iteration.
2. A guard checks that the deckUrl column exists. Without it the script would have nowhere to record the output and would loop forever re-generating decks on every run.
3. For each prospect row, the script skips two cases: rows that already have a deckUrl (already processed) and rows missing a prospect name (blank drafts).
4. It copies the template into the output folder with a name like Pitch — Acme Ltd, opens the new deck, and replaces every placeholder across every slide. Missing industry or hook values fall back to an empty string to avoid a literal undefined in the deck.
5. The deck URL is written back into the in-memory values array. The + 1 accounts for the header row that was removed when destructuring into rows.
6. After the loop, a single setValues call writes the whole array back. Batching the write avoids hundreds of tiny round trips against the Sheet.
7. It logs how many new decks were created — usually that matches the number of new prospects added since the last run.

Example run

A Prospects Sheet with these rows (only one already processed):

prospect	industry	hook	deckUrl
Acme Ltd	Manufacturing	Lean-in on uptime metrics	https://docs.google.com/…/old
Boreal Bank	Fintech	Compliance-first messaging
Carve Coffee	Retail	Loyalty as the lead story

After a run, two new decks appear in the output folder:

• Pitch — Boreal Bank
• Pitch — Carve Coffee

And the Sheet’s deckUrl column is filled in for both. Acme is left untouched because its deckUrl was already set. Run the script again ten minutes later with no Sheet changes and it logs “Generated 0 new pitch deck(s)” — exactly the right answer.

Run it

This is an on-demand job. Sales adds rows to the Sheet during the week and runs the script when a batch is ready:

1. In the Apps Script editor, select personalisePitches and click Run.
2. Approve the authorisation prompt the first time.
3. Open the output folder, or click the URLs in the Sheet’s deckUrl column to jump straight to each new deck.

To make it self-service for non-editors, add an onOpen menu so the script can be triggered from inside the Sheet itself:

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('Pitch tools')
    .addItem('Generate new pitch decks', 'personalisePitches')
    .addToUi();
}

Watch out for

• Once a row has a deckUrl, the script never updates it again. If a prospect’s industry or hook changes, clear the deckUrl cell first to force a re-generation — or delete the existing deck in Drive and clear the cell to start clean.
• The script writes the entire Sheet back at the end. If somebody is editing the Sheet during the run, their changes will be overwritten by the in-memory snapshot. Schedule the script for a quiet time of day, or switch to per-cell writes if multiple people edit simultaneously.
• makeCopy plus openById is two network calls per prospect, and the per-slide text replacement adds a third. A few hundred prospects fit comfortably inside the six-minute execution limit; a few thousand will not — batch by adding a LIMIT early in the loop and run on a trigger.
• Re-using the same prospect name in two rows produces two decks with identical filenames in Drive. Drive will keep both with a (1) suffix; use the deck URL in the Sheet, not the filename, when you share the pitch.