Generate product one-pagers from a catalog

Northwind’s catalogue lives in a Sheet — one row per SKU, with the name, spec line, price, and a link to the product photo. The sales team keeps asking for a one-pager per product: same layout, same fonts, just the right product’s details and image. Building them by hand means duplicating a template a hundred times, swapping the text three times per slide, and dragging the image into roughly the same place every time. It does not get done.

This script does the duplicating. It reads the catalogue, clones the one-pager template once per SKU within a single deck, and stamps the name, spec, and price onto each slide. If there’s an image URL, it fetches the image and drops it on the slide at a fixed position. The result is a single deck — one slide per product — exported to PDF or shared straight from Drive.

What you’ll need

• A Slides template with a single slide containing the one-pager layout. Anywhere the name, spec, or price should appear, write {{name}}, {{spec}}, and {{price}}. Leave a clear rectangle where the product image should land — the script inserts the image at a fixed coordinate.
• A Catalogue Sheet with headers sku, name, spec, price, and imageUrl in row 1, one row per product. The imageUrl is optional; rows without one get a slide with no image.
• A Drive home for the generated deck. The script saves it in your default Drive folder under the name Catalogue one-pagers.

The script

// The one-slide Slides template that defines the one-pager layout.
const TEMPLATE = '1abcOnePagerTemplateId';

// The Sheet holding the catalogue, one row per SKU.
const CATALOGUE = '1abcCatalogueId';

// Currency symbol used in front of the price field.
const CURRENCY = '£';

// Image placement on each slide, in points.
const IMAGE_LEFT = 400;
const IMAGE_TOP = 100;
const IMAGE_WIDTH = 240;
const IMAGE_HEIGHT = 240;

/**
 * Reads the catalogue and produces one slide per product, cloned from
 * the template into a single deck.
 */
function buildOnePagers() {
  // 1. Read the catalogue. Skip the run if there is nothing to build.
  const items = readSheet(CATALOGUE);
  if (!items.length) {
    Logger.log('Catalogue is empty — nothing to generate.');
    return;
  }

  // 2. Copy the template into a fresh deck so the master is never edited.
  const master = DriveApp.getFileById(TEMPLATE).makeCopy('Catalogue one-pagers');
  const deck = SlidesApp.openById(master.getId());
  const templateSlide = deck.getSlides()[0];

  // 3. Reuse the existing template slide for the first item, then
  //    duplicate for each subsequent product.
  for (let i = 0; i < items.length; i++) {
    const slide = i === 0 ? templateSlide : templateSlide.duplicate();
    slide.replaceAllText('{{name}}', items[i].name || '');
    slide.replaceAllText('{{spec}}', items[i].spec || '');
    slide.replaceAllText('{{price}}', `${CURRENCY}${items[i].price ?? ''}`);

    // 4. Fetch and insert the product image, if one is configured.
    //    A bad URL on one row should never abort the whole run.
    if (items[i].imageUrl) {
      try {
        const blob = UrlFetchApp.fetch(items[i].imageUrl).getBlob();
        slide.insertImage(blob, IMAGE_LEFT, IMAGE_TOP, IMAGE_WIDTH, IMAGE_HEIGHT);
      } catch (err) {
        Logger.log(`Skipped image for ${items[i].name}: ${err.message}`);
      }
    }
  }

  Logger.log(`Generated ${items.length} one-pager(s): ${master.getUrl()}`);
}

/**
 * Reads a single-tab Sheet into an array of objects, using row 1 as keys.
 */
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. buildOnePagers reads the catalogue Sheet via readSheet, which turns each row into an object keyed by the headers in row 1. Empty catalogues short-circuit before any Drive work happens.
2. It copies the template into Drive under a fixed name, then opens the copy in Slides. The master template is never modified, so a botched run can simply be deleted.
3. Slide 0 of the copy is the template layout. The first product fills that slide directly; every subsequent product calls templateSlide.duplicate() to clone it.
4. replaceAllText swaps each {{placeholder}} for the product’s value. Nullish-coalescing (??) and || '' keep blank cells from rendering as the literal undefined or null.
5. The price is prefixed with the configured currency symbol so the slide reads “£12.99” rather than just “12.99”. Swap CURRENCY once at the top of the file if you localise the deck.
6. If the row has an imageUrl, UrlFetchApp.fetch pulls the image as a blob and insertImage drops it at the configured rectangle. The try/catch means one broken link only affects one slide; the rest of the run still completes.
7. It logs the URL of the finished deck so you can jump straight to it from the execution log.

Example run

A Catalogue Sheet with these rows:

sku	name	spec	price	imageUrl
NW-101	Maple desk	1400 × 700 mm, oak	349.00	https://example.com/desk.jpg
NW-102	Linen chair	Steel frame, washable	129.00	https://example.com/chair.jpg
NW-103	Floor lamp	1.6 m, brushed brass	89.00

Produces a three-slide deck called Catalogue one-pagers:

• Slide 1 — Maple desk, “1400 × 700 mm, oak”, £349.00, with the desk photo on the right.
• Slide 2 — Linen chair, “Steel frame, washable”, £129.00, with the chair photo.
• Slide 3 — Floor lamp, “1.6 m, brushed brass”, £89.00, no image (the row had no imageUrl).

Run it

This is an on-demand job — run it when the catalogue Sheet has changed enough to warrant a fresh PDF:

1. In the Apps Script editor, select buildOnePagers and click Run.
2. Approve the authorisation prompt the first time. UrlFetchApp needs external-request access, in addition to the standard Slides, Sheets, and Drive scopes.
3. Open the new deck from the execution log, then File → Download → PDF to share with the sales team.

Watch out for

• Every run creates a fresh Catalogue one-pagers file in your default Drive folder. After a few iterations you will see Catalogue one-pagers (1), (2), and so on. Either delete the previous run first or look the existing file up by name and update it in place.
• UrlFetchApp.fetch only retrieves publicly accessible URLs. Drive share links need to be set to “Anyone with the link” and image hosts must allow direct fetches. Broken links are caught and logged, but the resulting slide will have an empty rectangle where the photo should be.
• Prices come straight from the Sheet, so 129 displays as £129, not £129.00. Format the price column to two decimals in the Sheet, or wrap the value in Number(items[i].price).toFixed(2) if you want the script to enforce the trailing zero.
• A large catalogue stresses both UrlFetchApp quotas and the six-minute execution limit. For more than a couple of hundred SKUs, batch the work by reading a slice of the Sheet on each run and tracking progress in a helper column.