Generate personalized certificates for a name list

After every Northwind masterclass, someone has to produce a certificate for each attendee — the same Doc, the same layout, only the name and course changing. Done by hand it means duplicating a template dozens of times, editing three fields in each copy, and hoping no two end up with the same name.

This script does the whole batch. It reads the attendee list, copies the certificate template once per person, swaps in their name, course, and today’s date, and saves each finished Doc to a Drive folder. A class of forty certificates becomes one click.

What you’ll need

• A certificate template Doc containing three placeholders: {{name}}, {{course}}, and {{date}}, placed wherever those values should appear.
• An Attendees sheet with a header row and three columns: name, email, course.
• A Drive folder where the finished certificates are saved.
• The IDs of the template, the sheet, and the folder — fill them into the config block at the top of the script.

The script

// The certificate template Doc, copied once per attendee.
const TEMPLATE = '1abcCertTemplateId';

// The Attendees sheet: name, email, course.
const ATTENDEES = '1abcAttendeesId';

// The Drive folder finished certificates are saved into.
const CERTS_FOLDER = '1abcCertsFolderId';

/**
 * Reads the attendee list and produces one personalised certificate
 * Doc per attendee, saved to the certificates folder.
 */
function generateCertificates() {
  // 1. Today's date, formatted for printing on the certificate.
  const today = Utilities.formatDate(new Date(), 'GMT', 'd MMMM yyyy');

  // 2. Read the attendee sheet — header row plus data rows.
  const [h, ...rows] = SpreadsheetApp.openById(ATTENDEES)
    .getSheets()[0]
    .getDataRange()
    .getValues();

  if (!rows.length) {
    Logger.log('No attendees found — nothing to generate.');
    return;
  }

  // 3. Map header names to column indexes so rows are read by name.
  const col = Object.fromEntries(h.map((k, i) => [k, i]));
  const folder = DriveApp.getFolderById(CERTS_FOLDER);

  // 4. Produce one certificate per attendee.
  for (const r of rows) {
    // Copy the template into the certificates folder.
    const copy = DriveApp.getFileById(TEMPLATE).makeCopy(
      `Certificate — ${r[col.name]}`, folder);

    // Open the copy and swap each placeholder for this attendee's data.
    const doc = DocumentApp.openById(copy.getId());
    const body = doc.getBody();
    body.replaceText('{{name}}', r[col.name]);
    body.replaceText('{{course}}', r[col.course]);
    body.replaceText('{{date}}', today);
    doc.saveAndClose();
  }
  Logger.log('Generated ' + rows.length + ' certificates.');
}

How it works

1. generateCertificates formats today’s date as something readable like 13 July 2025 — the date printed on every certificate in the batch.
2. It reads the whole Attendees sheet, splitting the header row off from the data rows. If there are no data rows, it logs a message and stops.
3. It builds a col lookup that maps each header name to its column index, so later code can write r[col.name] instead of guessing a column number.
4. For each attendee row, it copies the template Doc into the certificates folder, naming the copy Certificate — <name>.
5. It opens the copy and replaces {{name}}, {{course}}, and {{date}} with that attendee’s values and the run date, then saves and closes the Doc.
6. The loop repeats until every attendee has a certificate.

Example run

Suppose the Attendees sheet holds:

name	email	course
Ada Cole	[email protected]	Advanced Spreadsheets
Ben Frost	[email protected]	Apps Script Basics

Running the script on 13 July 2025 produces two Docs in the certificates folder:

Doc name	{{name}}	{{course}}	{{date}}
Certificate — Ada Cole	Ada Cole	Advanced Spreadsheets	13 July 2025
Certificate — Ben Frost	Ben Frost	Apps Script Basics	13 July 2025

Each Doc is the template with those three values filled in — ready to share, or to export as PDF.

Run it

This is an on-demand job — run it once after a masterclass:

1. In the Apps Script editor, select generateCertificates and click Run.
2. Approve the authorisation prompt the first time — the script needs access to the Sheet, the template, and the Drive folder.
3. Open the certificates folder to find one Doc per attendee.

Watch out for

• The script makes one copy per attendee every time it runs. Run it twice and you get duplicate Docs — clear the folder, or filter to attendees who do not already have a certificate, before re-running.
• replaceText fails silently if a placeholder is split across styled text. If part of {{name}} is bold in the template, the match misses — type each placeholder in one consistent style.
• A large class can hit the six-minute execution limit, since each certificate is a copy plus an open-and-save. For a very long list, process the rows in batches across separate runs.
• The output is Docs, not PDFs. To deliver PDFs, export each copy with copy.getAs('application/pdf') and save that to the folder.
• The email column is read into the sheet but not used here. It is there so you can extend the script to email each attendee their certificate as a next step — pair this with Mail merge with personalized PDF attachments to send every certificate out automatically.