Create Xero Ar/ap Markdown Snapshots with Optional Openai Gpt Summaries

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 →

{
  "id": "Sl3Qs2hk8p9lN6cB",
  "name": "My workflow",
  "tags": [],
  "nodes": [
    {
      "id": "c04b6dd1-27b0-45ae-8adc-93551eb786f2",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        0,
        0
      ],
      "parameters": {
        "width": 480,
        "height": 896,
        "content": "## Untitled workflow\n\n### How it works\n\nThis workflow runs on a scheduled cadence to create an accounting snapshot from Xero. It prepares reporting settings, fetches customer invoices and supplier bills in parallel, normalizes both lists, then merges them into a Markdown snapshot and final output. A separate optional AI branch can generate an enhanced narrative summary using a chat model.\n\n### Setup steps\n\n- Configure the schedule trigger with the desired weekly, monthly, quarterly, or annual cadence.\n- Connect valid Xero credentials on both Xero nodes and confirm the organization, filters, and date settings match the intended reporting scope.\n- Review the code nodes for snapshot settings, normalization rules, top-list limits, currency formatting, and Markdown output format.\n- If using the optional AI branch, configure the Editable Chat Model with an OpenAI-compatible credential and adjust the AI agent prompt/input as needed.\n\n### Customization\n\nAdjust the reporting cadence, date-range logic, number of top invoices or bills, Markdown template, and optional AI summary prompt to match the desired finance reporting style."
      },
      "typeVersion": 1
    },
    {
      "id": "72bb8aa5-8e67-4530-8d62-665efcec1a0b",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        560,
        192
      ],
      "parameters": {
        "color": 7,
        "width": 480,
        "height": 320,
        "content": "## Schedule and settings\n\nStarts the workflow on the configured cadence and prepares snapshot parameters such as date range, reporting period, and any values used by the Xero queries."
      },
      "typeVersion": 1
    },
    {
      "id": "b6de165d-b8a6-433a-8837-e2edb5156f14",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1200,
        0
      ],
      "parameters": {
        "color": 7,
        "height": 560,
        "content": "## Fetch Xero records\n\nRetrieves customer invoices and supplier bills from Xero in two parallel branches using the prepared snapshot settings."
      },
      "typeVersion": 1
    },
    {
      "id": "5c71a310-0702-4347-8bb8-3b13dc9368c3",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1536,
        0
      ],
      "parameters": {
        "color": 7,
        "height": 560,
        "content": "## Normalize invoice lists\n\nTransforms the fetched accounts receivable and accounts payable records into consistent top-list formats for downstream merging."
      },
      "typeVersion": 1
    },
    {
      "id": "ebcfaa41-0576-4b66-8c33-8ea668ae902b",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1888,
        160
      ],
      "parameters": {
        "color": 7,
        "width": 800,
        "height": 304,
        "content": "## Build final snapshot\n\nAppends the normalized AR and AP lists, builds a Markdown snapshot, and produces the standard final snapshot output."
      },
      "typeVersion": 1
    },
    {
      "id": "0ce3119c-1660-4f85-a758-ccda6a73d138",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2192,
        880
      ],
      "parameters": {
        "color": 7,
        "width": 512,
        "height": 544,
        "content": "## Optional AI summary\n\nA separate lower-canvas branch that uses an AI agent and chat model to generate an optional narrative summary, then formats a final AI-enhanced snapshot output."
      },
      "typeVersion": 1
    },
    {
      "id": "d37531f4-0632-4f90-bb56-aa0af794d1a4",
      "name": "When Weekly at 8am",
      "type": "n8n-nodes-base.scheduleTrigger",
      "position": [
        608,
        352
      ],
      "parameters": {
        "rule": {
          "interval": [
            {
              "field": "weeks",
              "triggerAtHour": 8
            }
          ]
        }
      },
      "typeVersion": 1.3
    },
    {
      "id": "b01bc5c5-e56e-4436-a7ac-f3799a13c96d",
      "name": "Prepare Snapshot Config",
      "type": "n8n-nodes-base.code",
      "position": [
        896,
        352
      ],
      "parameters": {
        "jsCode": "const now = new Date();\nconst cadence = 'weekly'; // Change to weekly, monthly, quarterly, annual, or a custom label.\nconst xeroTenantId = 'YOUR_XERO_TENANT_ID';\n\nreturn [{\n  json: {\n    generatedAt: now.toISOString(),\n    reportDate: now.toISOString().slice(0, 10),\n    cadence,\n    xeroTenantId,\n    customerWhere: 'Type==\\\"ACCREC\\\"&&Status==\\\"AUTHORISED\\\"&&AmountDue>0',\n    supplierWhere: 'Type==\\\"ACCPAY\\\"&&Status==\\\"AUTHORISED\\\"&&AmountDue>0'\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "ffb7dcf2-cede-4656-9d40-222b0a64b396",
      "name": "Retrieve Xero Customer Invoices",
      "type": "n8n-nodes-base.xero",
      "position": [
        1248,
        208
      ],
      "parameters": {
        "options": {
          "where": "={{ $json.customerWhere }}"
        },
        "operation": "getAll",
        "returnAll": true,
        "organizationId": "={{ $json.xeroTenantId }}"
      },
      "typeVersion": 1,
      "alwaysOutputData": true
    },
    {
      "id": "ab3baf64-f0ac-4023-ab09-b208f2df486d",
      "name": "Retrieve Xero Supplier Bills",
      "type": "n8n-nodes-base.xero",
      "position": [
        1248,
        400
      ],
      "parameters": {
        "options": {
          "where": "={{ $json.supplierWhere }}"
        },
        "operation": "getAll",
        "returnAll": true,
        "organizationId": "={{ $json.xeroTenantId }}"
      },
      "typeVersion": 1,
      "alwaysOutputData": true
    },
    {
      "id": "d4fe433f-bd8d-4dd7-9688-d827a7c7fc31",
      "name": "Flatten Customer Invoices",
      "type": "n8n-nodes-base.code",
      "position": [
        1584,
        208
      ],
      "parameters": {
        "jsCode": "function flattenInvoices(items) {\n  const out = [];\n  for (const item of items) {\n    const json = item.json || {};\n    if (Array.isArray(json.Invoices)) out.push(...json.Invoices);\n    else if (Array.isArray(json.invoices)) out.push(...json.invoices);\n    else if (Object.keys(json).length) out.push(json);\n  }\n  return out;\n}\n\nfunction xeroDate(value) {\n  if (!value) return null;\n  if (typeof value === 'string') {\n    const ms = value.match(/\\/Date\\((\\d+)/);\n    if (ms) return new Date(Number(ms[1]));\n    const d = new Date(value);\n    if (!Number.isNaN(d.getTime())) return d;\n  }\n  if (typeof value === 'number') return new Date(value);\n  return null;\n}\n\nfunction iso(value) {\n  const d = xeroDate(value);\n  return d ? d.toISOString().slice(0, 10) : '';\n}\n\nfunction amount(value) {\n  const n = Number(value ?? 0);\n  return Number.isFinite(n) ? n : 0;\n}\n\nfunction contactName(inv) {\n  return inv.Contact?.Name || inv.Contact?.name || inv.contact?.name || inv.contactName || inv.ContactName || '';\n}\n\nconst today = new Date();\nconst dayMs = 24 * 60 * 60 * 1000;\nconst invoices = flattenInvoices($input.all())\n  .filter(inv => amount(inv.AmountDue ?? inv.amountDue ?? inv.AmountRemaining ?? inv.amountRemaining) > 0)\n  .map(inv => {\n    const invoiceDateValue = inv.DateString || inv.Date || inv.date || inv.InvoiceDate || inv.invoiceDate;\n    const dueDateValue = inv.DueDateString || inv.DueDate || inv.dueDate;\n    const invoiceDate = xeroDate(invoiceDateValue);\n    const daysOutstanding = invoiceDate ? Math.max(0, Math.floor((today - invoiceDate) / dayMs)) : null;\n    return {\n      customer: contactName(inv),\n      invoiceNumber: inv.InvoiceNumber || inv.invoiceNumber || inv.Reference || inv.reference || '',\n      reference: inv.Reference || inv.reference || '',\n      invoiceDate: iso(invoiceDateValue),\n      targetCollectionDate: iso(dueDateValue),\n      dueDate: iso(dueDateValue),\n      daysOutstanding,\n      currency: inv.CurrencyCode || inv.currencyCode || inv.Currency || 'PHP',\n      amountDue: amount(inv.AmountDue ?? inv.amountDue ?? inv.AmountRemaining ?? inv.amountRemaining),\n      status: inv.Status || inv.status || '',\n      invoiceId: inv.InvoiceID || inv.invoiceID || inv.invoiceId || ''\n    };\n  })\n  .sort((a, b) => b.amountDue - a.amountDue)\n  .slice(0, 5);\n\nreturn [{ json: { snapshotSection: 'customer_invoices', count: invoices.length, topCustomerInvoices: invoices } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "6f7aeecf-2928-4f38-a8cd-d78a1f6bc8c3",
      "name": "Flatten Supplier Bills",
      "type": "n8n-nodes-base.code",
      "position": [
        1584,
        400
      ],
      "parameters": {
        "jsCode": "function flattenInvoices(items) {\n  const out = [];\n  for (const item of items) {\n    const json = item.json || {};\n    if (Array.isArray(json.Invoices)) out.push(...json.Invoices);\n    else if (Array.isArray(json.invoices)) out.push(...json.invoices);\n    else if (Object.keys(json).length) out.push(json);\n  }\n  return out;\n}\n\nfunction xeroDate(value) {\n  if (!value) return null;\n  if (typeof value === 'string') {\n    const ms = value.match(/\\/Date\\((\\d+)/);\n    if (ms) return new Date(Number(ms[1]));\n    const d = new Date(value);\n    if (!Number.isNaN(d.getTime())) return d;\n  }\n  if (typeof value === 'number') return new Date(value);\n  return null;\n}\n\nfunction iso(value) {\n  const d = xeroDate(value);\n  return d ? d.toISOString().slice(0, 10) : '';\n}\n\nfunction amount(value) {\n  const n = Number(value ?? 0);\n  return Number.isFinite(n) ? n : 0;\n}\n\nfunction contactName(inv) {\n  return inv.Contact?.Name || inv.Contact?.name || inv.contact?.name || inv.contactName || inv.ContactName || '';\n}\n\nconst today = new Date();\nconst dayMs = 24 * 60 * 60 * 1000;\nconst bills = flattenInvoices($input.all())\n  .filter(inv => amount(inv.AmountDue ?? inv.amountDue ?? inv.AmountRemaining ?? inv.amountRemaining) > 0)\n  .map(inv => {\n    const billDateValue = inv.DateString || inv.Date || inv.date || inv.InvoiceDate || inv.invoiceDate;\n    const dueDateValue = inv.DueDateString || inv.DueDate || inv.dueDate;\n    const billDate = xeroDate(billDateValue);\n    const daysOutstanding = billDate ? Math.max(0, Math.floor((today - billDate) / dayMs)) : null;\n    return {\n      supplier: contactName(inv),\n      billNumber: inv.InvoiceNumber || inv.invoiceNumber || inv.Reference || inv.reference || '',\n      reference: inv.Reference || inv.reference || '',\n      billDate: iso(billDateValue),\n      dueDate: iso(dueDateValue),\n      daysOutstanding,\n      currency: inv.CurrencyCode || inv.currencyCode || inv.Currency || 'PHP',\n      amountDue: amount(inv.AmountDue ?? inv.amountDue ?? inv.AmountRemaining ?? inv.amountRemaining),\n      status: inv.Status || inv.status || '',\n      invoiceId: inv.InvoiceID || inv.invoiceID || inv.invoiceId || ''\n    };\n  })\n  .sort((a, b) => b.amountDue - a.amountDue)\n  .slice(0, 5);\n\nreturn [{ json: { snapshotSection: 'supplier_bills', count: bills.length, topSupplierBills: bills } }];"
      },
      "typeVersion": 2
    },
    {
      "id": "4906a4e5-3d59-4f8b-b1d0-33acc2b8d861",
      "name": "Merge Receivables and Payables",
      "type": "n8n-nodes-base.merge",
      "position": [
        1936,
        288
      ],
      "parameters": {},
      "typeVersion": 3.2
    },
    {
      "id": "5d07a712-60f4-45f0-a3e2-b0e779d36553",
      "name": "Create Snapshot Markdown",
      "type": "n8n-nodes-base.code",
      "position": [
        2240,
        288
      ],
      "parameters": {
        "jsCode": "const items = $input.all().map(i => i.json);\nconst ar = items.find(i => i.snapshotSection === 'customer_invoices') || { topCustomerInvoices: [] };\nconst ap = items.find(i => i.snapshotSection === 'supplier_bills') || { topSupplierBills: [] };\nconst settings = $('Prepare Snapshot Config').first().json;\n\nfunction money(value, currency) {\n  try {\n    return new Intl.NumberFormat('en-PH', { style: 'currency', currency: currency || 'PHP' }).format(Number(value || 0));\n  } catch (e) {\n    return `${currency || ''} ${Number(value || 0).toLocaleString('en-US', { minimumFractionDigits: 2, maximumFractionDigits: 2 })}`.trim();\n  }\n}\n\nfunction safe(value) {\n  const s = String(value ?? '');\n  return s.replace(/\\|/g, '\\\\|').replace(/\\n/g, ' ');\n}\n\nfunction tableCustomer(rows) {\n  const header = '| # | Customer | Invoice | Amount due | Target collection date | Days outstanding | Invoice date | Due date |\\n|---:|---|---|---:|---|---:|---|---|';\n  if (!rows.length) return `${header}\\n| - | No outstanding customer invoices found | - | - | - | - | - | - |`;\n  return [header, ...rows.map((r, i) => `| ${i + 1} | ${safe(r.customer)} | ${safe(r.invoiceNumber)} | ${safe(money(r.amountDue, r.currency))} | ${safe(r.targetCollectionDate)} | ${r.daysOutstanding ?? ''} | ${safe(r.invoiceDate)} | ${safe(r.dueDate)} |`)].join('\\n');\n}\n\nfunction tableSupplier(rows) {\n  const header = '| # | Supplier | Bill | Amount due | Due date | Days outstanding | Bill date |\\n|---:|---|---|---:|---|---:|---|';\n  if (!rows.length) return `${header}\\n| - | No outstanding supplier bills found | - | - | - | - | - |`;\n  return [header, ...rows.map((r, i) => `| ${i + 1} | ${safe(r.supplier)} | ${safe(r.billNumber)} | ${safe(money(r.amountDue, r.currency))} | ${safe(r.dueDate)} | ${r.daysOutstanding ?? ''} | ${safe(r.billDate)} |`)].join('\\n');\n}\n\nconst reportDate = settings.reportDate || new Date().toISOString().slice(0, 10);\nconst subject = `Xero Outstanding AR/AP Snapshot - ${reportDate}`;\nconst markdown = [\n  `# ${subject}`,\n  '',\n  `Cadence: ${settings.cadence || 'weekly'}`,\n  `Generated at: ${settings.generatedAt || new Date().toISOString()}`,\n  '',\n  '## Top 5 outstanding customer invoices',\n  tableCustomer(ar.topCustomerInvoices || []),\n  '',\n  '## Top 5 outstanding supplier bills',\n  tableSupplier(ap.topSupplierBills || []),\n  '',\n  '## Notes',\n  '- Customer invoices are sorted by amount due from highest to lowest.',\n  '- Target collection date defaults to the Xero due date. Replace this rule if your collection team uses a separate target date.',\n  '- Supplier bills are sorted by amount due from highest to lowest.',\n  '- Days outstanding is calculated from invoice/bill date to the report date.',\n  '- This workflow is read-only and does not approve, collect, or pay anything.'\n].join('\\n');\n\nconst aiPrompt = `Summarize this Xero outstanding receivables/payables snapshot in one concise paragraph. Mention the largest customer invoice, largest supplier bill, and any obvious cash collection or payment scheduling risks. Do not invent facts or recommend paying/collecting anything outside the data.\\n\\n${markdown}`;\n\nreturn [{\n  json: {\n    subject,\n    markdown,\n    aiPrompt,\n    data: {\n      customerInvoices: ar.topCustomerInvoices || [],\n      supplierBills: ap.topSupplierBills || []\n    }\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "6af0882c-0f73-402b-b108-8740f2f4a9b4",
      "name": "Generate Snapshot Output",
      "type": "n8n-nodes-base.code",
      "position": [
        2544,
        288
      ],
      "parameters": {
        "jsCode": "const snapshot = $input.first().json;\nreturn [{\n  json: {\n    subject: snapshot.subject,\n    markdown: snapshot.markdown,\n    data: snapshot.data,\n    nextStep: 'Connect this node to Email, Slack, Telegram, Google Sheets, Notion, or another delivery/storage node.'\n  }\n}];"
      },
      "typeVersion": 2
    },
    {
      "id": "aa9d8002-d6df-4ce7-99bc-e39769e3c7b9",
      "name": "AI Summary Agent",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        2240,
        1040
      ],
      "parameters": {
        "text": "={{ $json.aiPrompt }}",
        "options": {
          "systemMessage": "You are a cautious finance operations assistant. Summarize Xero receivables and payables snapshots from the provided data only. Keep it short, factual, and action-oriented. Do not approve payments, promise collection outcomes, invent target dates, or alter numbers."
        },
        "promptType": "define"
      },
      "typeVersion": 1.6
    },
    {
      "id": "36092faf-61cd-4481-9652-43ea934429a1",
      "name": "OpenAI Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        2240,
        1280
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-5-mini"
        },
        "options": {}
      },
      "typeVersion": 1.2
    },
    {
      "id": "70703d98-d002-444b-a133-42f36364800e",
      "name": "Generate AI Enhanced Snapshot",
      "type": "n8n-nodes-base.code",
      "position": [
        2560,
        1040
      ],
      "parameters": {
        "jsCode": "const snapshot = $('Create Snapshot Markdown').first().json;\nconst aiText = $json.output || $json.text || $json.response || '';\nconst markdown = aiText ? `## AI Summary\\n${aiText}\\n\\n${snapshot.markdown}` : snapshot.markdown;\nreturn [{ json: { subject: snapshot.subject, markdown, data: snapshot.data } }];"
      },
      "typeVersion": 2
    }
  ],
  "active": false,
  "settings": {
    "binaryMode": "separate",
    "executionOrder": "v1"
  },
  "versionId": "de0ea703-cac5-4835-909c-396030c9802d",
  "nodeGroups": [],
  "connections": {
    "AI Summary Agent": {
      "main": [
        [
          {
            "node": "Generate AI Enhanced Snapshot",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "AI Summary Agent",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "When Weekly at 8am": {
      "main": [
        [
          {
            "node": "Prepare Snapshot Config",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Flatten Supplier Bills": {
      "main": [
        [
          {
            "node": "Merge Receivables and Payables",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "Prepare Snapshot Config": {
      "main": [
        [
          {
            "node": "Retrieve Xero Customer Invoices",
            "type": "main",
            "index": 0
          },
          {
            "node": "Retrieve Xero Supplier Bills",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Create Snapshot Markdown": {
      "main": [
        [
          {
            "node": "Generate Snapshot Output",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Flatten Customer Invoices": {
      "main": [
        [
          {
            "node": "Merge Receivables and Payables",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Retrieve Xero Supplier Bills": {
      "main": [
        [
          {
            "node": "Flatten Supplier Bills",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Merge Receivables and Payables": {
      "main": [
        [
          {
            "node": "Create Snapshot Markdown",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Retrieve Xero Customer Invoices": {
      "main": [
        [
          {
            "node": "Flatten Customer Invoices",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}

About this workflow

This workflow runs on a schedule to pull outstanding customer invoices and supplier bills from Xero, ranks the top 5 by amount due, and generates a Markdown AR/AP snapshot (optionally adding an OpenAI-based narrative summary). Runs on a scheduled trigger (weekly by default).…

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

More AI & RAG workflows → · Browse all categories →

Related workflows

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