nexum This workflow corresponds to n8n.io template #17807 — we link there as the canonical source.
This workflow follows the Gmail → Google Sheets recipe pattern — see all workflows that pair these two integrations.
The workflow JSON
Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →
{
"name": "Send certification expiry alerts for security agents with Gmail and Google Sheets",
"nodes": [
{
"name": "Overview",
"type": "n8n-nodes-base.stickyNote",
"position": [
-580,
-147
],
"parameters": {
"color": 7,
"width": 420,
"height": 1400,
"content": "## Send Certification Expiry Alerts for Security Agents\n\nEvery Monday morning, this workflow scans your Google Sheets agent roster for APS, SSIAP, or SST certifications expiring within the next 30 days, and sends a consolidated alert email to the manager \u2014 no manual spreadsheet checks needed.\n\n### How it works\n\n1. **Every Monday 08:00** fires the workflow on a weekly schedule.\n2. **Configure** holds all user-facing settings (sheet URL, expiry threshold, manager email).\n3. **Read Agent Roster** fetches all rows from your agent database.\n4. **Calculate Days Until Expiry** computes how many days remain before each certification expires.\n5. **Filter Expiring Soon** keeps only agents with a certification expiring within the threshold.\n6. **Build Alert Line** formats each agent's details into an HTML table row.\n7. **Aggregate Alert Rows** combines all expiring agents into a single list.\n8. **Send Alert Email** delivers one consolidated email to the manager with the full list.\n\n### Setup\n\n1. Connect your **Google Sheets OAuth2** credential.\n2. Connect your **Gmail OAuth2** credential.\n3. In the **Configure** node, update `sheetUrl`, `managerEmail`, and `expiryThreshold`.\n4. Ensure your sheet has these columns: Agent_Name, Cert_Type, Expiry_Date.\n5. Activate the workflow.\n\n### Customization\n\n- Change the alert threshold in the **Configure** node (default: 30 days).\n- Filter by certification type by adding a condition in **Filter Expiring Soon**.\n- Send individual emails per agent by removing the Aggregate node."
},
"typeVersion": 1
},
{
"name": "Trigger & Configure",
"type": "n8n-nodes-base.stickyNote",
"position": [
-60,
-147
],
"parameters": {
"color": 1,
"width": 720,
"height": 367,
"content": "## Trigger & Configure\n\nFires every Monday at 08:00. Configure holds the sheet URL, manager email, and expiry threshold (default: 30 days)."
},
"typeVersion": 1
},
{
"name": "Read & Calculate",
"type": "n8n-nodes-base.stickyNote",
"position": [
740,
-147
],
"parameters": {
"color": 1,
"width": 720,
"height": 367,
"content": "## Read & Calculate\n\nFetches all agents from the roster. Computes the number of days remaining before each certification expires."
},
"typeVersion": 1
},
{
"name": "Filter & Format",
"type": "n8n-nodes-base.stickyNote",
"position": [
1540,
-147
],
"parameters": {
"color": 1,
"width": 720,
"height": 367,
"content": "## Filter & Format\n\nKeeps only agents whose certification expires within the configured threshold. Formats each entry as an HTML table row."
},
"typeVersion": 1
},
{
"name": "Aggregate & Alert",
"type": "n8n-nodes-base.stickyNote",
"position": [
2340,
-128
],
"parameters": {
"color": 1,
"width": 720,
"height": 348,
"content": "## Aggregate & Alert\n\nCombines all expiring agent rows into a single list. Sends one consolidated alert email to the manager."
},
"typeVersion": 1
},
{
"name": "Every Monday 08:00",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
0,
0
],
"parameters": {
"rule": {
"interval": [
{
"field": "cronExpression",
"expression": "0 8 * * 1"
}
]
}
},
"typeVersion": 1.3
},
{
"name": "Configure",
"type": "n8n-nodes-base.set",
"position": [
400,
0
],
"parameters": {
"mode": "manual",
"options": {},
"assignments": {
"assignments": [
{
"id": "cfg-sheet",
"name": "sheetUrl",
"type": "string",
"value": "https://docs.google.com/spreadsheets/d/YOUR_SHEET_ID_HERE/edit"
},
{
"id": "cfg-email",
"name": "managerEmail",
"type": "string",
"value": "user@example.com"
},
{
"id": "cfg-threshold",
"name": "expiryThreshold",
"type": "number",
"value": 30
}
]
},
"includeOtherFields": false
},
"typeVersion": 3.4
},
{
"name": "Read Agent Roster",
"type": "n8n-nodes-base.googleSheets",
"position": [
800,
0
],
"parameters": {
"options": {},
"resource": "sheet",
"operation": "read",
"sheetName": {
"__rl": true,
"mode": "id",
"value": 0
},
"documentId": {
"__rl": true,
"mode": "url",
"value": "={{ $('Configure').first().json.sheetUrl }}"
}
},
"typeVersion": 4.7
},
{
"name": "Calculate Days Until Expiry",
"type": "n8n-nodes-base.set",
"position": [
1200,
0
],
"parameters": {
"mode": "manual",
"options": {},
"assignments": {
"assignments": [
{
"id": "assign-days",
"name": "daysUntilExpiry",
"type": "number",
"value": "={{ Math.floor(DateTime.fromFormat($json.Expiry_Date, 'dd/MM/yyyy').diff($now, 'days').days) }}"
}
]
},
"includeOtherFields": true
},
"typeVersion": 3.4
},
{
"name": "Filter Expiring Soon",
"type": "n8n-nodes-base.filter",
"position": [
1600,
0
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 3,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "loose"
},
"combinator": "and",
"conditions": [
{
"id": "cond-lte",
"operator": {
"type": "number",
"operation": "lte"
},
"leftValue": "={{ $json.daysUntilExpiry }}",
"rightValue": "={{ $('Configure').first().json.expiryThreshold }}"
},
{
"id": "cond-gte",
"operator": {
"type": "number",
"operation": "gte"
},
"leftValue": "={{ $json.daysUntilExpiry }}",
"rightValue": "0"
}
]
}
},
"typeVersion": 2.3
},
{
"name": "Build Alert Line",
"type": "n8n-nodes-base.set",
"position": [
2000,
0
],
"parameters": {
"mode": "manual",
"options": {},
"assignments": {
"assignments": [
{
"id": "assign-row",
"name": "alertRow",
"type": "string",
"value": "={{ \"<tr><td>\" + $json.Agent_Name + \"</td><td>\" + $json.Cert_Type + \"</td><td>\" + $json.Expiry_Date + \"</td><td>\" + $json.daysUntilExpiry + \" days</td></tr>\" }}"
}
]
},
"includeOtherFields": false
},
"typeVersion": 3.4
},
{
"name": "Aggregate Alert Rows",
"type": "n8n-nodes-base.aggregate",
"position": [
2400,
0
],
"parameters": {
"options": {},
"aggregate": "aggregateAllItemData",
"destinationFieldName": "agents"
},
"typeVersion": 1
},
{
"name": "Send Alert Email",
"type": "n8n-nodes-base.gmail",
"position": [
2800,
0
],
"parameters": {
"sendTo": "={{ $('Configure').first().json.managerEmail }}",
"message": "={{ \"<html><body style=\\\"font-family:Arial,sans-serif\\\"><p>The following agents have certifications expiring within \" + $('Configure').first().json.expiryThreshold + \" days:</p><table border=\\\"1\\\" cellpadding=\\\"8\\\"><tr><th>Agent</th><th>Certification</th><th>Expiry date</th><th>Days remaining</th></tr>\" + $json.agents.map(a => a.alertRow).join('') + \"</table><p>Please arrange renewal as soon as possible.</p></body></html>\" }}",
"options": {
"appendAttribution": false
},
"subject": "={{ \"Certification expiry alert \u2014 \" + $json.agents.length + \" agent(s) require renewal\" }}",
"resource": "message",
"emailType": "html",
"operation": "send"
},
"typeVersion": 2.2
}
],
"active": false,
"settings": {
"executionOrder": "v1"
},
"connections": {
"Configure": {
"main": [
[
{
"node": "Read Agent Roster",
"type": "main",
"index": 0
}
]
]
},
"Build Alert Line": {
"main": [
[
{
"node": "Aggregate Alert Rows",
"type": "main",
"index": 0
}
]
]
},
"Read Agent Roster": {
"main": [
[
{
"node": "Calculate Days Until Expiry",
"type": "main",
"index": 0
}
]
]
},
"Every Monday 08:00": {
"main": [
[
{
"node": "Configure",
"type": "main",
"index": 0
}
]
]
},
"Aggregate Alert Rows": {
"main": [
[
{
"node": "Send Alert Email",
"type": "main",
"index": 0
}
]
]
},
"Filter Expiring Soon": {
"main": [
[
{
"node": "Build Alert Line",
"type": "main",
"index": 0
}
]
]
},
"Calculate Days Until Expiry": {
"main": [
[
{
"node": "Filter Expiring Soon",
"type": "main",
"index": 0
}
]
]
}
},
"description": "Every Monday, scans a Google Sheets agent roster for APS, SSIAP, or SST certifications expiring within 30 days, and sends a consolidated alert email to the manager listing agents who need renewal."
}
Pro
For the full experience including quality scoring and batch install features for each workflow upgrade to Pro
About this workflow
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. Runs every…
Source: https://n8n.io/workflows/17807/ — original creator credit. Request a take-down →
Related workflows
Workflows that share integrations, category, or trigger type with this one. All free to copy and import.
BSW Growth Agent · Lite (Free Tier). Uses googleSheets, googleDrive, httpRequest, gmail. Scheduled trigger; 21 nodes.
Google Sheets, Google Drive, HTTP Request +1
Founder's Discovery Engine. Uses googleSheets, googleDrive, httpRequest, gmail. Scheduled trigger; 21 nodes.
Google Sheets, Google Drive, HTTP Request +1
This workflow runs every Friday at 16:00, reads an agent roster from Google Sheets, emails each active agent a personalized timesheet reminder via Gmail, and writes the date the reminder was sent back
Google Sheets, Gmail
Perfect for store owners and agencies.
HTTP Request, Google Sheets, Gmail
Ejemplo01-mejorado. Uses gmail, googleSheets. Scheduled trigger; 6 nodes.
Gmail, Google Sheets