Generate printable award certificates as slides

Northwind’s awards night needs forty certificates on stiff paper, each with the right name spelled correctly, the right award title, and tonight’s date. Designing them in Slides is easy. Doing it forty times without a typo, the afternoon of the event, is not — and the printer is already waiting.

This script takes a one-slide template — the certificate design, with {{name}}, {{award}} and {{date}} placeholders where the personal bits go — and stamps out one filled-in slide per row of the Awards Sheet. The output is a single deck. Export it to PDF, send it to the printer, and the only thing left is signing them.

What you’ll need

• A one-slide Slides template that contains the certificate design. Anywhere the recipient’s name should appear, write {{name}}. Likewise {{award}} for the award title and {{date}} for the date.
• An Awards Sheet with headers name and award in row 1, one row per recipient. The script reads them in Sheet order, so put the cohort in the order they will be called on stage.
• Edit access to a Drive folder where the generated deck can live — the script saves the copy with the name Awards night in your default folder.

The script

// The single-slide template that defines the certificate design.
const TEMPLATE = '1abcAwardTemplateId';

// The Sheet listing recipients — columns: name, award.
const AWARDS = '1abcAwardsSheetId';

// Date format shown on each certificate. Adjust if you prefer a US order
// or want to include the day of the week.
const DATE_FORMAT = 'd MMMM yyyy';

/**
 * Reads the Awards Sheet and produces one certificate slide per row,
 * cloned from the template. The result is a single deck ready to print.
 */
function generateAwardSlides() {
  const today = Utilities.formatDate(new Date(), Session.getScriptTimeZone(), DATE_FORMAT);

  // 1. Read the recipients. Row 1 is headers; the rest are people.
  const [h, ...rows] = SpreadsheetApp.openById(AWARDS).getSheets()[0]
    .getDataRange().getValues();
  const col = Object.fromEntries(h.map((k, i) => [k, i]));
  if (!rows.length) {
    Logger.log('No recipients in the Awards Sheet — nothing to print.');
    return;
  }

  // 2. Copy the template so the original stays clean. Every run gets a
  //    fresh deck; the master is never modified.
  const master = DriveApp.getFileById(TEMPLATE).makeCopy('Awards night');
  const deck = SlidesApp.openById(master.getId());
  const templateSlide = deck.getSlides()[0];

  // 3. Reuse the existing template slide for row 0, then duplicate for
  //    every additional recipient. Replace the placeholders on each.
  for (let i = 0; i < rows.length; i++) {
    const slide = i === 0 ? templateSlide : templateSlide.duplicate();
    slide.replaceAllText('{{name}}', rows[i][col.name] || '');
    slide.replaceAllText('{{award}}', rows[i][col.award] || '');
    slide.replaceAllText('{{date}}', today);
  }

  Logger.log(`Generated ${rows.length} certificate(s): ${master.getUrl()}`);
}

How it works

1. generateAwardSlides formats today’s date once, up front, so every certificate carries the same date even if the run straddles midnight.
2. It reads the Awards Sheet, separating the header row from the data. col is a small lookup of column index by header name, which means the script keeps working if you add a column or reorder the existing ones.
3. If the Sheet has no recipients, it logs and exits — there is no point producing an empty deck.
4. It copies the template into Drive under a new name, then opens that copy in Slides. The original template is never touched, so a botched run can simply be deleted.
5. For the first row it reuses the existing single slide; for every row after, it duplicates that slide. replaceAllText then swaps each {{placeholder}} for the recipient’s data.
6. It logs the URL of the finished deck so you can jump straight to it from the execution log.

Example run

An Awards Sheet with these rows:

name	award
Priya Shah	Designer of the year
Marcus Reid	Newcomer of the year
Aoife Byrne	Community award

Produces a three-slide deck called Awards night, where each slide is a copy of the template with the placeholders filled in. Slide 1 reads “Designer of the year — Priya Shah — 27 May 2026”, slide 2 names Marcus, and slide 3 names Aoife. Export as PDF and send to the printer.

Run it

This is an on-demand job — run it the morning of the event, once the recipient list is final:

1. In the Apps Script editor, select generateAwardSlides and click Run.
2. Approve the authorisation prompt the first time.
3. Open the generated Awards night deck from the execution log, then File → Download → PDF to send to the printer.

Watch out for

• The deck name is hard-coded as Awards night. Running the script twice in a day produces two files with the same name in your default Drive folder; rename or delete the previous run before re-running, or pass a unique name to makeCopy such as `Awards night ${today}`.
• replaceAllText is case-sensitive and exact. {{Name}} will not match {{name}}, so keep the template placeholders in lowercase.
• Special characters in names (apostrophes, accents) render fine, but be careful with curly quotes copied from Word — they look identical to straight quotes on screen but break placeholder matching if you ever used them inside {{ }}.
• Long award titles can overflow the placeholder box. Either shorten them in the Sheet or enlarge the text box in the template before running.