Send Monthly Consolidated Customer Statements From Google Sheets with Gmail

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "name": "Send monthly customer account statements from Google Sheets with Gmail",
  "nodes": [
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000001",
      "name": "Sticky Note - read me first",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -860,
        -80
      ],
      "parameters": {
        "color": 1,
        "width": 700,
        "height": 860,
        "content": "## One statement per customer, on the 1st of every month\n\n### How it works\nOn the 1st of each month this reads your `invoices` sheet, keeps only rows it can safely bill (open status, readable due date, positive amount, valid email), groups them by customer, and sends each customer **one** statement listing every open invoice with an ageing summary (Not yet due through 90+ days). A customer with six open invoices gets one email, not six.\n\nEvery email becomes one row in `statement_log`. That log is also a guard: customers already marked `sent` this month are not re-emailed, and `failed` sends are retried on the next run. Dropped rows are counted in `skipped_rows`; guard-skipped customers are not.\n\nA quiet run can mean nobody owes you anything, or that no row was readable: if in doubt, run **Get open invoices** alone.\n\n### Setup\n1. Connect **Gmail** and **Google Sheets**.\n2. `invoices` headers: `customer_name, customer_email, invoice_id, invoice_date, due_date, amount, status`. Keep `due_date` a plain-text column written as YYYY-MM-DD (slash dates are read US-style, month/day/year). A row is closed when `status` matches one of these, ignoring case and surrounding spaces: paid, paid in full, payment received, void, cancelled, canceled, refunded, draft, closed, written off, complete, completed. **Anything else, blank included, will be billed.**\n3. `statement_log` headers: `sent_at, customer_name, customer_email, statement_period, invoice_count, total_due, skipped_rows, send_status, message_id, thread_id`. Leave the rows empty.\n4. Fill **Settings**: business name, your name, reply-to, currency symbol, sheet URL. Leave `minBalanceToSend` at 0 to email everyone.\n5. Open **Workflow settings -> Timezone** and pick your own: the 09:00 send and the ageing follow it.\n6. Open each Sheets node and the Gmail node once so credentials attach. Test with two invoices for one test customer (your own address): expect **one** email listing **both**. Then publish."
      },
      "typeVersion": 1
    },
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000004",
      "name": "Sticky Note - section 1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -100,
        -80
      ],
      "parameters": {
        "color": 7,
        "width": 600,
        "height": 860,
        "content": "### 1. Trigger and settings\nEverything you edit lives in **Settings**."
      },
      "typeVersion": 1
    },
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000005",
      "name": "Sticky Note - section 2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        540,
        -80
      ],
      "parameters": {
        "color": 7,
        "width": 860,
        "height": 860,
        "content": "### 2. Read and build\nReads the `invoices` tab, checks `statement_log` for customers already sent this month, and groups open invoices by customer."
      },
      "typeVersion": 1
    },
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000006",
      "name": "Sticky Note - section 3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1440,
        -80
      ],
      "parameters": {
        "color": 7,
        "width": 860,
        "height": 860,
        "content": "### 3. Send and log\nOne email per customer, then one log row each. Keep every Sheets node on **By URL** - picking from a list silently wipes **Log sent statements** settings; restore *Map Automatically* and Cell Format *Let n8n format*."
      },
      "typeVersion": 1
    },
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000010",
      "name": "Monthly on the 1st",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        0,
        340
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "months",
              "triggerAtHour": 9,
              "monthsInterval": 1,
              "triggerAtMinute": 0,
              "triggerAtDayOfMonth": 1
            }
          ]
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000011",
      "name": "Settings",
      "type": "n8n-nodes-base.set",
      "position": [
        260,
        340
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "set-sheeturl",
              "name": "sheetUrl",
              "type": "string",
              "value": "https://docs.google.com/spreadsheets/d/PASTE_YOUR_SHEET_ID_HERE/edit"
            },
            {
              "id": "set-business",
              "name": "businessName",
              "type": "string",
              "value": "Your Business Name"
            },
            {
              "id": "set-yourname",
              "name": "yourName",
              "type": "string",
              "value": "Your Name"
            },
            {
              "id": "set-youremail",
              "name": "yourEmail",
              "type": "string",
              "value": "you@example.com"
            },
            {
              "id": "set-symbol",
              "name": "currencySymbol",
              "type": "string",
              "value": "$"
            },
            {
              "id": "set-minbalance",
              "name": "minBalanceToSend",
              "type": "number",
              "value": 0
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000012",
      "name": "Get open invoices",
      "type": "n8n-nodes-base.googleSheets",
      "maxTries": 3,
      "position": [
        640,
        340
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "invoices"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "={{ $('Settings').first().json.sheetUrl }}"
        }
      },
      "retryOnFail": true,
      "typeVersion": 4.5,
      "alwaysOutputData": true,
      "waitBetweenTries": 5000
    },
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000017",
      "name": "Read the statement log",
      "type": "n8n-nodes-base.googleSheets",
      "maxTries": 3,
      "position": [
        900,
        340
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "statement_log"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "={{ $('Settings').first().json.sheetUrl }}"
        }
      },
      "executeOnce": true,
      "retryOnFail": true,
      "typeVersion": 4.5,
      "alwaysOutputData": true,
      "waitBetweenTries": 5000
    },
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000013",
      "name": "Build statements",
      "type": "n8n-nodes-base.code",
      "position": [
        1160,
        340
      ],
      "parameters": {
        "jsCode": "// Build one consolidated statement per customer.\n// Mode: Run Once for All Items.\n//\n// $input carries the statement_log rows (from Read the statement log) - the\n// log doubles as the resend guard. Invoice rows come via $() from the\n// earlier node.\n\nconst cfg = $('Settings').first().json;\nconst rows = $('Get open invoices').all().map(i => i.json);\nconst logRows = $input.all().map(i => i.json);\n\n// Today in the WORKFLOW timezone.\n// Do not use new Date() here - the Code node ignores the workflow timezone\n// and would use the host clock instead.\nconst nowTz = $now;\nconst today = new Date(nowTz.year, nowTz.month - 1, nowTz.day);\n\n// Closed states are matched exactly, after trimming and lowercasing.\n// Anything not on this list is treated as OPEN and will be billed.\nconst CLOSED = [\n  'paid', 'paid in full', 'payment received', 'void', 'cancelled', 'canceled',\n  'refunded', 'draft', 'closed', 'written off', 'complete', 'completed'\n];\n\n// An unedited Settings node must fail loudly, not brand real emails with\n// 'Your Business Name'. sheetUrl already fails loudly on its own; the two\n// name fields would otherwise fail in the worst place - the customer's inbox.\nconst bn = String(cfg.businessName == null ? '' : cfg.businessName).trim();\nconst yn = String(cfg.yourName == null ? '' : cfg.yourName).trim();\nif (!bn || bn === 'Your Business Name' || !yn || yn === 'Your Name') {\n  throw new Error('Open the Settings node and replace the placeholder business name / your name before running.');\n}\n\nconst symbol = String(cfg.currencySymbol || '$');\n\n// An untouched reply-to placeholder must not leak into mail headers. With no\n// Reply-To at all, replies simply go to the Gmail account that sent the mail\n// (the Gmail node omits the header when this is empty).\nconst replyToRaw = String(cfg.yourEmail == null ? '' : cfg.yourEmail).trim();\nconst replyTo = (replyToRaw.indexOf('@') !== -1 && !replyToRaw.toLowerCase().endsWith('@example.com')) ? replyToRaw : '';\nconst minBalance = Number(cfg.minBalanceToSend) > 0 ? Number(cfg.minBalanceToSend) : 0;\n\n// Parse a due date as a LOCAL calendar date.\n// new Date('YYYY-MM-DD') parses as UTC midnight, which shifts the day backwards\n// for anyone west of UTC and makes every ageing number wrong by one - so the\n// ISO form is taken apart by hand instead.\n// The fallback matters just as much: Google Sheets hands a real date cell back\n// the way it is DISPLAYED ('7/15/2026', or a full timestamp), not as ISO.\n// Slash dates are read in US month/day/year order (the way Sheets displays\n// them by default). '15/7/2026' fails that parse and the row is skipped and\n// counted; '5/7/2026' cannot be told apart from May 7 and is read as US\n// order - the sticky tells the buyer exactly that, and that plain-text ISO\n// is the reliable path.\nfunction parseLocalDate(v) {\n  if (v instanceof Date) return new Date(v.getFullYear(), v.getMonth(), v.getDate());\n  const s = String(v == null ? '' : v).trim();\n  if (s === '') return null;\n\n  const head = s.split(' ')[0].split('T')[0];\n  const p = head.split('-');\n  if (p.length >= 3) {\n    const y = Number(p[0]);\n    const m = Number(p[1]);\n    const d = Number(p[2]);\n    if (Number.isFinite(y) && Number.isFinite(m) && Number.isFinite(d) && y >= 1000) {\n      const dt = new Date(y, m - 1, d);\n      // Round-trip check so 2026-02-30 is rejected instead of rolling into March.\n      if (dt.getFullYear() === y && dt.getMonth() === m - 1 && dt.getDate() === d) return dt;\n      return null;\n    }\n  }\n\n  const parsed = new Date(s);\n  if (isNaN(parsed.getTime())) return null;\n  return new Date(parsed.getFullYear(), parsed.getMonth(), parsed.getDate());\n}\n\n// Read an amount without a regex. Understands '1,250.00', '$1,250.00',\n// '9.500,50' (European) and '(1,250.00)' (accounting negative).\nfunction toAmount(v) {\n  if (typeof v === 'number') return Number.isFinite(v) ? v : NaN;\n  const s = String(v == null ? '' : v).trim();\n  if (s === '') return NaN;\n\n  const negative = (s.indexOf('(') !== -1 && s.indexOf(')') !== -1) || s.indexOf('-') !== -1;\n\n  let kept = '';\n  for (const ch of s) {\n    if ((ch >= '0' && ch <= '9') || ch === '.' || ch === ',') kept += ch;\n  }\n  if (kept === '') return NaN;\n\n  const lastDot = kept.lastIndexOf('.');\n  const lastComma = kept.lastIndexOf(',');\n  let decimalAt = -1;\n  if (lastDot !== -1 && lastComma !== -1) {\n    // Whichever separator comes last is the decimal one.\n    decimalAt = Math.max(lastDot, lastComma);\n  } else if (lastComma !== -1) {\n    // Only commas: decimal separator when one or two digits follow\n    // ('9,50' and the one-decimal European form '2,5' alike).\n    if (kept.length - lastComma === 2 || kept.length - lastComma === 3) decimalAt = lastComma;\n  } else if (lastDot !== -1) {\n    // Only dots: '1.250' is ambiguous, so treat 3 trailing digits as thousands.\n    if (kept.length - lastDot !== 4) decimalAt = lastDot;\n  }\n\n  const intPart = decimalAt === -1 ? kept : kept.slice(0, decimalAt);\n  const fracPart = decimalAt === -1 ? '' : kept.slice(decimalAt + 1);\n\n  let digitsInt = '';\n  for (const ch of intPart) { if (ch >= '0' && ch <= '9') digitsInt += ch; }\n  let digitsFrac = '';\n  for (const ch of fracPart) { if (ch >= '0' && ch <= '9') digitsFrac += ch; }\n  if (digitsInt === '' && digitsFrac === '') return NaN;\n\n  const n = Number((digitsInt === '' ? '0' : digitsInt) + (digitsFrac === '' ? '' : '.' + digitsFrac));\n  if (!Number.isFinite(n)) return NaN;\n  return negative ? -n : n;\n}\n\nfunction money(n) {\n  return symbol + Number(n).toFixed(2);\n}\n\nfunction esc(v) {\n  return String(v == null ? '' : v)\n    .split('&').join('&amp;')\n    .split('<').join('&lt;')\n    .split('>').join('&gt;')\n    .split('\"').join('&quot;');\n}\n\nconst groups = new Map();\nconst seenInvoices = new Set();\nlet skipped = 0;\n\nfor (const r of rows) {\n  const email = String(r.customer_email == null ? '' : r.customer_email).trim();\n  const status = String(r.status == null ? '' : r.status).trim().toLowerCase();\n  const amount = toAmount(r.amount);\n  const due = parseLocalDate(r.due_date);\n\n  // Skip anything we cannot legitimately bill on. Counted, never silent.\n  if (!email || email.indexOf('@') === -1) { skipped++; continue; }\n  if (CLOSED.indexOf(status) !== -1) { skipped++; continue; }\n  if (!Number.isFinite(amount) || amount <= 0) { skipped++; continue; }\n  if (!due) { skipped++; continue; }\n\n  // A duplicated ledger row must not double a customer's total. Blank ids\n  // are left alone - they cannot be told apart safely.\n  const invId = String(r.invoice_id == null ? '' : r.invoice_id).trim();\n  if (invId) {\n    const dupKey = email.toLowerCase() + '|' + invId;\n    if (seenInvoices.has(dupKey)) { skipped++; continue; }\n    seenInvoices.add(dupKey);\n  }\n\n  // round, not floor - a DST change mid-period must not shift the bucket.\n  const days = Math.round((today - due) / 86400000);\n\n  let bucket;\n  if (days <= 0) bucket = 'Not yet due';\n  else if (days <= 30) bucket = '1-30 days';\n  else if (days <= 60) bucket = '31-60 days';\n  else if (days <= 90) bucket = '61-90 days';\n  else bucket = '90+ days';\n\n  const key = email.toLowerCase();\n  if (!groups.has(key)) {\n    const nm = String(r.customer_name == null ? '' : r.customer_name).trim();\n    groups.set(key, { name: nm || email, email: email, lines: [] });\n  }\n  groups.get(key).lines.push({\n    invoice_id: invId,\n    invoice_date: String(r.invoice_date == null ? '' : r.invoice_date).trim(),\n    due_date: String(r.due_date == null ? '' : r.due_date).trim(),\n    amount: amount,\n    days: days,\n    bucket: bucket\n  });\n}\n\nconst BUCKETS = ['Not yet due', '1-30 days', '31-60 days', '61-90 days', '90+ days'];\nconst out = [];\nconst period = nowTz.toFormat('LLLL yyyy');\nconst asOf = nowTz.toFormat('yyyy-MM-dd');\n\n// Resend guard: any customer already logged as sent for this period is\n// dropped here. Failed sends are NOT in this set, so re-running the\n// workflow retries exactly the customers whose email did not go out.\nconst sentThisPeriod = new Set();\nfor (const lr of logRows) {\n  const em = String(lr.customer_email == null ? '' : lr.customer_email).trim().toLowerCase();\n  const pd = String(lr.statement_period == null ? '' : lr.statement_period).trim();\n  const st = String(lr.send_status == null ? '' : lr.send_status).trim().toLowerCase();\n  if (em && st === 'sent' && pd === period) sentThisPeriod.add(em);\n}\n\nfor (const g of groups.values()) {\n  if (sentThisPeriod.has(g.email.toLowerCase())) continue;\n  const total = g.lines.reduce((s, l) => s + l.amount, 0);\n  if (total < minBalance) { skipped += g.lines.length; continue; }\n\n  // Oldest debt first.\n  g.lines.sort((a, b) => b.days - a.days);\n\n  const lineRows = g.lines.map(l =>\n    '<tr><td>' + esc(l.invoice_id) + '</td><td>' + esc(l.invoice_date) + '</td><td>' + esc(l.due_date) +\n    '</td><td align=\"right\">' + money(l.amount) + '</td><td>' + esc(l.bucket) + '</td></tr>'\n  ).join('');\n\n  const ageingRows = BUCKETS\n    .map(b => ({ label: b, sum: g.lines.filter(l => l.bucket === b).reduce((s, l) => s + l.amount, 0) }))\n    .filter(x => x.sum > 0)\n    .map(x => '<tr><td>' + esc(x.label) + '</td><td align=\"right\">' + money(x.sum) + '</td></tr>')\n    .join('');\n\n  const html =\n    '<p>Hi ' + esc(g.name) + ',</p>' +\n    '<p>Here is your account statement with ' + esc(cfg.businessName) + ', as of ' + esc(asOf) + '.</p>' +\n    '<table border=\"1\" cellpadding=\"6\" cellspacing=\"0\">' +\n    '<thead><tr><th>Invoice</th><th>Invoiced</th><th>Due date</th><th>Amount</th><th>Age</th></tr></thead>' +\n    '<tbody>' + lineRows + '</tbody></table>' +\n    '<p><b>Total outstanding: ' + money(total) + '</b></p>' +\n    '<table border=\"1\" cellpadding=\"6\" cellspacing=\"0\">' +\n    '<thead><tr><th>Age</th><th>Amount</th></tr></thead>' +\n    '<tbody>' + ageingRows + '</tbody></table>' +\n    '<p>If anything here looks wrong, or you have already sent payment, just reply to this email and we will check it.</p>' +\n    '<p>Thank you,<br>' + esc(cfg.yourName) + '<br>' + esc(cfg.businessName) + '</p>';\n\n  out.push({\n    json: {\n      customer_name: g.name,\n      customer_email: g.email,\n      subject: 'Statement of account - ' + String(cfg.businessName) + ' - ' + period,\n      html: html,\n      reply_to: replyTo,\n      invoice_count: g.lines.length,\n      total_due: Number(total.toFixed(2)),\n      statement_period: period,\n      run_skipped_rows: 0\n    }\n  });\n}\n\n// Stamp the run-level skipped count onto every statement so it reaches the log.\n// This is a whole-run figure repeated per row, not a per-customer count.\nfor (const o of out) {\n  o.json.run_skipped_rows = skipped;\n}\n\nreturn out;"
      },
      "typeVersion": 2
    },
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000014",
      "name": "Email the statement",
      "type": "n8n-nodes-base.gmail",
      "onError": "continueRegularOutput",
      "position": [
        1540,
        340
      ],
      "parameters": {
        "sendTo": "={{ $json.customer_email }}",
        "message": "={{ $json.html }}",
        "options": {
          "replyTo": "={{ $json.reply_to }}",
          "appendAttribution": false
        },
        "subject": "={{ $json.subject }}",
        "emailType": "html"
      },
      "typeVersion": 2.1
    },
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000015",
      "name": "Build the log row",
      "type": "n8n-nodes-base.code",
      "position": [
        1800,
        340
      ],
      "parameters": {
        "jsCode": "// Pair each Gmail response with the statement that produced it.\n// Gmail preserves input order 1:1, so index pairing is safe here.\n// Mode: Run Once for All Items.\n\nconst statements = $('Build statements').all();\nconst sent = $input.all();\nconst stamp = $now.toFormat('yyyy-MM-dd HH:mm');\n\nreturn sent.map((item, i) => {\n  const s = statements[i] ? statements[i].json : {};\n  const j = item.json == null ? {} : item.json;\n\n  // The Gmail node is set to carry on past a failure, so a bad address costs\n  // one customer rather than the whole run - and the failure still gets logged.\n  const messageId = j.id == null ? '' : String(j.id);\n\n  return {\n    json: {\n      sent_at: stamp,\n      customer_name: s.customer_name == null ? '' : s.customer_name,\n      customer_email: s.customer_email == null ? '' : s.customer_email,\n      statement_period: s.statement_period == null ? '' : s.statement_period,\n      invoice_count: s.invoice_count == null ? 0 : s.invoice_count,\n      total_due: s.total_due == null ? 0 : s.total_due,\n      skipped_rows: s.run_skipped_rows == null ? 0 : s.run_skipped_rows,\n      send_status: messageId === '' ? 'failed' : 'sent',\n      message_id: messageId,\n      thread_id: j.threadId == null ? '' : String(j.threadId)\n    }\n  };\n});"
      },
      "typeVersion": 2
    },
    {
      "id": "a1b2c3d4-0001-4000-8000-000000000016",
      "name": "Log sent statements",
      "type": "n8n-nodes-base.googleSheets",
      "maxTries": 3,
      "position": [
        2060,
        340
      ],
      "parameters": {
        "columns": {
          "value": {},
          "mappingMode": "autoMapInputData",
          "matchingColumns": []
        },
        "options": {
          "cellFormat": "RAW"
        },
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "name",
          "value": "statement_log"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "={{ $('Settings').first().json.sheetUrl }}"
        }
      },
      "retryOnFail": true,
      "typeVersion": 4.5,
      "waitBetweenTries": 5000
    }
  ],
  "settings": {
    "executionOrder": "v1"
  },
  "connections": {
    "Settings": {
      "main": [
        [
          {
            "node": "Get open invoices",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build statements": {
      "main": [
        [
          {
            "node": "Email the statement",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Build the log row": {
      "main": [
        [
          {
            "node": "Log sent statements",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get open invoices": {
      "main": [
        [
          {
            "node": "Read the statement log",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Monthly on the 1st": {
      "main": [
        [
          {
            "node": "Settings",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Email the statement": {
      "main": [
        [
          {
            "node": "Build the log row",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Read the statement log": {
      "main": [
        [
          {
            "node": "Build statements",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

About this workflow

This workflow runs on the 1st of every month, reads open invoices from Google Sheets, builds one consolidated account statement per customer with ageing totals, emails it via Gmail, and appends a send log back to Google Sheets to prevent duplicate sends and retry failures. Runs…

Source: https://n8n.io/workflows/17771/ — original creator credit. Request a take-down →

More Email & Gmail workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.