January 22, 2026

Google Sheets + Gmail: filing deadlines you never miss

Lisa Granqvist Partner Workflow Automation Expert

Get a free AI assessment → ⬇️ Use template

Deadline tracking sounds simple until you’re juggling 10 to 50 Companies House filings and one missed date turns into an expensive, avoidable problem.

Practice owners feel it when penalties land. Accounts managers feel it at 4:55 PM. And ops folks cleaning up “who confirmed what” live in the spreadsheet forever. This Sheets Gmail deadlines automation keeps the dates current and pushes a daily summary you can actually act on.

You’ll see how the workflow pulls live due dates, sends a color-coded Gmail report, and records one-click Yes/No confirmations back into Google Sheets with timestamps.

How This Automation Works

The full n8n workflow, from trigger to final output:

n8n Workflow Template: Google Sheets + Gmail: filing deadlines you never miss

Click to explore

flowchart LR

    subgraph sg0["Schedule Flow"]
        direction LR
        n1@{ icon: "mdi:database", form: "rounded", label: "Read Company Database", pos: "b", h: 48 }
        n2["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Build Interactive Email"]
        n6@{ icon: "mdi:play-circle", form: "rounded", label: "Schedule Trigger1", pos: "b", h: 48 }
        n7@{ icon: "mdi:message-outline", form: "rounded", label: "Send via Gmail1", pos: "b", h: 48 }
        n8["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/httprequest.dark.svg' width='40' height='40' /></div><br/>Get Company Data"]
        n9@{ icon: "mdi:database", form: "rounded", label: "Update Due Dates in Sheet", pos: "b", h: 48 }
        n8 --> n9
        n6 --> n1
        n1 --> n8
        n2 --> n7
        n9 --> n2
    end

    subgraph sg1["Flow 2"]
        direction LR
        n0["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/webhook.dark.svg' width='40' height='40' /></div><br/>Webhook - Receive Confirmati.."]
        n3["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/code.svg' width='40' height='40' /></div><br/>Process Webhook Data"]
        n4@{ icon: "mdi:database", form: "rounded", label: "Update Google Sheet Database", pos: "b", h: 48 }
        n5["<div style='background:#f5f5f5;padding:10px;border-radius:8px;display:inline-block;border:1px solid #e0e0e0'><img src='https://flowpast.com/wp-content/uploads/n8n-workflow-icons/webhook.dark.svg' width='40' height='40' /></div><br/>Send Confirmation Page"]
        n3 --> n4
        n4 --> n5
        n0 --> n3
    end

    %% Styling
    classDef trigger fill:#e8f5e9,stroke:#388e3c,stroke-width:2px
    classDef ai fill:#e3f2fd,stroke:#1976d2,stroke-width:2px
    classDef aiModel fill:#e8eaf6,stroke:#3f51b5,stroke-width:2px
    classDef decision fill:#fff8e1,stroke:#f9a825,stroke-width:2px
    classDef database fill:#fce4ec,stroke:#c2185b,stroke-width:2px
    classDef api fill:#fff3e0,stroke:#e65100,stroke-width:2px
    classDef code fill:#f3e5f5,stroke:#7b1fa2,stroke-width:2px
    classDef disabled stroke-dasharray: 5 5,opacity: 0.5
    class n6 trigger
    class n1,n9,n4 database
    class n8,n0,n5 api
    class n2,n3 code
    classDef customIcon fill:none,stroke:none
    class n2,n8,n0,n3,n5 customIcon

The Problem: Companies House deadlines slip in the gaps

If you’re checking Companies House deadlines manually, the process is basically “small risk, repeated a lot.” Someone opens a spreadsheet, looks up a company, searches for the right due date, updates a cell, then tries to tell the team what’s urgent. Do that across 20 companies and you’ve burned the best part of an hour, and you still have to chase confirmations. Worse, the spreadsheet becomes a vague memory of what happened, not an audit trail of who did what and when.

The friction compounds. Here’s where it breaks down in real life.

Each company needs its own check, which means you repeat the same steps all week.
Due dates change or get interpreted inconsistently, so the sheet slowly drifts away from the truth.
Confirmations get buried in replies and chat messages, so you end up chasing people instead of doing actual work.
Miss one filing and you’re looking at roughly £150 to £1,500 in penalties, plus the awkward client conversation.

The Solution: daily deadline checks with one-click confirmations

This n8n workflow turns your Google Sheet into a living deadline tracker and turns Gmail into the daily “what needs attention” briefing. Every weekday at 5 PM, it reads your company list from Google Sheets, pulls fresh filing and confirmation deadlines from the Companies House API, then writes the latest due dates back into the same sheet. After that, it generates an HTML email that’s easy to scan: a sortable table, clear urgency colors (green to red), and Yes/No buttons next to each company. When someone clicks a button, a webhook receives the response and the workflow stamps the sheet with the confirmation and timestamp. That’s your audit trail, automatically.

The workflow starts on a weekday schedule. It refreshes deadlines from Companies House, builds a prioritized Gmail summary, then listens for Yes/No clicks to update Google Sheets in seconds. No extra tracker. No messy follow-ups.

What You Get: Automation vs. Results

What This Workflow Automates

Results You’ll Get

It reads your tracked company list directly from Google Sheets every weekday.
It fetches live filing deadline data from the Companies House API for each company.
It writes the newest due dates and a “last updated” timestamp back into the sheet.
It sends a single Gmail summary with color-coded urgency and clickable Yes/No buttons.

Most firms save about 2–3 hours a week compared to manual checks.
You walk into each day with a short, prioritized list instead of a spreadsheet scavenger hunt.
Fewer mistakes from copy-paste and “I thought you updated that” confusion.
A clean audit trail showing confirmation status and timestamps per company.
Lower risk of Companies House penalties, which also protects client trust.

Example: What This Looks Like

Say your firm tracks 30 companies. Manually, you might spend about 5 minutes per company to look up deadlines, update the sheet, and message the team, which is roughly 2.5 hours for a single full sweep (and it tends to happen more than once a week). With this workflow, you spend maybe 5 minutes maintaining the company list in Google Sheets, then the 5 PM Gmail summary arrives automatically and takes about 10 minutes to review. Confirmations are one click, and the timestamp lands in the sheet without anyone copying anything.

What You’ll Need

n8n instance (try n8n Cloud free)
Self-hosting option if you prefer (Hostinger works well)
Google Sheets for the company database and audit trail.
Gmail to send the daily deadline summary email.
Companies House API key (get it from api.company-information.service.gov.uk).

Skill level: Beginner. You’ll connect accounts, paste an API key, and confirm your webhook URL once.

Don’t want to set this up yourself? Talk to an automation expert (free 15-minute consultation).

How It Works

A weekday schedule triggers the run at 5 PM. That timing is intentional: you get a clean end-of-day snapshot before tomorrow’s work starts.

Your Google Sheet provides the company list. n8n reads the rows (company_number and company_name), so the sheet stays the source of truth for what’s being monitored.

Companies House due dates get refreshed automatically. For each company, the workflow calls the Companies House API, then syncs accounts_due and confirmation_due back into Google Sheets along with a last_updated timestamp.

Gmail delivers the summary and captures confirmations. The email is generated as HTML with urgency colors and Yes/No buttons; clicks go to an n8n webhook which writes confirmation_submitted (and the time) back to the same sheet.

You can easily modify the 5 PM schedule to a morning run, or change the email recipients and formatting, based on your needs. See the full implementation guide below for customization options.

Step-by-Step Implementation Guide

Step 1: Configure the Webhook Trigger

This workflow listens for confirmation updates from interactive email links and responds with a confirmation page.

Add and open Incoming Confirmation Webhook.
Set Path to confirmation-updates.
Set Response Mode to responseNode so Return Confirmation Page can handle responses.

⚠️ Common Pitfall: The email links in Compose Interactive Email depend on your public webhook URL. Replace https://[YOUR_WEBHOOK_DOMAIN]/webhook/confirmation-updates with your live n8n domain.

Step 2: Configure the Schedule Trigger

This workflow runs on weekdays to fetch and update company due dates.

Add and open Scheduled Weekday Trigger.
Set the cron rule to 0 17 * * 1-5 to run at 17:00 on weekdays.

Adjust the cron expression if you need a different timezone or send time.

Step 3: Connect Google Sheets

These nodes read the tracking database and update due dates and confirmation status.

Open Retrieve Company Sheet and select the Document with [YOUR_ID] and Sheet with [YOUR_ID].
Credential Required: Connect your googleSheetsOAuth2Api credentials.
Open Sync Due Dates to Sheet and confirm Operation is appendOrUpdate.
Map columns in Sync Due Dates to Sheet using expressions like {{$json.accounts.next_due}}, {{$json.company_name}}, {{$json.company_number}}, {{$json.confirmation_statement.next_due}}, and {{$json.confirmation_statement.last_made_up_to}}.
Credential Required: Connect your googleSheetsOAuth2Api credentials for Sync Due Dates to Sheet.
Open Modify Sheet Records and confirm Operation is update with Matching Columns set to company_number.
Set column expressions in Modify Sheet Records to {{$json.query.company_name}}, {{$json.query.company_number}}, and {{$json.query.confirmation_submitted}}.
Credential Required: Connect your googleSheetsOAuth2Api credentials for Modify Sheet Records.

Step 4: Set Up API Enrichment and Email Composition

This step enriches companies with Companies House data and builds the HTML email.

Open Fetch Company Profile and set URL to =https://api.company-information.service.gov.uk/company/{{ $json.company_number }}.
Credential Required: Connect your httpBasicAuth credentials for Fetch Company Profile.
Open Compose Interactive Email and review the JavaScript. Ensure the webhook base URL is set to your public domain: https://[YOUR_WEBHOOK_DOMAIN]/webhook/confirmation-updates.
Keep the output fields from Compose Interactive Email as emailBody and subject for downstream email sending.

Step 5: Configure Output and Confirmation Response

Emails are sent via Gmail and the webhook responds with a confirmation page after updates.

Open Dispatch Email via Gmail and set Send To to [YOUR_EMAIL].
Set Message to {{$json.emailBody}} and Subject to {{$json.subject}}.
Credential Required: Connect your gmailOAuth2 credentials.
Open Return Confirmation Page and keep Respond With set to text.
Set Response Body to the HTML expression shown, beginning with =<!DOCTYPE html> and including {{ $json.company_name }} and {{ $json.confirmation_submitted }}.

Step 6: Test & Activate Your Workflow

Verify both scheduled and webhook-driven paths before activating.

Click Execute Workflow on Scheduled Weekday Trigger to run the scheduled path manually.
Confirm data flows in order: Scheduled Weekday Trigger → Retrieve Company Sheet → Fetch Company Profile → Sync Due Dates to Sheet → Compose Interactive Email → Dispatch Email via Gmail.
Trigger Incoming Confirmation Webhook by opening a generated confirmation link and verify the flow: Incoming Confirmation Webhook → Transform Webhook Payload → Modify Sheet Records → Return Confirmation Page.
A successful test updates the Google Sheet record and displays the confirmation HTML page.
Turn on the workflow using the Active toggle to enable weekday automation.

🔒

Unlock Full Step-by-Step Guide

Get the complete implementation guide + downloadable template

Common Gotchas

Google Sheets credentials can expire or need specific permissions. If things break, check the n8n credentials screen and the sheet’s sharing access first.
If you’re using Wait nodes or external rendering, processing times vary. Bump up the wait duration if downstream nodes fail on empty responses.
Default prompts in AI nodes are generic. Add your brand voice early or you’ll be editing outputs forever.

Frequently Asked Questions

How long does it take to set up this Sheets Gmail deadlines automation?

About 15 minutes once your credentials are ready.

Do I need coding skills to automate filing deadlines?

No. You’ll connect Google Sheets, Gmail, and paste your Companies House API key.

Is n8n free to use for this Sheets Gmail deadlines workflow?

Yes. n8n has a free self-hosted option and a free trial on n8n Cloud. Cloud plans start at $20/month for higher volume. You’ll also need to factor in Companies House API usage (the key is free).

Where can I host n8n to run this automation?

Two options: n8n Cloud (managed, easiest setup) or self-hosting on a VPS. For self-hosting, Hostinger VPS is affordable and handles n8n well. Self-hosting gives you unlimited executions but requires basic server management.

Can I customize this Sheets Gmail deadlines workflow for different recipients or a different schedule?

Yes, and it’s straightforward. Change the Scheduled Weekday Trigger if you want morning runs, and update the Dispatch Email via Gmail node to add partners, managers, or a shared mailbox. You can also tweak the Compose Interactive Email code to sort by a different column, add a “client manager” field from Google Sheets, or adjust the red/orange/yellow/green thresholds so “urgent” matches how your firm actually operates.

Why is my Google Sheets connection failing in this workflow?

Usually it’s expired Google OAuth permissions or the connected Google account no longer has access to the spreadsheet. Reconnect the Google Sheets credential in n8n, then open the sheet and confirm it’s shared with the same account. If you duplicated the workflow, double-check you also updated the spreadsheet ID in the Google Sheets nodes.

How many companies can this Sheets Gmail deadlines automation handle?

Dozens is normal, and many teams run 50+ without issues. On n8n Cloud Starter, you’re limited by monthly executions, so you’ll want to keep an eye on how many rows you process each day. If you self-host, there’s no execution cap; capacity depends on your server and the speed of the Companies House API responses.

Is this Sheets Gmail deadlines automation better than using Zapier or Make?

Often, yes, because this workflow mixes scheduled batching, HTML email generation, and a webhook “response handler” that writes back to the sheet. Zapier and Make can do parts of it, but the logic tends to sprawl into multiple scenarios, which gets pricey and harder to audit. n8n also lets you self-host for unlimited runs, which matters if you’re checking lots of companies daily. If you only need a simple reminder email from a static sheet, Zapier can be fine. Talk to an automation expert if you want help choosing.

Once this is running, the spreadsheet stays accurate and the team gets a daily prompt that’s hard to ignore. Honestly, that’s a calmer week for everyone.

Need Help Setting This Up?

Our automation experts can build and customize this workflow for your specific needs. Free 15-minute consultation—no commitment required.

Lisa Granqvist

Workflow Automation Expert

Expert in workflow automation and no-code tools.

{
"id": null,
"meta": {
"instanceId": "81f5661417398f340548675dd926ba8b13fac0d1d26e26cb530887ec4c2fad50",
"templateId": null,
"templateCredsSetupCompleted": null
},
"name": "Automated Filing Deadline Alerts",
"tags": [],
"nodes": [
{
"id": "flowpast-topbar-10707",
"name": "Flowpast Branding",
"type": "n8n-nodes-base.stickyNote",
"position": [
0,
20
],
"parameters": {
"color": 7,
"width": 1115,
"height": 80,
"content": "## Flowpast.com | Automation Workflow Library\n**\ud83d\udcd6 Full tutorial & setup guide:** flowpast.com"
},
"typeVersion": 1
},
{
"id": "6fafe18a-6fd9-4371-8832-3344c0c04b81",
"name": "Incoming Confirmation Webhook",
"type": "n8n-nodes-base.webhook",
"position": [
990,
450
],
"webhookId": "aa5c51e0-2405-4161-8635-086e329929b8",
"parameters": {
"path": "confirmation-updates",
"options": [],
"responseMode": "responseNode"
},
"typeVersion": 1
},
{
"id": "ad9fbc87-7e24-4fd1-bf87-48b800c258f2",
"name": "Retrieve Company Sheet",
"type": "n8n-nodes-base.googleSheets",
"position": [
700,
225
],
"parameters": {
"options": [],
"sheetName": {
"__rl": true,
"mode": "list",
"value": "[YOUR_ID]",
"cachedResultName": "Sheet2"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "[YOUR_ID]",
"cachedResultName": "Company Tracking Database"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.4
},
{
"id": "33c526d5-7b2e-4fe5-83a6-9573aa6b82cf",
"name": "Compose Interactive Email",
"type": "n8n-nodes-base.code",
"position": [
0,
145
],
"parameters": {
"jsCode": "const items = $input.all();\n\nconst webhookBaseUrl = 'https://[YOUR_WEBHOOK_DOMAIN]/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><link rel="preload" as="image" href="https://flowpast.s3.eu-north-1.amazonaws.com/featured_blog_images/106882.webp"><link rel="preload" as="image" href="https://flowpast.s3.eu-north-1.amazonaws.com/featured_blog_images/106882.webp"></head>
<body>';\n\nemailBody += '<div style=\"background-color: white; padding: 20px; border-radius: 8px; margin-bottom: 20px;\">';\nemailBody += '<h2 style=\"color: #1976d2;\">\ud83d\udccb 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 = '\u2705';\n  let statusText = 'OK';\n  \n  if (accountsInfo.label === 'OVERDUE' || confirmationInfo.label === 'OVERDUE') {\n    statusIcon = '\ud83d\udd34';\n    statusText = 'OVERDUE';\n  } else if (accountsInfo.label === 'URGENT' || confirmationInfo.label === 'URGENT') {\n    statusIcon = '\ud83d\udfe0';\n    statusText = 'URGENT';\n  } else if (accountsInfo.label === 'SOON' || confirmationInfo.label === 'SOON') {\n    statusIcon = '\ud83d\udfe1';\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\">\u2713 Yes</a>`;\n  emailBody += `<a href=\"${noUrl}\" class=\"btn btn-no\">\u2717 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;\">\ud83d\udd34 Red = Overdue | \ud83d\udfe0 Orange = Due within 7 days | \ud83d\udfe1 Yellow = Due within 30 days | \u2705 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": "Transform Webhook Payload",
"type": "n8n-nodes-base.code",
"position": [
650,
545
],
"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": "Modify Sheet Records",
"type": "n8n-nodes-base.googleSheets",
"position": [
455,
460
],
"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": "[YOUR_ID]",
"cachedResultName": "Sheet2"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "[YOUR_ID]",
"cachedResultName": "Company Tracking Database"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.4
},
{
"id": "42a5efc8-53ea-4fe5-832a-c24a476052cb",
"name": "Return Confirmation Page",
"type": "n8n-nodes-base.respondToWebhook",
"position": [
125,
555
],
"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<link rel="preload" as="image" href="https://flowpast.s3.eu-north-1.amazonaws.com/featured_blog_images/106882.webp"><link rel="preload" as="image" href="https://flowpast.s3.eu-north-1.amazonaws.com/featured_blog_images/106882.webp"></head>
\n<body>\n  <div class=\"container\">\n    <div class=\"success\">\u2713</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": "Scheduled Weekday Trigger",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
1015,
160
],
"parameters": {
"rule": {
"interval": [
{
"field": "cronExpression",
"expression": "0 17 * * 1-5"
}
]
}
},
"typeVersion": 1.1
},
{
"id": "08a752c6-2e29-48f5-b760-d1a349e37c57",
"name": "Dispatch Email via Gmail",
"type": "n8n-nodes-base.gmail",
"position": [
205,
360
],
"webhookId": "c23fb0cc-dfea-4a01-b1a0-975eb12d10a4",
"parameters": {
"sendTo": "[YOUR_EMAIL]",
"message": "={{ $json.emailBody }}",
"options": {
"appendAttribution": false
},
"subject": "={{ $json.subject }}"
},
"credentials": {
"gmailOAuth2": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 2.1
},
{
"id": "0d8ad3eb-adce-46bf-81ea-e1bff8f77195",
"name": "Fetch Company Profile",
"type": "n8n-nodes-base.httpRequest",
"position": [
505,
120
],
"parameters": {
"url": "=https://api.company-information.service.gov.uk/company/{{ $json.company_number }}",
"options": [],
"authentication": "genericCredentialType",
"genericAuthType": "httpBasicAuth"
},
"credentials": {
"httpBasicAuth": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.1
},
{
"id": "0e591128-9b31-43d1-9777-2d2f8de4dbdf",
"name": "Sync Due Dates to Sheet",
"type": "n8n-nodes-base.googleSheets",
"position": [
190,
225
],
"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": "[YOUR_ID]",
"cachedResultName": "Sheet2"
},
"documentId": {
"__rl": true,
"mode": "list",
"value": "[YOUR_ID]",
"cachedResultName": "Company Tracking Database"
}
},
"credentials": {
"googleSheetsOAuth2Api": {
"id": "credential-id",
"name": ""
}
},
"typeVersion": 4.7
}
],
"active": false,
"pinData": [],
"settings": {
"executionOrder": "v1"
},
"versionId": null,
"connections": {
"Fetch Company Profile": {
"main": [
[
{
"node": "Sync Due Dates to Sheet",
"type": "main",
"index": 0
}
]
]
},
"Scheduled Weekday Trigger": {
"main": [
[
{
"node": "Retrieve Company Sheet",
"type": "main",
"index": 0
}
]
]
},
"Transform Webhook Payload": {
"main": [
[
{
"node": "Modify Sheet Records",
"type": "main",
"index": 0
}
]
]
},
"Retrieve Company Sheet": {
"main": [
[
{
"node": "Fetch Company Profile",
"type": "main",
"index": 0
}
]
]
},
"Compose Interactive Email": {
"main": [
[
{
"node": "Dispatch Email via Gmail",
"type": "main",
"index": 0
}
]
]
},
"Sync Due Dates to Sheet": {
"main": [
[
{
"node": "Compose Interactive Email",
"type": "main",
"index": 0
}
]
]
},
"Modify Sheet Records": {
"main": [
[
{
"node": "Return Confirmation Page",
"type": "main",
"index": 0
}
]
]
},
"Incoming Confirmation Webhook": {
"main": [
[
{
"node": "Transform Webhook Payload",
"type": "main",
"index": 0
}
]
]
}
}
}