Generate event badges and name cards

When Northwind hosts a client breakfast or an industry meet-up, the badges always come together at the last minute. The attendee list lives in a sheet, the badge design lives in a Slides template, and someone — usually the office manager — spends an evening duplicating slides, retyping names, and generating QR codes one at a time. The list always changes the day before, and the print order misses the deadline.

This script connects the sheet to the template in one pass. It copies the template, duplicates the badge slide for every attendee, fills in name and role from the spreadsheet, fetches a QR code that points to their email, and inserts it onto the slide. You hand the resulting deck to the printer and the office manager goes home. It is the kind of small piece of automation that pays for itself the first time you run it.

What you’ll need

• A Slides template with one badge slide containing {{name}} and {{role}} placeholder text where the printed badge should show those fields.
• A Google Sheet of attendees with headers name, role, and email (any order — the script reads by header name).
• Nothing else — the QR codes come from the free qrserver.com endpoint via UrlFetchApp.

The script

// The Slides template — must contain ONE slide with {{name}} and {{role}}.
const TEMPLATE = '1abcBadgeTemplateId';

// The attendees sheet — header row in row 1, columns name/role/email.
const ATTENDEES = '1abcAttendeesId';

// Where to drop the QR code on each badge (points). Tune to your template.
const QR_LEFT = 300;
const QR_TOP = 250;
const QR_SIZE = 100;

// QR code generator. Returns a PNG. The size param drives image resolution,
// not the rendered size on the slide.
const QR_ENDPOINT = 'https://api.qrserver.com/v1/create-qr-code/?size=200x200&data=';

/**
 * Reads attendees from a sheet and produces a printable deck — one slide
 * per attendee — with their name, role, and a QR code to their email.
 *
 * @returns {string} URL of the generated deck.
 */
function generateBadges() {
  // 1. Read the attendee sheet and build a header lookup so the script
  //    works regardless of column order.
  const [headerRow, ...rows] = SpreadsheetApp.openById(ATTENDEES)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  const col = Object.fromEntries(headerRow.map((k, i) => [String(k).trim(), i]));
  ['name', 'role', 'email'].forEach((h) => {
    if (col[h] === undefined) {
      throw new Error(`Attendee sheet is missing required column "${h}".`);
    }
  });

  if (!rows.length) {
    Logger.log('No attendees in the sheet — nothing to do.');
    return '';
  }

  // 2. Copy the template so the original stays clean for next time.
  const today = new Date().toISOString().slice(0, 10);
  const master = DriveApp.getFileById(TEMPLATE)
    .makeCopy(`Badges — ${today}`);
  const deck = SlidesApp.openById(master.getId());
  const template = deck.getSlides()[0];

  // 3. For each attendee, reuse the template for the first row and
  //    duplicate it for every subsequent row.
  rows.forEach((row, i) => {
    const name = row[col.name];
    const role = row[col.role];
    const email = row[col.email];

    if (!name || !email) {
      Logger.log(`Row ${i + 2}: missing name or email — skipped.`);
      return;
    }

    const slide = i === 0 ? template : template.duplicate();

    // 4. replaceAllText is scoped to this slide only, so the same
    //    placeholders on other slides aren't accidentally touched.
    slide.replaceAllText('{{name}}', String(name));
    slide.replaceAllText('{{role}}', String(role || ''));

    // 5. Fetch the QR code (a PNG) and drop it onto the slide. The
    //    QR points at the attendee's email — change to a check-in URL
    //    if your event uses one.
    const qr = UrlFetchApp.fetch(QR_ENDPOINT + encodeURIComponent(email)).getBlob();
    slide.insertImage(qr, QR_LEFT, QR_TOP, QR_SIZE, QR_SIZE);
  });

  Logger.log(`Generated ${rows.length} badge slide(s) in ${master.getUrl()}.`);
  return master.getUrl();
}

How it works

1. generateBadges reads the attendee sheet and builds a header-to-column lookup so the script doesn’t care about column order — it asks for name, role, and email by name and complains loudly if one is missing.
2. If the sheet is empty, it logs and exits. There is no point copying the template if there is nothing to fill in.
3. It makes a dated copy of the badge template using DriveApp.makeCopy. The original is untouched, so the same template feeds every event.
4. It walks the attendee rows. The first row reuses the template’s existing slide; every subsequent row calls template.duplicate() to produce a fresh copy at the same position. duplicate() preserves every detail of the layout, so the badges all look identical.
5. slide.replaceAllText swaps the {{name}} and {{role}} placeholders on that slide only. Scoping at the slide level means a placeholder used on a different slide (a footer, for instance) is not accidentally rewritten.
6. For each attendee it fetches a QR code from the free qrserver.com endpoint, encoded with the attendee’s email as the payload. The PNG blob is inserted at a fixed position on the slide — QR_LEFT, QR_TOP, QR_SIZE give you a single place to tune the layout for your template.
7. It logs the URL of the generated deck so you can open it straight from the editor and send it to the printer.

Example run

Say the attendees sheet has four rows:

name	role	email
Maya Patel	Creative director	[email protected]
Jordan Reyes	Senior engineer	[email protected]
Sam Okafor	Account manager	[email protected]
Lin Chen	Producer	[email protected]

After one run, a deck named Badges — 2025-09-21 lands in your Drive root with four slides — one per attendee — each showing the name, the role, and a QR code pointing at their email. The original template is untouched.

Run it

This is an on-demand job — you run it once per event:

1. Update the attendees sheet right up until you run the script — last-minute additions are fine.
2. In the Apps Script editor, select generateBadges and click Run.
3. Approve the authorisation prompt the first time — the script needs Slides, Drive, Sheets, and external requests for the QR endpoint.
4. Open the URL from the log, check a couple of slides, and download as PDF for the printer.

For a recurring event, give the office manager a custom menu on the attendees sheet that calls generateBadges — no editor required.

Watch out for

• The QR endpoint is a free third-party service. It’s fine for occasional events but has no SLA — if uptime matters, swap in a QR library or a paid endpoint. The QR data is the email; if you’d rather not put attendee emails in a generated image, point the QR at a per-attendee check-in URL instead.
• The template slide is mutated. The first attendee’s badge replaces the placeholders on the template’s only slide. That’s fine because the script works on a copy, but never point TEMPLATE at a deck you intend to keep using by hand.
• Position is hard-coded. The QR lands at QR_LEFT, QR_TOP regardless of what the template looks like. Tune those constants once for your badge layout — measure in points, not pixels.
• replaceAllText is case-sensitive. {{Name}} does not match {{name}}. Keep your template placeholders consistent with the keys the script expects, or normalise both sides.