See llms.txt for all machine-readable content.

Back to Templates

Send certification expiry alerts from Google Sheets with Gmail

Created by

Created by: nexum || nexum
nexum

Last update

Last update 3 days ago

Categories

Share

Quick overview

This workflow runs every Monday at 08:00 to read a Google Sheets roster, calculate days until each agent’s certification expiry date, and send a consolidated HTML alert email via Gmail to the configured manager when certifications expire within a defined threshold.

How it works

  1. Runs every Monday at 08:00 on a scheduled trigger.
  2. Loads the configured Google Sheets URL, manager email address, and expiry threshold (in days).
  3. Reads all rows from the agent roster in Google Sheets.
  4. Calculates how many days remain until each row’s Expiry_Date.
  5. Keeps only agents whose certification expires between 0 and the configured threshold.
  6. Builds an HTML table row for each expiring agent and aggregates all rows into a single list.
  7. Sends one consolidated HTML email through Gmail to the manager with the expiring agents and their remaining days.

Setup

  1. Add a Google Sheets OAuth2 credential and ensure the workflow can access your roster spreadsheet.
  2. Add a Gmail OAuth2 credential for the account that sends the alert email.
  3. Update the configured sheet URL, manager email, and expiry threshold values.
  4. Ensure the Google Sheet contains Agent_Name, Cert_Type, and Expiry_Date formatted as dd/MM/yyyy.

Requirements

  • Go back
  • Title
  • Send certification expiry alerts from Google Sheets with Gmail
  • Description
  • Quick overview (10-50 words)
  • This workflow runs every Monday at 08:00 to read a Google Sheets roster, calculate days until each agent’s certification expiry date, and send a consolidated HTML alert email via Gmail to the configured manager when certifications expire within a defined threshold.
  • 41 / 50 words
  • How it works (50+ words)
  • Runs every Monday at 08:00 on a scheduled trigger.
  • Loads the configured Google Sheets URL, manager email address, and expiry threshold (in days).
  • Reads all rows from the agent roster in Google Sheets.
  • Calculates how many days remain until each row’s Expiry_Date.
  • Keeps only agents whose certification expires between 0 and the configured threshold.
  • Builds an HTML table row for each expiring agent and aggregates all rows into a single list.
  • Sends one consolidated HTML email through Gmail to the manager with the expiring agents and their remaining days.
  • 90 / 50+ words
  • Setup (50+ words)
  • Add a Google Sheets OAuth2 credential and ensure the workflow can access your roster spreadsheet.
  • Add a Gmail OAuth2 credential for the account that sends the alert email.
  • Update the configured sheet URL, manager email, and expiry threshold values.

Customization

  • An n8n instance (self-hosted or cloud)
  • A Google account with Gmail and Google Sheets access
  • A roster spreadsheet with columns: Agent_Name, Cert_Type, Expiry_Date (dd/MM/yyyy)

Additional info

Change the expiry threshold in the Configure node (default: 30 days)
Filter by a specific certification type (APS, SSIAP, SST) in Filter Expiring Soon
Send one email per agent by removing the Aggregate node
Adjust the run frequency (e.g. daily) in the schedule trigger