{
  "name": "Send Weekly Overdue Invoice Reminders with n8n (free JSON)",
  "nodes": [
    {
      "id": "9209c319-41a0-4b50-a40c-6a728f665afb",
      "name": "Overview",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -500,
        -440
      ],
      "parameters": {
        "color": 7,
        "width": 420,
        "height": 700,
        "content": "## Weekly reminders for overdue invoices\n\nEvery Monday at 08:00 this workflow reads an invoice tracker in Google Sheets, picks the invoices that are still marked Unpaid, have no chase date and were issued more than 7 days ago, emails each client one HTML reminder through Gmail, then marks the row as Chased with today's date.\n\n### How it works\n\n1. Every Monday 08:00: weekly schedule.\n2. Configure: holds the spreadsheet address and the number of days (7).\n3. Read Invoice Sheet: reads all rows of the first tab.\n4. Capture Row Index: stores each row's sheet row number.\n5. Filter Unpaid Invoices: keeps Status = Unpaid with an empty Chase_Date.\n6. Calculate Days Overdue: counts the days since Invoice_Date (not since a due date).\n7. Filter Overdue 7+ Days: keeps rows where that count is greater than 7.\n8. Send Reminder Email: sends the reminder to the address in Email.\n9. Prepare Row Update: gets the row number back.\n10. Update Invoice Status: writes Chased and today's date.\n\n### Setup\n\n1. Connect your Google Sheets and Gmail credentials.\n2. In Configure, replace YOUR_SPREADSHEET_URL with your spreadsheet address.\n3. Row 1 of the first tab needs these headers: Client, Email, Amount, Invoice_Date, Status, Chase_Date.\n4. Status must be exactly Unpaid. Invoice_Date must be text in the form dd/MM/yyyy.\n5. Test on a copy of the sheet with your own address in Email before turning it on.\n\n### Customize\n\n- The number of days is overdueThreshold in Configure.\n- Day and time: the schedule node.\n- Wording: the message field of Send Reminder Email.\n\nSource: based on a template from the n8n Library at n8n.io, documented by LeenLabs.\nNot tested on live APIs."
      },
      "typeVersion": 1
    },
    {
      "id": "d523135e-eb66-4893-a55c-a0a322b8274e",
      "name": "Trigger & Configure",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -60,
        -180
      ],
      "parameters": {
        "color": 1,
        "width": 460,
        "height": 480,
        "content": "## Trigger and settings\n\nRuns every Monday at 08:00, in the timezone set in your n8n instance. Configure holds the spreadsheet address (YOUR_SPREADSHEET_URL must be replaced) and the number of days, 7."
      },
      "typeVersion": 1
    },
    {
      "id": "5dfdd87b-c662-485a-b491-8d3894068d3c",
      "name": "Read Data",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        428,
        -180
      ],
      "parameters": {
        "color": 1,
        "width": 420,
        "height": 480,
        "content": "## Read data\n\nReads all rows of the first tab. Capture Row Index then stores each row's position (item number + 2, which assumes headers in row 1 and no blank rows) so the right row can be updated later."
      },
      "typeVersion": 1
    },
    {
      "id": "62f6a266-c5dc-4bba-b2ce-e25c8b2291f0",
      "name": "Filter Overdue",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        876,
        -180
      ],
      "parameters": {
        "color": 1,
        "width": 660,
        "height": 480,
        "content": "## Filter overdue\n\nKeeps rows with Status exactly Unpaid and an empty Chase_Date. Then counts the days since Invoice_Date (format dd/MM/yyyy) and keeps rows where that number is greater than the threshold. This is time since the invoice date, not since a due date."
      },
      "typeVersion": 1
    },
    {
      "id": "69ee17f8-29ac-4478-b390-702faba05c7a",
      "name": "Notify & Update",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1548,
        -180
      ],
      "parameters": {
        "color": 1,
        "width": 660,
        "height": 480,
        "content": "## Notify and update\n\nSends one HTML email per selected row to the address in Email, then sets Status to Chased and Chase_Date to today. A row is chased only once. If the email is sent but the sheet update fails, the client may get a second reminder next Monday."
      },
      "typeVersion": 1
    },
    {
      "id": "5f99d986-89a8-4bc9-b12f-2189093b646a",
      "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
    },
    {
      "id": "51aada92-f2f9-44f0-b8b6-4dd17ec7a0f4",
      "name": "Configure",
      "type": "n8n-nodes-base.set",
      "position": [
        224,
        0
      ],
      "parameters": {
        "mode": "manual",
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "cfg-sheet-url",
              "name": "sheetUrl",
              "type": "string",
              "value": "YOUR_SPREADSHEET_URL"
            },
            {
              "id": "cfg-threshold",
              "name": "overdueThreshold",
              "type": "number",
              "value": 7
            }
          ]
        },
        "includeOtherFields": false
      },
      "typeVersion": 3.4
    },
    {
      "id": "fbbdb8cc-1c5c-4a6e-a148-30b4408dc8dd",
      "name": "Read Invoice Sheet",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        448,
        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
    },
    {
      "id": "f2ae0213-5964-4f48-a14e-74181ea80347",
      "name": "Capture Row Index",
      "type": "n8n-nodes-base.set",
      "position": [
        672,
        0
      ],
      "parameters": {
        "mode": "manual",
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "assign-row-number",
              "name": "row_number",
              "type": "number",
              "value": "={{ $itemIndex + 2 }}"
            }
          ]
        },
        "includeOtherFields": true
      },
      "typeVersion": 3.4
    },
    {
      "id": "e09a5ea4-35f8-4c13-a15f-47f22ca1f8a6",
      "name": "Filter Unpaid Invoices",
      "type": "n8n-nodes-base.filter",
      "position": [
        896,
        0
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 3,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "cond-statut",
              "operator": {
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.Status }}",
              "rightValue": "Unpaid"
            },
            {
              "id": "cond-relance",
              "operator": {
                "type": "string",
                "operation": "empty",
                "singleValue": true
              },
              "leftValue": "={{ $json['Chase_Date'] }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.3
    },
    {
      "id": "2f232b6d-a960-45d3-8e87-e22e3db9a654",
      "name": "Calculate Days Overdue",
      "type": "n8n-nodes-base.set",
      "position": [
        1120,
        0
      ],
      "parameters": {
        "mode": "manual",
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "assign-retard",
              "name": "joursRetard",
              "type": "number",
              "value": "={{ Math.floor($now.diff(DateTime.fromFormat($json.Invoice_Date, 'dd/MM/yyyy'), 'days').days) }}"
            }
          ]
        },
        "includeOtherFields": true
      },
      "typeVersion": 3.4
    },
    {
      "id": "b9ca502e-0745-4071-bc9c-7e8986e414d3",
      "name": "Filter Overdue 7+ Days",
      "type": "n8n-nodes-base.filter",
      "position": [
        1344,
        0
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 3,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "loose"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "cond-retard",
              "operator": {
                "type": "number",
                "operation": "gt"
              },
              "leftValue": "={{ $json.joursRetard }}",
              "rightValue": "={{ $('Configure').first().json.overdueThreshold }}"
            }
          ]
        }
      },
      "typeVersion": 2.3
    },
    {
      "id": "6de52584-3429-4489-a78c-2baa389fe72d",
      "name": "Send Reminder Email",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1568,
        0
      ],
      "parameters": {
        "sendTo": "={{ $json.Email }}",
        "message": "={{ \"<html><body style=\\\"font-family:Arial,sans-serif\\\"><p>Dear <strong>\" + $json.Client + \"</strong>,</p><p>This is a friendly reminder that the following invoice remains outstanding.</p><table border=\\\"1\\\" cellpadding=\\\"8\\\"><tr><td>Invoice date</td><td>\" + $json.Invoice_Date + \"</td></tr><tr><td>Amount due</td><td>\" + $json.Amount + \"</td></tr><tr><td>Days overdue</td><td>\" + $json.joursRetard + \" days</td></tr></table><p>Please arrange payment at your earliest convenience.</p><p>Best regards</p></body></html>\" }}",
        "options": {
          "appendAttribution": false
        },
        "subject": "={{ \"Payment reminder — Invoice dated \" + $json.Invoice_Date }}",
        "resource": "message",
        "emailType": "html",
        "operation": "send"
      },
      "typeVersion": 2.2
    },
    {
      "id": "29bf23cc-0c96-43f4-ae31-81b8459d0acd",
      "name": "Prepare Row Update",
      "type": "n8n-nodes-base.set",
      "position": [
        1792,
        0
      ],
      "parameters": {
        "mode": "manual",
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "restore-row",
              "name": "row_number",
              "type": "number",
              "value": "={{ $('Filter Overdue 7+ Days').item.json.row_number }}"
            },
            {
              "id": "restore-client",
              "name": "Client",
              "type": "string",
              "value": "={{ $('Filter Overdue 7+ Days').item.json.Client }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "efe3881b-edd9-42fe-8168-e981ed3193ea",
      "name": "Update Invoice Status",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        2016,
        0
      ],
      "parameters": {
        "columns": {
          "value": {
            "Status": "Chased",
            "Chase_Date": "={{ $now.toFormat('dd/MM/yyyy') }}",
            "row_number": "={{ $json.row_number }}"
          },
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "row_number"
          ]
        },
        "options": {},
        "resource": "sheet",
        "operation": "update",
        "sheetName": {
          "__rl": true,
          "mode": "id",
          "value": 0
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "={{ $('Configure').first().json.sheetUrl }}"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "4f7a2c19-8d36-4b05-9e71-a3c60d5b8e24",
      "name": "Before you turn it on",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -500,
        -1180
      ],
      "parameters": {
        "color": 3,
        "width": 420,
        "height": 700,
        "content": "## Before you turn it on\n\n1. This sends real emails to your clients with no human review. Test on a copy of the sheet with your own address in the Email column.\n2. Days are counted from the invoice date, not from a due date. Payment terms are ignored.\n3. Each invoice is reminded once. After that Chase_Date is filled and it is never chased again.\n4. Rows are silently skipped when Status is not exactly Unpaid or Invoice_Date is not in the form dd/MM/yyyy.\n5. The row number is item position + 2. Blank rows or headers not in row 1 can make it update the wrong row.\n6. If a reminder is sent but the sheet update fails, the client can be reminded again next Monday.\n7. The sheet is chosen by id 0, normally the first tab. Check it is the right one.\n8. The email has no invoice number, due date, payment details or sender name. Edit it before use.\n9. Check the data protection and debt collection rules that apply to you before you contact clients automatically."
      },
      "typeVersion": 1
    }
  ],
  "connections": {
    "Configure": {
      "main": [
        [
          {
            "node": "Read Invoice Sheet",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Capture Row Index": {
      "main": [
        [
          {
            "node": "Filter Unpaid Invoices",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Every Monday 08:00": {
      "main": [
        [
          {
            "node": "Configure",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Prepare Row Update": {
      "main": [
        [
          {
            "node": "Update Invoice Status",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read Invoice Sheet": {
      "main": [
        [
          {
            "node": "Capture Row Index",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Send Reminder Email": {
      "main": [
        [
          {
            "node": "Prepare Row Update",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Calculate Days Overdue": {
      "main": [
        [
          {
            "node": "Filter Overdue 7+ Days",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter Overdue 7+ Days": {
      "main": [
        [
          {
            "node": "Send Reminder Email",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Filter Unpaid Invoices": {
      "main": [
        [
          {
            "node": "Calculate Days Overdue",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  },
  "settings": {
    "executionOrder": "v1"
  }
}