Certification Tracker Help

Setup guide, answers to common questions, and a free assistant for the Certification Tracker Google Sheets templates (Lite and Pro).

Ask the assistant
Hi! Ask me anything about the Certification Tracker sheet: setup, the Matrix, calendar or email reminders, renewals, or Lite vs Pro.

Get started (both editions)

  1. Employees tab: type over the example row and add your team. Role is used for required certificates. Email and Manager Email are optional.
  2. Certificate Types tab: check the starter list. Set "Valid For (months)" and "Required For" ("All", or roles separated by commas, matching the Role column; blank = optional).
  3. Certificates tab: one row per certificate held. Pick the employee and certificate from the dropdowns and enter the Expiry Date. Days Left, Status, the Matrix and the Dashboard update by themselves.
  4. Matrix tab: one row per active employee, one column per certificate. MISSING (solid red) means the role requires it but nothing is recorded. Pale red = expired, yellow = due within 30 days, blue = valid.
  5. Dashboard tab: compliance %, ACTION NEEDED (expired, missing, due soon), a by-certificate table and the next 12 months of expiries and renewal costs.

Lite: calendar reminders

  1. Lite has no scripts, so Google never asks for permissions and it works on company accounts that block scripts and in the Google Sheets phone app.
  2. On the Certificates tab, click "Add to Calendar" in a row. Google Calendar opens with the reminder filled in, and the employee and manager (emails from the Employees tab) invited as guests. Press Save.
  3. The event is placed 30 days before the expiry date; change this on the Settings tab (0 to 365 days). If that day has passed, the event is created for today.
  4. When a certificate is renewed, add a new row with the new dates (the old row shows "Replaced"), delete the old calendar event, and click "Add to Calendar" on the new row.

Pro: automatic email reminders

  1. Press ENABLE TRACKER at the top of the Certificates tab (or menu "Cert Tracker" > "Enable tracker"). Google asks for permission once (see the FAQ), then a test email is sent right away.
  2. Every day the sheet emails before each expiry, using the plan set per certificate type on Certificate Types > Reminders: Early (90, 60, 30, 14, 7 days before), Standard (60, 30, 7 days before) or Short (30, 7 days before), each also on the expiry date. You can type your own days, e.g. "45, 14, 0".
  3. Settings > "Reminders Go To" chooses who is emailed: employee, manager and admin; manager and admin; or admin only. Each person gets at most one email a day listing all their certificates that are due.
  4. Settings > "Weekly Summary" picks the day each manager gets their team's expired, expiring and missing certificates, and the admin gets the whole company. It is only sent when there is something to report.
  5. With a Completed date and a blank Expiry Date, Pro fills in the Expiry Date from Certificate Types > Valid For.
  6. Menu "Cert Tracker" > "Add certificate list" adds ready-made certificate types for Australia, the United Kingdom or the United States.

Pro: renew a certificate

  1. Press OPEN RENEWALS at the top of the Certificates tab (or menu > Open renewals). A side panel lists certificates expiring within 90 days or already expired.
  2. Enter the renewal date and press Renew, then Confirm. The new expiry date is calculated from Valid For and reminders start over.
  3. You can also add a new row for the renewed certificate: the old row shows "Replaced".

Pro: link Doyouok (free) to know if reminders ever stop

  1. Sign up for free at doyouok.com.
  2. On the Doyouok dashboard create a check: Task Name "Certification Tracker", Timeout (hours) 26, then press Create.
  3. Press "Copy Link" on that check and paste it into Settings > "Doyouok Ping URL" in the sheet.
  4. Press SEND TEST EMAIL on the Settings tab. The check turns "Up" on your Doyouok dashboard.
  5. If the daily check-in stops or reports a failed email, you are alerted by email, Telegram, Slack or Discord. Only a "still running" signal is sent: no employee names, emails, certificates or dates.

Frequently asked questions

Both have the Employees, Certificate Types, Certificates, Matrix and Dashboard tabs. Lite uses formulas only: you click "Add to Calendar" per certificate and there are no permission prompts. Pro adds automatic reminder emails to employees, managers and the admin, a weekly manager summary, one-click renewals, automatic expiry dates from the Completed date, ready-made certificate lists for Australia, the UK and the US, and optional Doyouok alerts if reminders stop.

Copy your Employees rows into Pro's Employees tab (same columns). On Certificates, select your rows from Employee to Notes and paste them into Pro's Employee column (same order). Add your certificate types on Pro's Certificate Types tab, then press ENABLE TRACKER.

Yes. The script is your own private copy inside your spreadsheet, so Google shows this for every personal script. Click "Advanced", then "Go to (project name)", then "Allow". You only do this once.

Some Google Workspace admins block scripts. Ask your Google admin to allow them, or use Certification Tracker Lite, which has no scripts.

Edit this spreadsheet, send reminder emails from your account, run once a day, show the Renewals side panel, and connect to an external service (only used if you add a Doyouok Ping URL).

MISSING means the employee's Role matches Certificate Types > Required For but no row exists on Certificates. Add a row for that employee and certificate with its Expiry Date. If it is not really required, change Required For.

Replaced: the same employee has a newer row for the same certificate, so only the latest one counts. Left: the employee's Status on the Employees tab is Left; their certificates are no longer counted, reminded or shown on the Matrix.

Check spam / junk first; the email is sent from your own Google account. Make sure Settings > "Admin Email" is right (blank = your Google account email), then press SEND TEST EMAIL again.

Use menu "Cert Tracker" > "Check sheet for problems", then "Enable tracker" again; this recreates the daily timer and re-asks for permission if Google removed it. Link Doyouok to be told automatically next time.

Free Gmail accounts can send to about 100 recipients a day, Google Workspace accounts to 1,500. Each person gets at most one reminder email per day.

Only after you press Save in Google Calendar. Google may ask whether to send invitations to the guests: choose Send. You can remove guests before saving.

The Matrix and Dashboard show up to 200 active employees and 25 certificate types. The Certificates tab holds 2,000 rows.

No. Everything stays in your Google account. Pro only sends the reminder emails themselves, and, if you link Doyouok, a "still running" signal with no employee data.

Yes. Use the filter button in any header to sort or filter, and add rows at the bottom. Don't rename the column headers. In Pro, don't delete the hidden ID column on Certificates.