Extract data from certificate PDFs straight into a Google Sheets compliance register (no code)

Category: How-to Guides • Niche: Compliance automation, OCR, Google Drive/Sheets, Apps Script/Make.com

Contents

• Quick answer
• Who this is for
• What you’ll set up
• Step-by-step: build the Sheet and email reminders
• Optional: add automatic PDF OCR with Make.com
• Retention periods cheat‑sheet (UK HSE)
• Troubleshooting
• FAQ

Quick answer

You can drop certificate PDFs (EICR, Gas Safe, LOLER, LEV, asbestos, first aid and more) into a Google Drive folder and have key fields written into a Google Sheet automatically, with colour statuses and reminder emails at 90, 30 and 7 days before expiry. Start with a simple Sheet plus a short Apps Script for email reminders, then, if you want full automation from PDFs, connect Drive to Make.com for OCR and field extraction. No coding background needed.

Who this is for

• Small contractors who need a single compliance register for training, equipment and site certificates.
• Office managers who chase expiries manually and want it automated.
• Trades that work with landlords or principal contractors who ask for evidence on demand.

What you’ll set up

• A Google Sheet with these columns: Type, Holder/Asset, Issue date, Expiry date, File link, Status, Days to expiry, Manager email, Engineer email.
• Conditional formatting to show Red if expired or under 0 days, Amber at 30 days, Green otherwise.
• A once‑a‑day email reminder from the Sheet using a ready‑to‑paste Apps Script.
• Optional: OCR that reads PDFs placed in a Drive folder and appends a row automatically.

Step-by-step: build the Sheet and email reminders

1. Create the Sheet

• In row 1, add headers: Type | Holder/Asset | Issue date | Expiry date | File link | Status | Days to expiry | Manager email | Engineer email
• Use Data > Data validation to force valid dates in Issue date and Expiry date.

2. Calculate “Days to expiry” and Status

• In Days to expiry (G2): =F2-TODAY() if F is Expiry date. If your Expiry is in D, adjust accordingly.
• In Status (F2), use:
  =IF(G2<0,"Expired",IF(G2<=30,"Due soon","OK"))
• Apply conditional formatting:
  • Expired = red fill
  • Due soon = amber fill
  • OK = green fill

3. Turn emails on with Apps Script

• Extensions > Apps Script. Replace the empty script with this minimal function and save.

function sendExpiryReminders() {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
  const data = sheet.getDataRange().getValues();
  const headers = data.shift();
  const idx = {
    type: headers.indexOf('Type'),
    holder: headers.indexOf('Holder/Asset'),
    expiry: headers.indexOf('Expiry date'),
    status: headers.indexOf('Status'),
    link: headers.indexOf('File link'),
    mgr: headers.indexOf('Manager email'),
    eng: headers.indexOf('Engineer email'),
    dte: headers.indexOf('Days to expiry')
  };
  const today = new Date();
  const targets = [90,30,7,0,-1];
  data.forEach(row => {
    const days = Number(row[idx.dte]);
    if (targets.includes(days)) {
      const to = [row[idx.mgr], row[idx.eng]].filter(Boolean).join(',');
      if (!to) return;
      const subject = `[Compliance] ${row[idx.type]} for ${row[idx.holder]} due in ${days} days`;
      const body = `Heads up,\n\n${row[idx.type]} for ${row[idx.holder]} expires on ${Utilities.formatDate(new Date(row[idx.expiry]), Session.getScriptTimeZone(), 'yyyy-MM-dd')}.\nStatus: ${row[idx.status]}\nFile: ${row[idx.link]}\n\nPlease renew and update the register.`;
      MailApp.sendEmail(to, subject, body);
    }
  });
}

• Set a daily trigger: Triggers > Add Trigger > choose sendExpiryReminders > Time‑driven > Day timer.

4. Optional: push a WhatsApp or SMS too

• If you already use a service like Twilio or a WhatsApp Business API provider, you can call their webhook from Apps Script UrlFetchApp.fetch after MailApp.sendEmail. Keep it simple to start with email.

5. Link the PDF in Drive

• Store the certificate PDF in Drive, right‑click and Get link, paste into File link. If you later automate OCR, this link will be filled for you.

Optional: add automatic PDF OCR with Make.com

You can automate from “PDF dropped in a Drive folder” to “row in the Sheet” using Make.com.

• Trigger: Google Drive “Watch files” in a specific folder.
• Step: OCR and parse fields. Pick one parser you’re comfortable with (Google Cloud Vision OCR, Nanonets, Klippa, or a Google Workspace Add‑on). Many invoice/receipt parsers work fine for simple certificate layouts.
• Step: Google Sheets “Add a row”. Map fields: Type, Holder/Asset, Issue date, Expiry date, File link.
• Step: Slack/Email/WhatsApp notification if parsing fails, so you can key it manually.

Tip: Start by enforcing consistent file names like TYPE_NAME_YYYY-MM-DD_expYYYY-MM-DD.pdf. Even without OCR, Apps Script can split the name to fill columns.

Retention periods cheat‑sheet (UK HSE)

Use this as a guide when setting your reminder windows and archive rules. Always check the latest HSE guidance for your specific work.

• RIDDOR records: keep for at least 3 years. See Reg 12 of RIDDOR 2013 at legislation.gov.uk.
• Accident book entries: keep at least 3 years (Social Security (Claims and Payments) Regulations).
• Health surveillance records (COSHH): keep 40 years from last entry.
• Asbestos health records: keep 40 years.
• LEV thorough examination and test: keep records at least 5 years.
• Gas safety checks (landlords): keep records for at least 2 years.
• Legionella monitoring results: keep for at least 5 years.

For links to official pages summarising these periods, see: Health surveillance record keeping (HSE), Asbestos NNLW guidance (HSE), LEV FAQs (HSE), Gas safety record guidance (HSE), Legionella records (HSE), RIDDOR Reg 12 (legislation.gov.uk).

Troubleshooting

• Emails not sending? In Apps Script, check Authorisations and that the daily trigger is active. Also check your spam.
• Days to expiry wrong? Make sure Expiry date cells are true dates, not text. Use Format > Number > Date.
• OCR messy? Start with consistent templates from your cert software. If layouts vary, key fields manually and let the reminders do the heavy lifting.
• Permissions: If your engineers can’t open the File link, set Drive sharing to your Google Workspace domain or add named accounts for privacy.

FAQ

• Can I do this without Make.com? Yes. You can keep it manual: drag PDFs into Drive and paste the link. The Sheet and email automation still save you hours.
• Can I add WhatsApp reminders? Yes, but you need a WhatsApp Business API provider. Email is simplest and widely accepted on audits.
• What about GDPR? Keep only what you need, lock down Drive sharing, and set a deletion rule for expired items after your retention period.
• Will this work with certification software I already use? Yes. Most tools let you save a PDF to Drive. If they use email, send it to a Drive‑connected inbox and forward to the folder.

Want to slash training times and increase revenue per Engineer? Join our Waitlist - https://trainar.ai/waitlist