Generate a portfolio deck from project data

When Northwind pitches a new client, the first thing the prospect asks for is “what have you done lately?”. The team keeps a Projects Sheet with every engagement — client, project, outcome, a link to the hero image — and a flag column for the projects worth showing off. What they don’t keep is a tidy deck of those flagged projects, because by the time someone has built one the featured list has already changed.

This script reads the Sheet, filters to the rows where featured is true, and stamps out one slide per featured project from a template. Each slide gets the client, project, outcome, and the hero image fetched live from its URL. The result is a single portfolio deck — refreshed by re-running the script, never out of date for more than a few minutes.

What you’ll need

• A Slides template with two slides: a title slide (slide 1) and a project-layout slide (slide 2) containing the placeholders {{client}}, {{project}}, {{outcome}}, and {{heroImage}}. The script duplicates slide 2 for every project; slide 1 is left alone.
• A Projects Sheet with headers client, project, outcome, heroImage, and featured. The featured column should hold TRUE or FALSE; only TRUE rows make it into the deck.
• The hero images served from URLs the script can fetch — public Drive share links, a CMS, or an internal image server. The script uses UrlFetchApp to pull each image as a blob.

The script

// The Sheet listing every project, with a `featured` flag column.
const PROJECTS_SHEET_ID = '1abcProjectsId';

// The template deck: slide 0 is the title, slide 1 is the project layout.
const PORTFOLIO_TEMPLATE_ID = '1abcPortfolioTemplateId';

// Position and size for the hero image, in points.
const IMAGE_LEFT = 50;
const IMAGE_TOP = 50;
const IMAGE_WIDTH = 600;
const IMAGE_HEIGHT = 350;

/**
 * Builds a portfolio deck containing one slide per featured project.
 * The original template is never modified — every run produces a new file.
 */
function buildPortfolioDeck() {
  // 1. Read the Sheet and keep only featured rows.
  const projects = readSheet(PROJECTS_SHEET_ID).filter((p) => p.featured);
  if (!projects.length) {
    Logger.log('No featured projects — leaving the portfolio untouched.');
    return;
  }

  // 2. Copy the template so the master is never edited. Name the new file
  //    after the current year so older portfolios are still findable.
  const master = DriveApp.getFileById(PORTFOLIO_TEMPLATE_ID)
    .makeCopy(`Portfolio — ${new Date().getFullYear()}`);
  const deck = SlidesApp.openById(master.getId());
  const template = deck.getSlides()[1]; // slide 0 is the title slide

  // 3. Reuse the template slide for the first project, then duplicate it
  //    for each additional one. Fill in placeholders and image as we go.
  for (let i = 0; i < projects.length; i++) {
    const slide = i === 0 ? template : template.duplicate();
    slide.replaceAllText('{{client}}', projects[i].client || '');
    slide.replaceAllText('{{project}}', projects[i].project || '');
    slide.replaceAllText('{{outcome}}', projects[i].outcome || '');

    if (projects[i].heroImage) {
      try {
        const blob = UrlFetchApp.fetch(projects[i].heroImage).getBlob();
        slide.insertImage(blob, IMAGE_LEFT, IMAGE_TOP, IMAGE_WIDTH, IMAGE_HEIGHT);
      } catch (err) {
        Logger.log(`Skipped image for ${projects[i].client}: ${err.message}`);
      }
    }
  }

  Logger.log(`Built portfolio with ${projects.length} project(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. buildPortfolioDeck calls readSheet, which turns each row into an object keyed by the headers in row 1. It then filters down to the rows where featured is truthy.
2. If nothing is featured, the script logs and exits — no empty deck is created.
3. It copies the template into Drive under a name like Portfolio — 2026. The yearly name means archive copies live happily side by side after a re-run in a new year.
4. It grabs slide 1 (index 1 — slide 0 is the title slide) as the project template. The first featured project fills that slide directly; every subsequent project gets a duplicate of it.
5. replaceAllText swaps each placeholder. Missing values fall back to an empty string so the script does not crash on a half-typed row.
6. If heroImage is set, UrlFetchApp.fetch pulls the image as a blob and insertImage drops it onto the slide at the configured position and size. The try/catch means a single broken URL does not abort the whole run.
7. It logs the URL of the finished portfolio so you can jump straight to it from the execution log.

Example run

A Projects Sheet with these rows:

client	project	outcome	heroImage	featured
Acme Ltd	Brand refresh	Conversions +38%	https://example.com/acme.png	TRUE
Boreal Bank	Mobile redesign	3.2 → 4.6 stars	https://example.com/boreal.png	TRUE
Carve Coffee	Loyalty pilot	Repeat orders +22%	https://example.com/carve.png	FALSE
Dewar Hotels	Booking flow	Conversions +12%	https://example.com/dewar.png	TRUE

Produces a deck named Portfolio — 2026 with the title slide untouched and three project slides — Acme, Boreal, Dewar — each with the matching text and hero image. Carve is skipped because its featured flag is FALSE.

Run it

This is an on-demand job, run when the featured list changes:

1. In the Apps Script editor, select buildPortfolioDeck and click Run.
2. Approve the authorisation prompt the first time — UrlFetchApp needs external-request access, on top of the usual Slides and Sheets scopes.
3. Open the new portfolio from the execution log to review the slides.

Watch out for

• Every run creates a new file. After a few iterations, your Drive folder fills with Portfolio — 2026, Portfolio — 2026 (1), and so on. If you want one canonical deck, look the file up by name first and update it in place instead of re-copying the template.
• UrlFetchApp.fetch only works for publicly accessible URLs. Drive share links need to be set to “Anyone with the link”; private URLs will fail the fetch and the catch will skip the image, leaving a blank area on the slide.
• The image position is fixed by IMAGE_LEFT/IMAGE_TOP/IMAGE_WIDTH/ IMAGE_HEIGHT. If the template uses a different layout, tweak those constants — or replace the literal insertImage call with one that matches a named placeholder.
• UrlFetchApp has a daily quota. A portfolio of twenty featured projects is fine; a thousand-row catalogue with images on every row will hit it. For that case, consider batching across multiple runs.