{ "edges": [ { "to": "0e591128-9b31-43d1-9777-2d2f8de4dbdf", "from": "0d8ad3eb-adce-46bf-81ea-e1bff8f77195", "toPort": "0", "fromPort": "0" }, { "to": "ad9fbc87-7e24-4fd1-bf87-48b800c258f2", "from": "ab9d01ab-c4bd-4481-a7bb-731aecedb828", "toPort": "0", "fromPort": "0" }, { "to": "7a603855-5722-456a-8f8a-b0a644d44369", "from": "b903b697-226e-4724-9338-d23a4bff9d48", "toPort": "0", "fromPort": "0" }, { "to": "0d8ad3eb-adce-46bf-81ea-e1bff8f77195", "from": "ad9fbc87-7e24-4fd1-bf87-48b800c258f2", "toPort": "0", "fromPort": "0" }, { "to": "08a752c6-2e29-48f5-b760-d1a349e37c57", "from": "33c526d5-7b2e-4fe5-83a6-9573aa6b82cf", "toPort": "0", "fromPort": "0" }, { "to": "33c526d5-7b2e-4fe5-83a6-9573aa6b82cf", "from": "0e591128-9b31-43d1-9777-2d2f8de4dbdf", "toPort": "0", "fromPort": "0" }, { "to": "42a5efc8-53ea-4fe5-832a-c24a476052cb", "from": "7a603855-5722-456a-8f8a-b0a644d44369", "toPort": "0", "fromPort": "0" }, { "to": "b903b697-226e-4724-9338-d23a4bff9d48", "from": "6fafe18a-6fd9-4371-8832-3344c0c04b81", "toPort": "0", "fromPort": "0" } ], "nodes": [ { "id": "6fafe18a-6fd9-4371-8832-3344c0c04b81", "name": "Webhook - Receive Confirmation Update", "type": "trigger", "params": { "kind": "n8n-nodes-base.webhook", "path": "confirmation-updates", "options": {}, "responseMode": "responseNode" }, "position": [ -16, 1040 ] }, { "id": "ad9fbc87-7e24-4fd1-bf87-48b800c258f2", "name": "Read Company Database", "type": "integration", "params": { "options": {}, "service": "googleSheets", "operation": "read", "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" } }, "position": [ 112, 496 ] }, { "id": "33c526d5-7b2e-4fe5-83a6-9573aa6b82cf", "name": "Build Interactive Email", "type": "code", "params": { "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 = '';\n\nemailBody += '

';\nemailBody += '

📋 Companies House Filing Deadlines

';\nemailBody += `

Report Date: ${new Date().toLocaleDateString('en-GB')}

`;\nemailBody += `

Total Companies: ${companies.length}

`;\nemailBody += '

';\n\nemailBody += '';\nemailBody += '';\nemailBody += '';\nemailBody += '';\nemailBody += '';\nemailBody += '';\nemailBody += '';\nemailBody += '';\nemailBody += '';\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 += '';\n emailBody += ``;\n emailBody += ``;\n \n emailBody += '';\n \n emailBody += '';\n \n emailBody += ``;\n \n emailBody += '';\n \n emailBody += '';\n});\n\nemailBody += '

Company Name	Company Number	Accounts Due	Confirmation Statement Due	Status	Confirmation Submitted?
${company.company_name}	${company.company_number}	';\n emailBody += ` `;\n emailBody += accountsDue \|\| 'N/A';\n emailBody += ` ${accountsInfo.days} `;\n emailBody += '	';\n emailBody += ` `;\n emailBody += confirmationDue \|\| 'N/A';\n emailBody += ` ${confirmationInfo.days} `;\n emailBody += '	${statusIcon} ${statusText}	';\n emailBody += `✓ Yes`;\n emailBody += `✗ No`;\n emailBody += '

';\nemailBody += '

🔴 Red = Overdue | 🟠 Orange = Due within 7 days | 🟡 Yellow = Due within 30 days | ✅ Green = OK

';\nemailBody += '

';\nemailBody += '';\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}];" }, "position": [ 784, 496 ] }, { "id": "b903b697-226e-4724-9338-d23a4bff9d48", "name": "Process Webhook Data", "type": "code", "params": { "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();" }, "position": [ 208, 1040 ] }, { "id": "7a603855-5722-456a-8f8a-b0a644d44369", "name": "Update Google Sheet Database", "type": "integration", "params": { "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": {}, "service": "googleSheets", "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" } }, "position": [ 432, 1040 ] }, { "id": "42a5efc8-53ea-4fe5-832a-c24a476052cb", "name": "Send Confirmation Page", "type": "passthrough", "params": { "mode": "noOp", "options": {}, "respondWith": "text", "responseBody": "=\n\n\n \n\n\n

✓

Update Recorded!

Confirmation statement status for
{{ $json.company_name }}
has been updated to: {{ $json.confirmation_submitted }}

You can close this window now.

\n\n" }, "position": [ 656, 1040 ] }, { "id": "ab9d01ab-c4bd-4481-a7bb-731aecedb828", "name": "Schedule Trigger1", "type": "trigger", "params": { "kind": "n8n-nodes-base.scheduleTrigger", "rule": { "interval": [ { "field": "cronExpression", "expression": "0 17 * * 1-5" } ] } }, "position": [ -112, 496 ] }, { "id": "08a752c6-2e29-48f5-b760-d1a349e37c57", "name": "Send via Gmail1", "type": "integration", "params": { "sendTo": "=Their email", "message": "={{ $json.emailBody }}", "options": { "appendAttribution": false }, "service": "gmail", "subject": "={{ $json.subject }}", "operation": "read" }, "position": [ 1008, 496 ] }, { "id": "0d8ad3eb-adce-46bf-81ea-e1bff8f77195", "name": "Get Company Data", "type": "http", "params": { "url": "=https://api.company-information.service.gov.uk/company/{{ $json.company_number }}", "options": {}, "authentication": "genericCredentialType", "genericAuthType": "httpBasicAuth" }, "position": [ 336, 496 ] }, { "id": "0e591128-9b31-43d1-9777-2d2f8de4dbdf", "name": "Update Due Dates in Sheet", "type": "integration", "params": { "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": {}, "service": "googleSheets", "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" } }, "position": [ 560, 496 ] } ] }

Accounting Deadline Alerts Automations