{ "name": "Send Escalating Overdue Invoice Reminders with n8n (free JSON)", "nodes": [ { "id": "4902b93f-a3cb-421a-ab51-7c099a8d0312", "name": "Overview", "type": "n8n-nodes-base.stickyNote", "position": [ -480, -192 ], "parameters": { "width": 480, "height": 1000, "content": "## Escalating reminders for overdue invoices\n\nEvery day at 9:00 this workflow reads an invoice tracker in Google Sheets. For each invoice marked unpaid that is at least 3 days past its due date and has not been reminded in the last 7 days, it picks a tier from the number of days overdue (1: under 14 days, 2: 14 to 29 days, 3: 30 days or more), writes the email, sends it to the client through Gmail, and records the reminder count and date in the sheet. For tier 3 it also emails you an alert. Nothing checks whether the client has actually paid.\n\n### How it works\n\n1. When Clock Strikes 9AM: daily schedule.\n2. Fetch Invoice Data from Sheets: reads every row of the Invoices tab.\n3. Draft Escalation Emails: code that filters, picks the tier and writes subject and body.\n4. Dispatch Reminder Emails: sends the email to ClientEmail.\n5. Update Invoice Status in Sheets: writes RemindersSent and LastReminderDate to the same row.\n6. Check Final Escalation Tier: passes tier 3 only.\n7. Notify Escalation Alert via Gmail: emails you.\n\n### Setup\n\n1. Connect Google Sheets and Gmail credentials.\n2. Replace YOUR_GOOGLE_SHEET_ID in both Google Sheets nodes.\n3. Replace YOUR_EMAIL_ADDRESS in Notify Escalation Alert via Gmail.\n4. Create the Invoices tab with the columns in the note Required sheet columns.\n5. Read and rewrite the three email texts in the code node. They make promises on your behalf.\n6. Check the timezone of the schedule and of your n8n instance.\n7. Test on a copy of the sheet with your own address in ClientEmail.\n\n### Customize\n\n- Tier limits (3, 14, 30 days) and the 7-day pause: constants in the code node.\n- Wording: the three templates in the code node.\n\nSource: based on a template from the n8n Library at n8n.io, documented by LeenLabs.\nNot tested on live APIs." }, "typeVersion": 1 }, { "id": "b5f73b37-2fd7-4803-bd5a-929d4d4d64f0", "name": "Sticky Note1", "type": "n8n-nodes-base.stickyNote", "position": [ 80, -96 ], "parameters": { "color": 7, "width": 640, "height": 320, "content": "## Schedule and prepare reminders\n\nStarts the workflow every morning, reads the invoice tracker from Google Sheets, and classifies unpaid invoices into reminder tiers while drafting the appropriate email content." }, "typeVersion": 1 }, { "id": "93ea7e41-006b-49c8-829a-9e160990a50b", "name": "Sticky Note2", "type": "n8n-nodes-base.stickyNote", "position": [ 752, -192 ], "parameters": { "color": 7, "width": 416, "height": 320, "content": "## Send and log reminders\n\nSends the customer reminder emails through Gmail, then records the reminder activity back into the invoice tracker." }, "typeVersion": 1 }, { "id": "06889b0d-92fc-4892-a3ac-1be5accdbfc1", "name": "Sticky Note3", "type": "n8n-nodes-base.stickyNote", "position": [ 752, 160 ], "parameters": { "color": 7, "width": 416, "height": 320, "content": "## Handle final escalation\n\nChecks whether an invoice has reached the final notice tier and, when applicable, sends an internal escalation alert by Gmail." }, "typeVersion": 1 }, { "id": "node-schedule", "name": "When Clock Strikes 9AM", "type": "n8n-nodes-base.scheduleTrigger", "position": [ 120, 60 ], "parameters": { "rule": { "interval": [ { "field": "days", "triggerAtHour": 9 } ] } }, "typeVersion": 1.2 }, { "id": "node-read-sheet", "name": "Fetch Invoice Data from Sheets", "type": "n8n-nodes-base.googleSheets", "position": [ 340, 60 ], "parameters": { "options": {}, "sheetName": { "__rl": true, "mode": "name", "value": "Invoices" }, "documentId": { "__rl": true, "mode": "id", "value": "YOUR_GOOGLE_SHEET_ID" } }, "typeVersion": 4.5 }, { "id": "node-build", "name": "Draft Escalation Emails", "type": "n8n-nodes-base.code", "position": [ 560, 60 ], "parameters": { "jsCode": "// Classify every unpaid invoice into an escalation tier and draft the email.\n// Edit the three email templates below to match your voice.\n\nconst REMIND_EVERY_DAYS = 7; // never email the same client more often than this\nconst today = new Date();\ntoday.setHours(0, 0, 0, 0);\n\nconst out = [];\n\nfor (const item of $input.all()) {\n const inv = item.json;\n\n if (String(inv.Status || '').trim().toLowerCase() !== 'unpaid') continue;\n if (!inv.DueDate || !inv.ClientEmail) continue;\n\n const due = new Date(inv.DueDate);\n if (isNaN(due)) continue;\n due.setHours(0, 0, 0, 0);\n\n const daysOverdue = Math.floor((today - due) / 86400000);\n if (daysOverdue < 3) continue; // small grace period\n\n // throttle: skip if a reminder went out recently\n if (inv.LastReminderDate) {\n const last = new Date(inv.LastReminderDate);\n if (!isNaN(last) && (today - last) / 86400000 < REMIND_EVERY_DAYS) continue;\n }\n\n const tier = daysOverdue >= 30 ? 3 : daysOverdue >= 14 ? 2 : 1;\n const amount = `${inv.Amount} ${inv.Currency || ''}`.trim();\n const firstName = String(inv.ClientName || 'there').split(' ')[0];\n\n let subject, body;\n\n if (tier === 1) {\n subject = `Friendly reminder: invoice ${inv.InvoiceNumber} (${amount})`;\n body = `Hi ${firstName},\\n\\nJust a quick note that invoice ${inv.InvoiceNumber} for ${amount} was due on ${inv.DueDate} and looks unpaid on our side.\\n\\nIf you've already sent the payment — thank you, please ignore this. Otherwise it would be great if you could settle it this week.\\n\\nIf anything is unclear about the invoice, just reply to this email.\\n\\nBest regards`;\n } else if (tier === 2) {\n subject = `Second reminder: invoice ${inv.InvoiceNumber} is ${daysOverdue} days overdue`;\n body = `Hi ${firstName},\\n\\nInvoice ${inv.InvoiceNumber} for ${amount} is now ${daysOverdue} days past its due date (${inv.DueDate}).\\n\\nPlease arrange payment within the next 5 business days, or let me know when we can expect it.\\n\\nIf there is an issue with the invoice or the work, reply here and we'll sort it out quickly.\\n\\nBest regards`;\n } else {\n subject = `FINAL NOTICE: invoice ${inv.InvoiceNumber} — ${daysOverdue} days overdue`;\n body = `Hi ${firstName},\\n\\nDespite previous reminders, invoice ${inv.InvoiceNumber} for ${amount} remains unpaid ${daysOverdue} days after its due date (${inv.DueDate}).\\n\\nPlease settle the amount within 7 days. After that we will have to pause ongoing work and may add late-payment charges as per our terms.\\n\\nI would much rather resolve this simply — if payment is already on its way, a quick reply is enough.\\n\\nRegards`;\n }\n\n out.push({\n json: {\n row_number: inv.row_number,\n InvoiceNumber: inv.InvoiceNumber,\n ClientName: inv.ClientName,\n ClientEmail: inv.ClientEmail,\n Amount: inv.Amount,\n Currency: inv.Currency || '',\n DueDate: inv.DueDate,\n daysOverdue,\n tier,\n subject,\n body,\n remindersSent: (parseInt(inv.RemindersSent, 10) || 0) + 1,\n todayISO: today.toISOString().slice(0, 10),\n },\n });\n}\n\nreturn out;" }, "typeVersion": 2 }, { "id": "node-send", "name": "Dispatch Reminder Emails", "type": "n8n-nodes-base.gmail", "position": [ 800, -40 ], "parameters": { "sendTo": "={{ $json.ClientEmail }}", "message": "={{ $json.body }}", "options": {}, "subject": "={{ $json.subject }}", "emailType": "text" }, "typeVersion": 2.1 }, { "id": "node-log", "name": "Update Invoice Status in Sheets", "type": "n8n-nodes-base.googleSheets", "position": [ 1020, -40 ], "parameters": { "columns": { "value": { "row_number": "={{ $json.row_number }}", "RemindersSent": "={{ $json.remindersSent }}", "LastReminderDate": "={{ $json.todayISO }}" }, "schema": [], "mappingMode": "defineBelow", "matchingColumns": [ "row_number" ] }, "options": {}, "operation": "update", "sheetName": { "__rl": true, "mode": "name", "value": "Invoices" }, "documentId": { "__rl": true, "mode": "id", "value": "YOUR_GOOGLE_SHEET_ID" } }, "typeVersion": 4.5 }, { "id": "node-if-final", "name": "Check Final Escalation Tier", "type": "n8n-nodes-base.if", "position": [ 800, 320 ], "parameters": { "options": {}, "conditions": { "options": { "version": 2, "leftValue": "", "caseSensitive": true, "typeValidation": "strict" }, "combinator": "and", "conditions": [ { "id": "cond-final", "operator": { "type": "number", "operation": "equals" }, "leftValue": "={{ $json.tier }}", "rightValue": 3 } ] } }, "typeVersion": 2.2 }, { "id": "node-alert", "name": "Notify Escalation Alert via Gmail", "type": "n8n-nodes-base.gmail", "position": [ 1024, 320 ], "parameters": { "sendTo": "YOUR_EMAIL_ADDRESS", "message": "={{ 'Final notice was just sent to ' + $json.ClientName + ' (' + $json.ClientEmail + ') for invoice ' + $json.InvoiceNumber + ', amount ' + $json.Amount + ' ' + $json.Currency + ', due ' + $json.DueDate + ' (' + $json.daysOverdue + ' days overdue, reminder #' + $json.remindersSent + ').\\n\\nConsider pausing work for this client or handing the case to collections.' }}", "options": {}, "subject": "={{ '🚨 Escalation: ' + $json.ClientName + ' — ' + $json.InvoiceNumber + ' is ' + $json.daysOverdue + ' days overdue' }}", "emailType": "text" }, "typeVersion": 2.1 }, { "id": "sticky-02", "name": "Required sheet columns", "type": "n8n-nodes-base.stickyNote", "position": [ -480, 656 ], "parameters": { "width": 380, "height": 460, "content": "## Required sheet columns (tab name: Invoices)\n\nRow 1 must hold these headers, spelled exactly:\n\n- InvoiceNumber (example: INV-2041)\n- ClientName (example: Jane Smith; only the first word is used in the greeting)\n- ClientEmail\n- Amount (example: 1250.00)\n- Currency (example: USD)\n- DueDate (example: 2026-07-15, written as year-month-day text)\n- Status (unpaid or paid)\n- LastReminderDate (filled by the workflow)\n- RemindersSent (filled by the workflow)\n\nSet Status to paid by hand as soon as a payment arrives. Only rows with Status unpaid (any capitals) are processed." }, "typeVersion": 1 }, { "id": "2b9e5d70-6a14-4c83-8f3b-d17a0e94c562", "name": "Before you turn it on", "type": "n8n-nodes-base.stickyNote", "position": [ -64, 656 ], "parameters": { "color": 3, "width": 640, "height": 820, "content": "## Before you turn it on\n\n1. This emails your clients every day with no human review. Test on a copy of the sheet with your own address in ClientEmail.\n2. Nothing checks payments. A client who paid but is not yet marked paid in the sheet gets a reminder, possibly a final notice.\n3. The tier comes from days overdue, not from reminders already sent. An invoice that is 30 days overdue the first time the workflow sees it gets a FINAL NOTICE as its first email, which says Despite previous reminders.\n4. Tier 3 repeats every 7 days, with an alert to you each time, until you change Status.\n5. If the sheet update fails after an email is sent, LastReminderDate is not written and the client can be emailed again the next day.\n6. Dates: DueDate must be readable as year-month-day. Other forms are skipped without any message or misread. In some timezones the code is off by one day (see the article).\n7. The alert address in Notify Escalation Alert via Gmail is a placeholder (YOUR_EMAIL_ADDRESS). Replace it, or you will not receive the final-notice alerts.\n8. The email texts mention pausing work, late-payment charges and fixed payment deadlines. Edit them to match your real terms and the rules that apply to you.\n9. Row numbers come from the read step. If they are missing, the update step fails.\n10. Not tested on live APIs." }, "typeVersion": 1 } ], "connections": { "When Clock Strikes 9AM": { "main": [ [ { "node": "Fetch Invoice Data from Sheets", "type": "main", "index": 0 } ] ] }, "Draft Escalation Emails": { "main": [ [ { "node": "Dispatch Reminder Emails", "type": "main", "index": 0 }, { "node": "Check Final Escalation Tier", "type": "main", "index": 0 } ] ] }, "Dispatch Reminder Emails": { "main": [ [ { "node": "Update Invoice Status in Sheets", "type": "main", "index": 0 } ] ] }, "Check Final Escalation Tier": { "main": [ [ { "node": "Notify Escalation Alert via Gmail", "type": "main", "index": 0 } ] ] }, "Fetch Invoice Data from Sheets": { "main": [ [ { "node": "Draft Escalation Emails", "type": "main", "index": 0 } ] ] } }, "settings": { "executionOrder": "v1" } }