How to Issue Training Completion Certificates from Google Sheets (Numbered, with Expiry Dates, 2026)

If you run internal training, a safety program, workshops or continuing-education sessions, you probably already keep completions in a spreadsheet. The certificate itself is the easy part. The parts that cause trouble later are the record-keeping ones: two certificates with the same number, an expiry date someone worked out by hand, or a renewal that overwrote the original record.

This guide sets up a small training register in Google Sheets: one tab for courses (with how long each certificate stays valid), one row per completion, and a free Apps Script that gives each passed row a unique certificate number, calculates the expiry date, saves a PDF certificate, and writes everything back to the row. If you only need a one-off certificate when someone submits a quiz, the simpler Form-to-PDF certificate guide is enough. For the basic template-and-merge method, see invoices and certificates from rows.

Watch the 2-minute walkthrough on YouTube. Short on time? Watch the 50-second version.

Step 1: a Courses tab

Put each course on its own row once, so names and validity periods are typed in one place instead of on every completion:

Course codeCourse nameHoursValid months
FA-101First Aid Basics824
FORKForklift Operator436
ONBNew Hire Orientation2(blank = no expiry)

The validity periods above are examples only. Use the period your program, employer or accrediting body actually sets.

Step 2: a Completions tab, one row per completion

Each time someone finishes a course, add a row. Don't overwrite an old row when someone renews; add a new one. That keeps a full history, and each certificate keeps its own number. Use these headers (the script finds columns by name, so the order doesn't matter and you can add your own columns):

Tip: turn on Data › Data validation for Course code (dropdown from the Courses range) and Result (dropdown list), so typos don't create rows the script can't match.

Step 3: the certificate template in Google Docs

Create a Doc, set it to landscape (File › Page setup), add your border, logo and a signature image, and type these placeholders where you want them. Each one must be typed in one go; if you edit the middle of a placeholder later, retype the whole thing.

Certificate of Completion
This certifies that {{Trainee}}
has completed {{Course name}} ({{Hours}} hours)
on {{Completed on}}.

Instructor: {{Instructor}}
Certificate no. {{Cert #}} · Issued {{Issued on}} · Valid until {{Expires on}}

{{Course code}} is also available. For a course with no validity period, {{Expires on}} becomes “No expiry”, so phrase that line so it still reads well (for example “Valid until: No expiry”), or keep two templates.

Copy the template ID (between /d/ and /edit in its URL) and the ID of a Drive folder for finished certificates (after /folders/).

Step 4: the script

In the sheet, open Extensions › Apps Script, paste the script below and fill in the two IDs. In the Apps Script editor, open Project Settings and make the time zone match the spreadsheet's (File › Settings in the sheet), so dates don't shift by a day. Save, reload the sheet, then use Certificates › Issue certificates for completed rows and approve the permissions the first time.

/**
 * Training completion certificates from Google Sheets.
 *
 * Tab "Courses":     Course code | Course name | Hours | Valid months
 * Tab "Completions": Trainee | Email | Course code | Completed on | Result | Instructor |
 *                    Cert # | Issued on | Expires on | Certificate PDF   (extra columns are fine)
 *
 * For every Completions row whose Result is Pass/Complete and whose Cert # is empty:
 * gives it the next certificate number (TC-2026-0001, ...), works out the expiry date
 * from the course's Valid months, fills the Google Docs template and saves a PDF,
 * then writes the number, dates and PDF link back to the row. Numbers are never reused.
 */
const TEMPLATE_ID = 'PASTE_TEMPLATE_DOC_ID';
const FOLDER_ID   = 'PASTE_OUTPUT_FOLDER_ID';
const PREFIX      = 'TC';     // numbers look like TC-2026-0001
const PASS_WORDS  = ['pass', 'passed', 'complete', 'completed'];
const KEEP_DOCS   = false;    // true keeps the filled Google Doc next to each PDF
const MAX_PER_RUN = 40;       // stays well inside Apps Script's 6-minute limit

function onOpen() {
  SpreadsheetApp.getUi().createMenu('Certificates')
    .addItem('Issue certificates for completed rows', 'issueCertificates')
    .addToUi();
}

function issueCertificates() {
  const lock = LockService.getDocumentLock();
  if (!lock.tryLock(30000)) throw new Error('Another run is in progress. Try again in a minute.');
  try {
    const ss = SpreadsheetApp.getActive();
    const tz = ss.getSpreadsheetTimeZone();
    const fmt = (d) => Utilities.formatDate(d, tz, 'MMMM d, yyyy');
    const courses = readCourses_(ss.getSheetByName('Courses'));
    const sh = ss.getSheetByName('Completions');
    const data = sh.getDataRange().getValues();
    const head = data[0].map((h) => String(h).trim());
    const col = (name) => {
      const i = head.indexOf(name);
      if (i < 0) throw new Error('Completions is missing the column "' + name + '"');
      return i;
    };
    const c = {
      trainee: col('Trainee'), course: col('Course code'), done: col('Completed on'),
      result: col('Result'), instructor: col('Instructor'), num: col('Cert #'),
      issued: col('Issued on'), expires: col('Expires on'), pdf: col('Certificate PDF')
    };
    const today = new Date();
    const year = Utilities.formatDate(today, tz, 'yyyy');
    const props = PropertiesService.getDocumentProperties();
    const key = 'lastCert-' + PREFIX + '-' + year;
    let last = Math.max(Number(props.getProperty(key) || 0), highestNumber_(data, c.num, year));
    const folder = DriveApp.getFolderById(FOLDER_ID);
    const template = DriveApp.getFileById(TEMPLATE_ID);
    const problems = [];
    let made = 0;

    for (let r = 1; r < data.length; r++) {
      const row = data[r];
      if (String(row[c.num]).trim() !== '') continue;                       // already issued
      if (PASS_WORDS.indexOf(String(row[c.result]).trim().toLowerCase()) < 0) continue;
      const course = courses[String(row[c.course]).trim().toUpperCase()];
      const done = row[c.done];
      const trainee = String(row[c.trainee]).trim();
      if (!trainee || !course || !(done instanceof Date)) { problems.push(r + 1); continue; }
      if (made >= MAX_PER_RUN) break;                                        // run again for the rest

      last += 1;
      props.setProperty(key, String(last));        // reserve the number before making files
      const number = PREFIX + '-' + year + '-' + String(last).padStart(4, '0');
      const expires = course.months > 0 ? addMonths_(done, course.months) : null;
      const fields = {
        'Trainee': trainee,
        'Course name': course.name,
        'Course code': course.code,
        'Hours': course.hours,
        'Completed on': fmt(done),
        'Instructor': String(row[c.instructor]).trim(),
        'Cert #': number,
        'Issued on': fmt(today),
        'Expires on': expires ? fmt(expires) : 'No expiry'
      };

      const name = number + ' ' + trainee + ' - ' + course.name;
      const copy = template.makeCopy(name, folder);
      const doc = DocumentApp.openById(copy.getId());
      const body = doc.getBody();
      Object.keys(fields).forEach((k) => {
        const escaped = k.replace(/[.*+?^${}()|[\]\\]/g, '\\$&');
        body.replaceText('\\{\\{' + escaped + '\\}\\}', String(fields[k]));
      });
      doc.saveAndClose();
      const pdf = folder.createFile(copy.getAs(MimeType.PDF).setName(name + '.pdf'));
      if (!KEEP_DOCS) copy.setTrashed(true);

      sh.getRange(r + 1, c.num + 1).setValue(number);
      sh.getRange(r + 1, c.issued + 1).setValue(today);
      sh.getRange(r + 1, c.expires + 1).setValue(expires || 'No expiry');
      sh.getRange(r + 1, c.pdf + 1).setValue(pdf.getUrl());
      made++;
    }
    ss.toast('Issued ' + made + ' certificate(s).' +
      (problems.length ? ' Check rows ' + problems.join(', ') + ' (trainee, course code or date).' : ''));
    return { made: made, problems: problems };
  } finally {
    lock.releaseLock();
  }
}

/** Courses tab -> { 'FA-101': {code, name, hours, months}, ... } (codes are case-insensitive). */
function readCourses_(sheet) {
  const rows = sheet.getDataRange().getValues();
  const h = rows[0].map((x) => String(x).trim());
  const iCode = h.indexOf('Course code'), iName = h.indexOf('Course name');
  const iHours = h.indexOf('Hours'), iMonths = h.indexOf('Valid months');
  const out = {};
  rows.slice(1).forEach((row) => {
    const code = String(row[iCode]).trim();
    if (!code) return;
    out[code.toUpperCase()] = {
      code: code,
      name: String(row[iName]).trim() || code,
      hours: iHours < 0 ? '' : row[iHours],
      months: iMonths < 0 ? 0 : Number(row[iMonths]) || 0
    };
  });
  return out;
}

/** Highest NNNN already used in the Cert # column for this prefix and year. */
function highestNumber_(data, numCol, year) {
  const re = new RegExp('^' + PREFIX + '-' + year + '-(\\d+)$');
  let max = 0;
  for (let r = 1; r < data.length; r++) {
    const m = String(data[r][numCol]).trim().match(re);
    if (m) max = Math.max(max, Number(m[1]));
  }
  return max;
}

/** Same as EDATE in Sheets: add months, clamping to the end of a shorter month. */
function addMonths_(d, months) {
  const out = new Date(d.getFullYear(), d.getMonth() + months, 1);
  const lastDay = new Date(out.getFullYear(), out.getMonth() + 1, 0).getDate();
  out.setDate(Math.min(d.getDate(), lastDay));
  return out;
}

How the numbering works

How the expiry date is worked out

Expires on = Completed on + the course's Valid months, the same way the Sheets function EDATE does it: a completion on March 31 with 11 months' validity expires on the last day of February. If your program counts validity as “through the day before”, change the line addMonths_(done, course.months) to subtract one day, and say so on the certificate. The expiry is fixed at issue time. If you later change a course's Valid months, existing certificates keep their dates; only new ones use the new period.

Step 5: see who is current and who is expiring

Add two helper columns to Completions (say K and L, assuming the column order from Step 2: A Trainee, C Course code, D Completed on, E Result, G Cert #, I Expires on). Status marks the latest certificate for each person and course:

=IF(OR(G2="",E2="Void"),"",IF(COUNTIFS(A:A,A2,C:C,C2,D:D,">"&D2,G:G,"<>",E:E,"<>Void")>0,"Superseded",IF(AND(ISNUMBER(I2),I2<TODAY()),"Expired","Current")))

Days left:

=IF(ISNUMBER(I2),I2-TODAY(),"")

Then a tab that lists everything expiring in the next 60 days (current certificates only):

=QUERY(Completions!A:L,"select A, C, G, I, L where K = 'Current' and L <= 60 order by I",1)

And a simple training matrix, with trainees down column A and course codes across row 1, showing the latest expiry date for each pair (format as a date and add a red conditional format for dates before today):

=LET(x,MAXIFS(Completions!$I:$I,Completions!$A:$A,$A2,Completions!$C:$C,B$1),IF(x=0,"",x))

A course with no expiry shows blank in this matrix because “No expiry” is text. To answer “is certificate TC-2026-0012 real?”, a one-cell lookup on a separate tab does it: =XLOOKUP(B1,Completions!G:G,Completions!A:A,"Not found").

Tips and caveats

Want certificates without maintaining a script? SheetWeave (disclosure: we make it) is a Google Sheets add-on that fills a Google Docs template from each row and saves a Doc, a PDF, or both in a Drive folder you choose, writing the links back to the row. It can run on selected rows, all rows, or only new ones, and file names can be built from your columns (for example the certificate number and trainee). It merges the values in your sheet, so keep the certificate number and expiry date as columns (typed or pasted as values, or an EDATE formula for expiry). Sheet data stays inside your Google account and it uses narrow scopes. 14-day free trial, no credit card required.
See SheetWeave

Related Google Sheets guides

Instructions refer to Google Sheets, Google Docs, Google Drive and Apps Script as they worked on November 12, 2026. Not affiliated with Google or any other vendor named here. Google Sheets, Google Docs and Google Drive are trademarks of Google LLC. Features change, so verify in your own account. Questions or corrections: hello@loomwise.app.