Accounting Alerts
Description
Categories
⚙️ Automation
Nodes Used
n8n-nodes-base.coden8n-nodes-base.coden8n-nodes-base.gmailn8n-nodes-base.webhookn8n-nodes-base.stickyNoten8n-nodes-base.stickyNoten8n-nodes-base.stickyNoten8n-nodes-base.stickyNoten8n-nodes-base.httpRequestn8n-nodes-base.googleSheets
PriceGratis
Views0
Last Updated11/28/2025
workflow.json
{
"id": "2x67pBiU8fQE5xi3",
"meta": {
"instanceId": "29404d21c00e483ee830f79142e426d2c17f5f7d0ab933362f77d75688caff98",
"templateCredsSetupCompleted": true
},
"name": "Accounting Alerts",
"tags": [],
"nodes": [
{
"id": "6fafe18a-6fd9-4371-8832-3344c0c04b81",
"name": "Webhook - Receive Confirmation Update",
"type": "n8n-nodes-base.webhook",
"position": [
-16,
1040
],
"webhookId": "aa5c51e0-2405-4161-8635-086e329929b8",
"parameters": {
"path": "confirmation-updates",
"options": {},
"responseMode": "responseNode"
},
"typeVersion": 1
},
{
"id": "ad9fbc87-7e24-4fd1-bf87-48b800c258f2",
"name": "Read Company Database",
"type": "n8n-nodes-base.googleSheets",
"position": [
112,
496
],
"parameters": {
"options": {},
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1513816265,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1IrXd6nu3GCIH6eviMUgQrMd81ElmodDhwzb0NHSQ56E/edit#gid=1513816265",
"cachedResultName": "Sheet2"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1IrXd6nu3GCIH6eviMUgQrMd81ElmodDhwzb0NHSQ56E",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1IrXd6nu3GCIH6eviMUgQrMd81ElmodDhwzb0NHSQ56E/edit?usp=drivesdk",
"cachedResultName": "Company Tracking Database"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "rdxRpLoz0Szp2Nw2",
"name": "Google Sheets account"
}
},
"typeVersion": 4.4
},
{
"id": "33c526d5-7b2e-4fe5-83a6-9573aa6b82cf",
"name": "Build Interactive Email",
"type": "n8n-nodes-base.code",
"position": [
784,
496
],
"parameters": {
"jsCode": "const items = $input.all();\n\nconst webhookBaseUrl = 'https://mega.srv967585.hstgr.cloud/webhook/confirmation-updates';\n\n// Filter companies that have data\nconst companies = items.filter(item => item.json.company_name);\n\n// Sort by accounting due date (soonest first)\ncompanies.sort((a, b) => {\n const dateA = a.json.accounts_due || '9999-99-99';\n const dateB = b.json.accounts_due || '9999-99-99';\n return dateA.localeCompare(dateB);\n});\n\nfunction getDueDateInfo(dueDate, isOverdue) {\n if (!dueDate || dueDate === 'N/A' || dueDate === '') {\n return { bgColor: '#f5f5f5', textColor: '#999', days: 'N/A' };\n }\n \n const daysLeft = Math.ceil((new Date(dueDate) - new Date()) / (1000 * 60 * 60 * 24));\n \n if (isOverdue || daysLeft < 0) {\n return { bgColor: '#ffcccc', textColor: '#d32f2f', days: Math.abs(daysLeft) + ' days', label: 'OVERDUE' };\n } else if (daysLeft <= 7) {\n return { bgColor: '#ffeecc', textColor: '#ff6f00', days: daysLeft + ' days', label: 'URGENT' };\n } else if (daysLeft <= 30) {\n return { bgColor: '#fff9c4', textColor: '#f57f17', days: daysLeft + ' days', label: 'SOON' };\n } else {\n return { bgColor: '#e8f5e9', textColor: '#2e7d32', days: daysLeft + ' days', label: 'OK' };\n }\n}\n\nlet emailBody = '<html><head><style>';\nemailBody += 'body { font-family: Arial, sans-serif; padding: 20px; background-color: #f9f9f9; }';\nemailBody += 'table { border-collapse: collapse; width: 100%; background-color: white; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }';\nemailBody += 'th { background-color: #1976d2; color: white; padding: 12px; text-align: left; font-weight: 600; font-size: 13px; }';\nemailBody += 'td { padding: 12px; border-bottom: 1px solid #e0e0e0; }';\nemailBody += '.date-cell { font-weight: 600; font-size: 14px; border-radius: 4px; padding: 8px; text-align: center; }';\nemailBody += '.days-text { font-size: 12px; margin-top: 3px; }';\nemailBody += '.company-name { font-weight: 600; font-size: 15px; color: #333; }';\nemailBody += '.btn { display: inline-block; padding: 8px 16px; margin: 0 4px; border-radius: 4px; text-decoration: none; font-weight: 600; font-size: 12px; }';\nemailBody += '.btn-yes { background-color: #4caf50; color: white; }';\nemailBody += '.btn-no { background-color: #f44336; color: white; }';\nemailBody += '</style></head><body>';\n\nemailBody += '<div style=\"background-color: white; padding: 20px; border-radius: 8px; margin-bottom: 20px;\">';\nemailBody += '<h2 style=\"color: #1976d2;\">📋 Companies House Filing Deadlines</h2>';\nemailBody += `<p style=\"color: #666;\"><strong>Report Date:</strong> ${new Date().toLocaleDateString('en-GB')}</p>`;\nemailBody += `<p style=\"color: #666;\"><strong>Total Companies:</strong> ${companies.length}</p>`;\nemailBody += '</div>';\n\nemailBody += '<table>';\nemailBody += '<thead><tr>';\nemailBody += '<th>Company Name</th>';\nemailBody += '<th>Company Number</th>';\nemailBody += '<th style=\"text-align: center;\">Accounts Due</th>';\nemailBody += '<th style=\"text-align: center;\">Confirmation Statement Due</th>';\nemailBody += '<th style=\"text-align: center;\">Status</th>';\nemailBody += '<th style=\"text-align: center;\">Confirmation Submitted?</th>';\nemailBody += '</tr></thead><tbody>';\n\ncompanies.forEach(item => {\n const company = item.json;\n \n // READ FROM THE CORRECT FIELDS (from Update Due Dates in Sheet output)\n const accountsDue = company.accounts_due || null;\n const accountsOverdue = false; // We'll need to calculate this\n \n const confirmationDue = company.confirmation_due || null;\n const confirmationOverdue = false; // We'll need to calculate this\n \n const accountsInfo = getDueDateInfo(accountsDue, accountsOverdue);\n const confirmationInfo = getDueDateInfo(confirmationDue, confirmationOverdue);\n \n let statusIcon = '✅';\n let statusText = 'OK';\n \n if (accountsInfo.label === 'OVERDUE' || confirmationInfo.label === 'OVERDUE') {\n statusIcon = '🔴';\n statusText = 'OVERDUE';\n } else if (accountsInfo.label === 'URGENT' || confirmationInfo.label === 'URGENT') {\n statusIcon = '🟠';\n statusText = 'URGENT';\n } else if (accountsInfo.label === 'SOON' || confirmationInfo.label === 'SOON') {\n statusIcon = '🟡';\n statusText = 'SOON';\n }\n \n const yesUrl = `${webhookBaseUrl}?company_number=${company.company_number}&company_name=${encodeURIComponent(company.company_name)}&confirmation_submitted=yes`;\n const noUrl = `${webhookBaseUrl}?company_number=${company.company_number}&company_name=${encodeURIComponent(company.company_name)}&confirmation_submitted=no`;\n \n emailBody += '<tr>';\n emailBody += `<td><div class=\"company-name\">${company.company_name}</div></td>`;\n emailBody += `<td style=\"font-family: monospace;\">${company.company_number}</td>`;\n \n emailBody += '<td>';\n emailBody += `<div class=\"date-cell\" style=\"background-color: ${accountsInfo.bgColor}; color: ${accountsInfo.textColor};\">`;\n emailBody += accountsDue || 'N/A';\n emailBody += `<div class=\"days-text\">${accountsInfo.days}</div>`;\n emailBody += '</div></td>';\n \n emailBody += '<td>';\n emailBody += `<div class=\"date-cell\" style=\"background-color: ${confirmationInfo.bgColor}; color: ${confirmationInfo.textColor};\">`;\n emailBody += confirmationDue || 'N/A';\n emailBody += `<div class=\"days-text\">${confirmationInfo.days}</div>`;\n emailBody += '</div></td>';\n \n emailBody += `<td style=\"text-align: center;\">${statusIcon} ${statusText}</td>`;\n \n emailBody += '<td style=\"text-align: center;\">';\n emailBody += `<a href=\"${yesUrl}\" class=\"btn btn-yes\">✓ Yes</a>`;\n emailBody += `<a href=\"${noUrl}\" class=\"btn btn-no\">✗ No</a>`;\n emailBody += '</td>';\n \n emailBody += '</tr>';\n});\n\nemailBody += '</tbody></table>';\nemailBody += '<div style=\"margin-top: 20px; padding: 15px; background: white; border-radius: 8px;\">';\nemailBody += '<p style=\"color: #666; font-size: 13px;\">🔴 Red = Overdue | 🟠 Orange = Due within 7 days | 🟡 Yellow = Due within 30 days | ✅ Green = OK</p>';\nemailBody += '</div>';\nemailBody += '</body></html>';\n\nlet subject = `Companies House Deadlines - ${companies.length} Companies`;\n\nreturn [{\n json: {\n emailBody: emailBody,\n subject: subject,\n totalCompanies: companies.length\n }\n}];"
},
"typeVersion": 2
},
{
"id": "b903b697-226e-4724-9338-d23a4bff9d48",
"name": "Process Webhook Data",
"type": "n8n-nodes-base.code",
"position": [
208,
1040
],
"parameters": {
"jsCode": "// Loop over input items and add a new field called 'myNewField' to the JSON of each one\nfor (const item of $input.all()) {\n item.json.myNewField = 1;\n}\n\nreturn $input.all();"
},
"typeVersion": 2
},
{
"id": "7a603855-5722-456a-8f8a-b0a644d44369",
"name": "Update Google Sheet Database",
"type": "n8n-nodes-base.googleSheets",
"position": [
432,
1040
],
"parameters": {
"columns": {
"value": {
"row_number": 0,
"company_name": "={{ $json.query.company_name }}",
"company_number": "={{ $json.query.company_number }}",
"confirmation_submitted": "={{ $json.query.confirmation_submitted }}"
},
"schema": [
{
"id": "company_number",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "company_number",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "company_name",
"type": "string",
"display": true,
"required": false,
"displayName": "company_name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "accounts_due",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "accounts_due",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "confirmation_due",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "confirmation_due",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "confirmation_submitted",
"type": "string",
"display": true,
"required": false,
"displayName": "confirmation_submitted",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "last_updated",
"type": "string",
"display": true,
"required": false,
"displayName": "last_updated",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "number",
"display": true,
"removed": false,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"company_number"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "update",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1513816265,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1IrXd6nu3GCIH6eviMUgQrMd81ElmodDhwzb0NHSQ56E/edit#gid=1513816265",
"cachedResultName": "Sheet2"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1IrXd6nu3GCIH6eviMUgQrMd81ElmodDhwzb0NHSQ56E",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1IrXd6nu3GCIH6eviMUgQrMd81ElmodDhwzb0NHSQ56E/edit?usp=drivesdk",
"cachedResultName": "Company Tracking Database"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "rdxRpLoz0Szp2Nw2",
"name": "Google Sheets account"
}
},
"typeVersion": 4.4
},
{
"id": "42a5efc8-53ea-4fe5-832a-c24a476052cb",
"name": "Send Confirmation Page",
"type": "n8n-nodes-base.respondToWebhook",
"position": [
656,
1040
],
"parameters": {
"options": {},
"respondWith": "text",
"responseBody": "=<!DOCTYPE html>\n<html>\n<head>\n <style>\n body { font-family: Arial, sans-serif; display: flex; justify-content: center; align-items: center; height: 100vh; margin: 0; background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); }\n .container { text-align: center; background: white; padding: 40px; border-radius: 10px; box-shadow: 0 10px 40px rgba(0,0,0,0.2); }\n .success { color: #4caf50; font-size: 60px; margin-bottom: 20px; }\n h1 { color: #333; margin: 0 0 10px 0; }\n p { color: #666; font-size: 18px; }\n .company { font-weight: bold; color: #1976d2; }\n </style>\n</head>\n<body>\n <div class=\"container\">\n <div class=\"success\">✓</div>\n <h1>Update Recorded!</h1>\n <p>Confirmation statement status for<br/><span class=\"company\">{{ $json.company_name }}</span><br/>has been updated to: <strong>{{ $json.confirmation_submitted }}</strong></p>\n <p style=\"font-size: 14px; color: #999; margin-top: 30px;\">You can close this window now.</p>\n </div>\n</body>\n</html>"
},
"typeVersion": 1
},
{
"id": "ab9d01ab-c4bd-4481-a7bb-731aecedb828",
"name": "Schedule Trigger1",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
-112,
496
],
"parameters": {
"rule": {
"interval": [
{
"field": "cronExpression",
"expression": "0 17 * * 1-5"
}
]
}
},
"typeVersion": 1.1
},
{
"id": "08a752c6-2e29-48f5-b760-d1a349e37c57",
"name": "Send via Gmail1",
"type": "n8n-nodes-base.gmail",
"position": [
1008,
496
],
"webhookId": "c23fb0cc-dfea-4a01-b1a0-975eb12d10a4",
"parameters": {
"sendTo": "=Their email",
"message": "={{ $json.emailBody }}",
"options": {
"appendAttribution": false
},
"subject": "={{ $json.subject }}"
},
"credentials": {
"gmailOAuth2": {
"id": "l5mju83wh7VSgIlD",
"name": "Mega Gmail account"
}
},
"typeVersion": 2.1
},
{
"id": "0d8ad3eb-adce-46bf-81ea-e1bff8f77195",
"name": "Get Company Data",
"type": "n8n-nodes-base.httpRequest",
"position": [
336,
496
],
"parameters": {
"url": "=https://api.company-information.service.gov.uk/company/{{ $json.company_number }}",
"options": {},
"authentication": "genericCredentialType",
"genericAuthType": "httpBasicAuth"
},
"credentials": {
"httpBasicAuth": {
"id": "kDD72YFbzTcrlv4J",
"name": "Company House API"
}
},
"typeVersion": 4.1
},
{
"id": "0e591128-9b31-43d1-9777-2d2f8de4dbdf",
"name": "Update Due Dates in Sheet",
"type": "n8n-nodes-base.googleSheets",
"position": [
560,
496
],
"parameters": {
"columns": {
"value": {
"accounts_due": "={{ $json.accounts.next_due }}",
"company_name": "={{ $json.company_name }}",
"last_updated": "={{ $json.accounts.last_accounts.made_up_to }}",
"company_number": "={{ $json.company_number }}",
"confirmation_due": "={{ $json.confirmation_statement.next_due }}",
"accounts_submitted": "={{ $json.accounts.last_accounts.made_up_to }}",
"confirmation_submitted": "={{ $json.confirmation_statement.last_made_up_to }}"
},
"schema": [
{
"id": "company_number",
"type": "string",
"display": true,
"removed": false,
"required": false,
"displayName": "company_number",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "company_name",
"type": "string",
"display": true,
"required": false,
"displayName": "company_name",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "accounts_due",
"type": "string",
"display": true,
"required": false,
"displayName": "accounts_due",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "confirmation_due",
"type": "string",
"display": true,
"required": false,
"displayName": "confirmation_due",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "confirmation_submitted",
"type": "string",
"display": true,
"required": false,
"displayName": "confirmation_submitted",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "accounts_submitted",
"type": "string",
"display": true,
"required": false,
"displayName": "accounts_submitted",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "last_updated",
"type": "string",
"display": true,
"required": false,
"displayName": "last_updated",
"defaultMatch": false,
"canBeUsedToMatch": true
},
{
"id": "row_number",
"type": "number",
"display": true,
"removed": true,
"readOnly": true,
"required": false,
"displayName": "row_number",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "defineBelow",
"matchingColumns": [
"company_number"
],
"attemptToConvertTypes": false,
"convertFieldsToString": false
},
"options": {},
"operation": "appendOrUpdate",
"sheetName": {
"__rl": true,
"mode": "list",
"value": 1513816265,
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1IrXd6nu3GCIH6eviMUgQrMd81ElmodDhwzb0NHSQ56E/edit#gid=1513816265",
"cachedResultName": "Sheet2"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "1IrXd6nu3GCIH6eviMUgQrMd81ElmodDhwzb0NHSQ56E",
"cachedResultUrl": "https://docs.google.com/spreadsheets/d/1IrXd6nu3GCIH6eviMUgQrMd81ElmodDhwzb0NHSQ56E/edit?usp=drivesdk",
"cachedResultName": "Company Tracking Database"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "rdxRpLoz0Szp2Nw2",
"name": "Google Sheets account"
}
},
"typeVersion": 4.7
},
{
"id": "a69802b1-4de6-4cfc-ae8d-3c6c47634c54",
"name": "Sticky Note2",
"type": "n8n-nodes-base.stickyNote",
"position": [
-672,
240
],
"parameters": {
"color": 4,
"width": 448,
"height": 416,
"content": "## 🎯 Accounting Alerts Automation\n\n**Purpose:** Automatically track Companies House filing deadlines for UK accounting firms and prevent costly penalties (£150-£1,500 per missed deadline).\n\n**How it works:**\n- Daily automated checks pull live deadline data from Companies House API\n- Color-coded email alerts (Red/Orange/Yellow/Green) prioritize urgent deadlines\n- Interactive \"Yes/No\" buttons let recipients confirm completion status\n- All data syncs back to Google Sheets for complete audit trail\n\n**Value:** Saves 2-3 hours/week per firm while eliminating manual tracking errors."
},
"typeVersion": 1
},
{
"id": "e6b4486d-e96f-40ea-b420-faf3a198f9c6",
"name": "Sticky Note3",
"type": "n8n-nodes-base.stickyNote",
"position": [
-176,
80
],
"parameters": {
"color": 5,
"width": 1424,
"height": 592,
"content": "## ## ⚙️ Daily Deadline Check & Alert System\n\n**Runs:** Every weekday at 5 PM (Mon-Fri)\n\n**What happens:**\n1. **Read Company Database** - Fetches all tracked companies from Google Sheets\n2. **Get Company Data** - Pulls live filing deadlines from Companies House API for each company\n3. **Update Due Dates** - Syncs latest deadline data back to the tracking sheet\n4. **Build Interactive Email** - Creates HTML email with:\n - Color-coded urgency indicators (days remaining)\n - Sortable table by due date\n - Clickable Yes/No confirmation buttons for each company\n5. **Send via Gmail** - Delivers consolidated report to accounting team\n\n**Why automated:** Manual deadline checking across 10-50+ companies is time-consuming and error-prone. This ensures nothing falls through the cracks."
},
"typeVersion": 1
},
{
"id": "711fd3ee-397d-4a16-ad82-fe18003b00aa",
"name": "Sticky Note4",
"type": "n8n-nodes-base.stickyNote",
"position": [
-176,
688
],
"parameters": {
"color": 7,
"width": 1472,
"height": 544,
"content": "## ## ✅ Email Response Handler (Webhook Flow)\n\n**Triggered when:** Recipient clicks \"Yes\" or \"No\" button in the alert email\n\n**What happens:**\n1. **Webhook** - Receives confirmation status (company_number, company_name, yes/no)\n2. **Process Data** - Extracts response details from the webhook payload\n3. **Update Sheet** - Records confirmation status in Google Sheets with timestamp\n4. **Confirmation Page** - Displays success message to user\n\n**Why this matters:** Provides instant feedback to the user and creates an audit trail of who confirmed what and when. No separate tracking system needed—everything updates automatically in the same spreadsheet.\n\n**Result:** Accountability without administrative burden."
},
"typeVersion": 1
},
{
"id": "7ba61a8e-f6d3-4611-93ce-20c0421b76b4",
"name": "Sticky Note5",
"type": "n8n-nodes-base.stickyNote",
"position": [
-672,
704
],
"parameters": {
"width": 464,
"height": 528,
"content": "## ## 📋 Setup Requirements\n\n**Google Sheets Database Structure:**\nCreate a sheet with these columns:\n- company_number (manually entered)\n- company_name (manually entered) \n- accounts_due (auto-updated)\n- confirmation_due (auto-updated)\n- confirmation_submitted (updated via email clicks)\n- last_updated (auto-timestamp)\n\n**Required Credentials:**\n- Google Sheets OAuth (for reading/writing data)\n- Companies House API key (free from api.company-information.service.gov.uk)\n- Gmail OAuth (for sending alerts)\n\n**Webhook Configuration:**\nUpdate webhook URL in \"Build Interactive Email\" node to match your n8n instance.\n\n**Time to Setup:** ~15 minutes once credentials are configured."
},
"typeVersion": 1
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "1e25be39-bcec-4104-90c4-219de5d26c06",
"connections": {
"Get Company Data": {
"main": [
[
{
"node": "Update Due Dates in Sheet",
"type": "main",
"index": 0
}
]
]
},
"Schedule Trigger1": {
"main": [
[
{
"node": "Read Company Database",
"type": "main",
"index": 0
}
]
]
},
"Process Webhook Data": {
"main": [
[
{
"node": "Update Google Sheet Database",
"type": "main",
"index": 0
}
]
]
},
"Read Company Database": {
"main": [
[
{
"node": "Get Company Data",
"type": "main",
"index": 0
}
]
]
},
"Build Interactive Email": {
"main": [
[
{
"node": "Send via Gmail1",
"type": "main",
"index": 0
}
]
]
},
"Update Due Dates in Sheet": {
"main": [
[
{
"node": "Build Interactive Email",
"type": "main",
"index": 0
}
]
]
},
"Update Google Sheet Database": {
"main": [
[
{
"node": "Send Confirmation Page",
"type": "main",
"index": 0
}
]
]
},
"Webhook - Receive Confirmation Update": {
"main": [
[
{
"node": "Process Webhook Data",
"type": "main",
"index": 0
}
]
]
}
}
}