Issue certificates after quiz completion

Northwind runs an internal training quiz once a week. Passing it has always meant a PDF certificate, but generating that certificate has always meant someone opening the template, copy-pasting a name and a date, exporting to PDF, and emailing it. Forty hires later, that someone is exhausted and the certificates start arriving a week late.

This script catches the form-submit event, checks the score, and — for everyone who clears the pass mark — clones a Doc template, swaps the placeholders for their name and the date, exports it as a PDF, and emails it straight to them. The trainer never opens Docs again.

What you’ll need

• A Google Form configured as a quiz with auto-grading. Make sure the total score question is titled exactly Total score so the script can read it.
• A Google Doc template with {{name}} and {{date}} placeholders where you want them to appear. Save its file ID in TEMPLATE.
• The pass mark. The example uses 7 — change PASS_SCORE to suit.
• A form-submit trigger pointing at onFormSubmit.

The script

// Doc template with {{name}} and {{date}} placeholders.
const TEMPLATE = '1abcCertTemplateId';

// Minimum total score for a pass. Anything below is silently skipped.
const PASS_SCORE = 7;

// Date format that prints on the certificate. Long-form for human reading.
const CERT_DATE_FORMAT = 'd MMM yyyy';

// Subject + body of the email that delivers the PDF.
const EMAIL_SUBJECT = 'Your Northwind certificate';
const EMAIL_BODY =
  'Congratulations on passing the Northwind training quiz.\n\n' +
  'Your certificate is attached.\n\n— Awadesh';

/**
 * Form-submit handler. Issues a PDF certificate to anyone who passed.
 *
 * @param {GoogleAppsScript.Events.FormsOnFormSubmit} e
 */
function onFormSubmit(e) {
  // 1. Read the auto-graded total score. Form values are always strings —
  //    parseInt is what turns "8" into a number we can compare.
  const score = parseInt(e.namedValues['Total score']?.[0], 10);
  if (!Number.isFinite(score) || score < PASS_SCORE) {
    Logger.log(`Skipping — score ${score} below pass mark ${PASS_SCORE}.`);
    return;
  }

  const name = e.namedValues.Name?.[0];
  const email = e.namedValues.Email?.[0];
  if (!name || !email) {
    Logger.log('Skipping — missing name or email on submission.');
    return;
  }

  // 2. Clone the template. makeCopy returns a Drive file we can open as a Doc.
  const copy = DriveApp.getFileById(TEMPLATE).makeCopy(`Certificate — ${name}`);
  const doc = DocumentApp.openById(copy.getId());

  // 3. Replace placeholders in the document body. replaceText takes a regex
  //    string — the curly braces here are literal, not capture groups.
  const today = Utilities.formatDate(new Date(), 'GMT', CERT_DATE_FORMAT);
  doc.getBody().replaceText('{{name}}', name);
  doc.getBody().replaceText('{{date}}', today);
  doc.saveAndClose();

  // 4. Export the saved Doc as PDF and email it. getAs forces a fresh render,
  //    so any text we just replaced is picked up correctly.
  const pdf = DriveApp.getFileById(copy.getId()).getAs('application/pdf');
  GmailApp.sendEmail(email, EMAIL_SUBJECT, EMAIL_BODY, { attachments: [pdf] });

  Logger.log(`Issued certificate to ${name} <${email}> for score ${score}.`);
}

How it works

1. The form-submit trigger fires onFormSubmit with an event whose namedValues holds each answer keyed by question title.
2. The script reads Total score, coerces it with parseInt, and bails out if the value is missing, non-numeric, or below PASS_SCORE. Quiz auto-grading writes the total as a string, so the coercion matters.
3. It pulls Name and Email next and bails again if either is missing — a certificate with no recipient is wasted work.
4. DriveApp.makeCopy clones the template Doc into the script owner’s root Drive. The copy is named after the hire so you can find it later if something needs reissuing.
5. replaceText swaps every {{name}} and {{date}} placeholder in the document body. saveAndClose flushes the changes so the next read sees them.
6. getAs('application/pdf') renders the freshly-saved Doc as a PDF blob, and GmailApp.sendEmail attaches it to a short congratulations message.

Example run

A hire submits the quiz with score 8 out of 10:

Question	Answer
Name	Theo Reyes
Email	[email protected]
Total score	8

Within a few seconds:

• A Doc named Certificate — Theo Reyes appears in Drive with Theo Reyes and 10 Aug 2025 filled into the template.
• Theo receives an email titled “Your Northwind certificate” with the rendered PDF attached.

Someone scoring 5 triggers a log line — Skipping — score 5 below pass mark 7. — and no Doc, no email.

Trigger it

PDF rendering and Gmail send both need authorisation, so this requires an installable on-submit trigger.

1. In the Apps Script editor, open Triggers (clock icon, left sidebar).
2. Click Add trigger and pick onFormSubmit.
3. Event source: From form. Event type: On form submit.
4. Save and approve the authorisation prompts.

Watch out for

• Quiz total titles vary. If you used the default “Total points” or renamed the question, change the e.namedValues['Total score'] key to match. Forms uses the visible title as the key.
• Certificate Docs pile up. Every pass leaves a Doc in your Drive root. Move the copy into an issued-certificates folder after generation, or trash it once the email goes out — the PDF blob lives independently of the source.
• Placeholders are case-sensitive. {{Name}} and {{name}} are different patterns to replaceText. Pick one convention and keep template and script aligned.
• PDF rendering is best-effort on complex layouts. If your template has embedded fonts or rotated text, render a sample by hand once before trusting the trigger — Google’s PDF export occasionally drops glyphs you did not expect.