Four hours every Friday, handed to a script.

Every week, the training coordinator spent close to four hours on a Friday doing the same manual task. Open the spreadsheet of that week’s course attendees. Type each person’s name and course details onto a certificate. Save it. Email it out, one at a time.

It was repetitive, it was open to typing errors, and it was consuming a full half-day that should have gone to planning and strategy instead.

IAP2 Canada runs training courses weekly. Attendee names, email addresses, course titles and dates were already being captured in a Google Sheet as part of registration. The certificate itself used a fixed, pre-designed template, so the design work was already done.

What was missing was the connective tissue. Nothing linked the sheet to the certificate to the outbound email.

There was also a recordkeeping requirement in the background: every certificate issued has to be archived, not just sent.

Rather than replacing the spreadsheet or redesigning the certificate, the job was to automate the space between them. The Google Sheet stayed the system of record. The template stayed exactly as it was. The only thing that needed to change was the manual labour of moving data from one to the other.

That made this an Apps Script build rather than a platform project. The tools the organization already paid for could talk to each other directly, with nothing new to license, learn or maintain.

The script reads each row of the weekly course sheet, pulls the attendee’s name, course title and date, merges that into the certificate template, renders a PDF and emails it to the attendee.

Two details that separate a real workflow from a demo

1. Every certificate generated is written to the organization’s training Drive automatically, whether or not the email send succeeds. That closes the recordkeeping requirement without anyone having to think about it.
2. If an attendee record has no email address, the script does not fail and does not skip the row. It still generates and archives the certificate, and reports the gap. Nobody ends up without a certificate on file because of one empty cell.