Conciliación bancaria de IA

• •Credenciales de API de Google Sheets
• •Clave de API de OpenAI

{
  "id": "wpg39fpK9dN3BaXH",
  "meta": {
    "instanceId": "27ffea455fd6438ca9c70f7a14250d75f584b8ec341c04d334cc7237b9299652",
    "templateCredsSetupCompleted": true
  },
  "name": "Bank Reconciliation AI",
  "tags": [],
  "nodes": [
    {
      "id": "87eb23a7-ed90-458f-9cd3-c98315ac8070",
      "name": "Al hacer clic en 'Ejecutar flujo de trabajo'",
      "type": "n8n-nodes-base.manualTrigger",
      "position": [
        864,
        -544
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "00eaa2bc-f104-4666-b071-f0de28ec1049",
      "name": "Datos de Facturas",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1232,
        -656
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 595638947,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=595638947",
          "cachedResultName": "Invoices"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
          "cachedResultName": "Reconciliation POC"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "8GgfaHX1qOHcB2ej",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "ae6b1aee-33a9-48ea-b11e-6fd267b8e544",
      "name": "Extractos Bancarios",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1232,
        -432
      ],
      "parameters": {
        "options": {},
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 2045470915,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=2045470915",
          "cachedResultName": "Bank Statement"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
          "cachedResultName": "Reconciliation POC"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "8GgfaHX1qOHcB2ej",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "1ddece06-81b0-4b81-a78e-c91ddd21b6bf",
      "name": "OpenAI Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        2240,
        -304
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4.1-mini"
        },
        "options": {
          "temperature": 0
        }
      },
      "credentials": {
        "openAiApi": {
          "id": "CncvDJ9wuNXul1oG",
          "name": "OpenAi account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "1896c41e-96e9-4a00-be6e-ca1325fd137a",
      "name": "Procesar Datos de Factura Vs Extracto Bancario 1",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        2320,
        -544
      ],
      "parameters": {
        "text": "=You are a Reconciliation Assistant.\nYou receive one JSON input object with:\n\n{\n  \"invoices\": [ { ... } ],\n  \"bank_transactions\": [ { ... } ],\n  \"currency\": \"USD\",\n  \"source\": \"Bank XYZ\"\n}\n\n🧩 Matching Rules (apply in order)\n\nHard reference match\n\nMatch if invoice[\"Invoice Number\"] == txn[\"Reference\"] (case-sensitive, trim spaces).\n\nAmount match\n\nMatch if invoice[\"Total (USD)\"] == txn[\"Credit (USD)\"].\n\nTreat empty strings as 0.\n\nDate proximity\n\nInvoice Date within ±7 days (inclusive) of Transaction Date.\n\nName/company heuristic (fallback)\n\nIf no reference match but company/customer name appears in transaction description (case-insensitive substring), and amount equal, and date within ±7 days.\n\nMark heuristic = true.\n\n🚫 Ignore / Exclude\n\nIgnore “Opening Balance” and “Closing Balance” rows.\n\nIgnore expense or debit transactions (Debit (USD) > 0) for invoice matching.\n\n🧠 Matching Priority\n\nIf multiple matches for one invoice:\n\nClosest date wins.\n\nIf tie, exact reference match preferred.\n\nIf still tied, pick lowest txn_row_number.\n\nIf one transaction matches multiple invoices (same amount), list under possible_matches.\n\n🧮 Confidence Scoring\n\nStart at 1.0, subtract:\n\n−0.05 → if only date proximity (no reference)\n\n−0.10 → if heuristic name match used\n\n−0.10 → if date gap > 3 days (but ≤ 7)\n\nClamp between 0 and 1.\n\n✅ Output Format (STRICT JSON ONLY)\n\nThe AI must return only JSON with the following structure and field names:\n\n{\n  \"matched\": [\n    {\n      \"invoice_no\": \"INV-1001\",\n      \"invoice_row_number\": 2,\n      \"txn_row_number\": 3,\n      \"amount\": 2470.0,\n      \"reference\": \"INV-1001\",\n      \"date_invoice\": \"2025-09-03\",\n      \"date_txn\": \"2025-09-03\",\n      \"confidence\": 0.99,\n      \"heuristic\": false,\n      \"notes\": \"reference+amount+date\"\n    }\n  ],\n  \"possible_matches\": [\n    {\n      \"invoice_no\": \"INV-1007\",\n      \"invoice_row_number\": 8,\n      \"candidates\": [\n        {\n          \"txn_row_number\": 13,\n          \"amount\": 1995.0,\n          \"reference\": \"INV-1007\",\n          \"date_txn\": \"2025-09-18\"\n        }\n      ],\n      \"reason\": \"multiple or weak signals\"\n    }\n  ],\n  \"unmatched\": {\n    \"invoices\": [\n      {\n        \"invoice_no\": \"INV-XXXX\",\n        \"invoice_row_number\": 12,\n        \"amount\": 100.0\n      }\n    ],\n    \"bank_transactions\": [\n      {\n        \"txn_row_number\": 19,\n        \"amount\": 5.25,\n        \"reason\": \"interest/expense/no reference\"\n      }\n    ]\n  },\n  \"summary\": {\n    \"currency\": \"USD\",\n    \"totals\": {\n      \"invoices_count\": 0,\n      \"bank_credits_count\": 0,\n      \"matched_count\": 0,\n      \"possible_matches_count\": 0,\n      \"unmatched_invoices_count\": 0,\n      \"unmatched_bank_count\": 0,\n      \"matched_amount_sum\": 0.0\n    },\n    \"parameters\": {\n      \"date_window_days\": 7\n    }\n  }\n}\n\n🧾 Output Rules\n\nAlways include all four sections (matched, possible_matches, unmatched, summary) even if empty.\n\nUse exact key names and field order as shown.\n\nAll numeric fields (amounts, counts, confidence) must be numbers, not strings.\n\nDates should be returned exactly as in the input, without conversion.\n\nDo not include any extra text or explanation — only valid JSON.\n\n\nUser (Data) Use the following JSON as input: {{ JSON.stringify($json.payload) }}\n",
        "options": {},
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2.2
    },
    {
      "id": "36e5a7c1-aed9-4d9a-92bc-7145d5bcc302",
      "name": "Combinar",
      "type": "n8n-nodes-base.merge",
      "position": [
        1552,
        -544
      ],
      "parameters": {},
      "typeVersion": 3.2
    },
    {
      "id": "149bd1b7-90e6-4309-8f52-3c73d0711199",
      "name": "Unir y Etiquetar Datos Combinados",
      "type": "n8n-nodes-base.code",
      "position": [
        1904,
        -544
      ],
      "parameters": {
        "jsCode": "const invoices = $items(\"Invoice Data\").map(i => i.json);\nconst bank = $items(\"Bank Statements\").map(b => b.json);\n\nreturn [{\n  json: {\n    payload: {\n      source: \"reconciliation_v1\",\n      currency: \"USD\",\n      invoices,\n      bank_transactions: bank\n    }\n  }\n}];\n"
      },
      "typeVersion": 2
    },
    {
      "id": "1bc4151f-8b9f-402c-a4c1-f30bf6e09bc9",
      "name": "Switch",
      "type": "n8n-nodes-base.switch",
      "position": [
        3488,
        -576
      ],
      "parameters": {
        "rules": {
          "values": [
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "ffa6f0a0-e329-4306-a693-18f191add964",
                    "operator": {
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json.stream }}",
                    "rightValue": "matched"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "a0325682-054e-48b4-80f8-edbb83e34459",
                    "operator": {
                      "name": "filter.operator.equals",
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json.stream }}",
                    "rightValue": "unmatched"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "65942913-ea50-4e77-b881-fd62ea4722e2",
                    "operator": {
                      "name": "filter.operator.equals",
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json.stream }}",
                    "rightValue": "summary"
                  }
                ]
              }
            },
            {
              "conditions": {
                "options": {
                  "version": 2,
                  "leftValue": "",
                  "caseSensitive": true,
                  "typeValidation": "strict"
                },
                "combinator": "and",
                "conditions": [
                  {
                    "id": "ec6f09d2-4bed-4146-8e2a-3c01ae2cf64a",
                    "operator": {
                      "name": "filter.operator.equals",
                      "type": "string",
                      "operation": "equals"
                    },
                    "leftValue": "={{ $json.stream }}",
                    "rightValue": "possible_matches"
                  }
                ]
              }
            }
          ]
        },
        "options": {}
      },
      "typeVersion": 3.2
    },
    {
      "id": "7c3a7f39-a4dd-4dc3-a43a-1293481668b4",
      "name": "Coincidencias",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        3760,
        -800
      ],
      "parameters": {
        "columns": {
          "value": {
            "Notes": "={{ $json.notes }}",
            "amount": "={{ $json.amount }}",
            "date_txn": "={{ $json.date_txn }}",
            "heuristic": "={{ $json.heuristic }}",
            "reference": "={{ $json.reference }}",
            "confidence": "={{ $json.confidence }}",
            "invoice_no": "={{ $json.invoice_no }}",
            "date_invoice": "={{ $json.date_invoice }}",
            "txn_row_number": "={{ $json.txn_row_number }}",
            "invoice_row_number": "={{ $json.invoice_row_number }}"
          },
          "schema": [
            {
              "id": "invoice_no",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "invoice_no",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "invoice_row_number",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "invoice_row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "txn_row_number",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "txn_row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "amount",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "amount",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "reference",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "reference",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "date_invoice",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "date_invoice",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "date_txn",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "date_txn",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "confidence",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "confidence",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "heuristic",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "heuristic",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Notes",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Notes",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "invoice_no"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1032802624,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=1032802624",
          "cachedResultName": "Reconciled Data(Matched)"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
          "cachedResultName": "Reconciliation POC"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "8GgfaHX1qOHcB2ej",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "9b83a407-6011-4024-b203-b53e3b0cc3fa",
      "name": "Posibles Coincidencias",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        3776,
        -256
      ],
      "parameters": {
        "columns": {
          "value": {
            "reason": "={{ $json.reason }}",
            "invoice_no": "={{ $json.invoice_no }}",
            "candidate_amount": "={{ $json.candidates[0].amount }}",
            "candidate_txn_row": "={{ $json.candidates[0].txn_row_number }}",
            "candidate_date_txn": "={{ $json.candidates[0].date_txn }}",
            "invoice_row_number": "={{ $json.invoice_row_number }}",
            "candidate_reference": "={{ $json.candidates[0].reference }}"
          },
          "schema": [
            {
              "id": "invoice_no",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "invoice_no",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "invoice_row_number",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "invoice_row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "reason",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "reason",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "candidate_txn_row",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "candidate_txn_row",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "candidate_amount",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "candidate_amount",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "candidate_reference",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "candidate_reference",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "candidate_date_txn",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "candidate_date_txn",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "invoice_no"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 124117742,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=124117742",
          "cachedResultName": "Reconciled Data(Possible Matches)"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
          "cachedResultName": "Reconciliation POC"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "8GgfaHX1qOHcB2ej",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "079696bc-1b88-42ac-89f2-535df24add51",
      "name": "If",
      "type": "n8n-nodes-base.if",
      "position": [
        3760,
        -624
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "bc7cfe0d-1ce8-4c5b-96f2-6813b6abaea2",
              "operator": {
                "name": "filter.operator.equals",
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.subtype }}",
              "rightValue": "invoice"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "1bdab5fa-bc29-461c-a07b-10228d27ed83",
      "name": "Factura_No_Coincidente",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        4000,
        -736
      ],
      "parameters": {
        "columns": {
          "value": {
            "type": "={{ $json.subtype }}",
            "amount": "={{ $json.amount }}",
            "reason": "=",
            "invoice_no": "={{ $json.invoice_no }}",
            "txn_row_number": "=",
            "invoice_row_number": "={{ $json.invoice_row_number }}"
          },
          "schema": [
            {
              "id": "type",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "type",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "invoice_no",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "invoice_no",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "invoice_row_number",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "invoice_row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "txn_row_number",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "txn_row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "amount",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "amount",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "reason",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "reason",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "invoice_no"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 481952904,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=481952904",
          "cachedResultName": "Reconciled Data(Unmatched)"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
          "cachedResultName": "Reconciliation POC"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "8GgfaHX1qOHcB2ej",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "33a28f43-8d0c-480d-bf19-8cbd2436b929",
      "name": "Transacción_Bancaria_No_Coincidente",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        4016,
        -528
      ],
      "parameters": {
        "columns": {
          "value": {
            "type": "={{ $json.subtype }}",
            "amount": "={{ $json.amount }}",
            "reason": "={{ $json.reason }}",
            "invoice_no": "={{ $json.txn_row_number }}",
            "txn_row_number": "={{ $json.txn_row_number }}",
            "invoice_row_number": "="
          },
          "schema": [
            {
              "id": "type",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "type",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "invoice_no",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "invoice_no",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "invoice_row_number",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "invoice_row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "txn_row_number",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "txn_row_number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "amount",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "amount",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "reason",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "reason",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "txn_row_number"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 481952904,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=481952904",
          "cachedResultName": "Reconciled Data(Unmatched)"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
          "cachedResultName": "Reconciliation POC"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "8GgfaHX1qOHcB2ej",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "135102bc-cbdf-4a6d-a0a6-0266bda6f006",
      "name": "Resumen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        3776,
        -448
      ],
      "parameters": {
        "columns": {
          "value": {
            "stream": "={{ $json.stream }}",
            "currency": "={{ $json.currency }}",
            "totals.invoice_count": "={{ $json.totals.invoices_count }}",
            "totals.matched_count": "={{ $json.totals.matched_count }}",
            "totals.bank_credit_count": "={{ $json.totals.bank_credits_count }}",
            "totals.matched_amount_sum": "={{ $json.totals.matched_amount_sum }}",
            "totals.unmatched_bank_count": "={{ $json.totals.unmatched_bank_count }}",
            "parameters.date_windows_date": "={{ $json.parameters.date_window_days }}",
            "totals.possible_matches_count": "={{ $json.totals.possible_matches_count }}",
            "totals.unmatched_invoice_count": "={{ $json.totals.unmatched_invoices_count }}"
          },
          "schema": [
            {
              "id": "stream",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "stream",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "currency",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "currency",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "totals.invoice_count",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "totals.invoice_count",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "totals.bank_credit_count",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "totals.bank_credit_count",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "totals.matched_count",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "totals.matched_count",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "totals.possible_matches_count",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "totals.possible_matches_count",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "totals.unmatched_bank_count",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "totals.unmatched_bank_count",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "totals.unmatched_invoice_count",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "totals.unmatched_invoice_count",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "totals.matched_amount_sum",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "totals.matched_amount_sum",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "parameters.date_windows_date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "parameters.date_windows_date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 657591862,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit#gid=657591862",
          "cachedResultName": "Reconciled Data(Summary)"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/14maYcRP7pDcSyzLOwAteurr8dSEoaTtG-X7gl5iKMJ8/edit?usp=drivesdk",
          "cachedResultName": "Reconciliation POC"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "8GgfaHX1qOHcB2ej",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "7605189e-60e8-4f02-8336-e924806a2b7a",
      "name": "Limpiar y Formatear Datos",
      "type": "n8n-nodes-base.code",
      "position": [
        2896,
        -544
      ],
      "parameters": {
        "jsCode": "const items = $input.all();\n\nfunction parseFencedJson(text) {\n  if (typeof text !== 'string') {\n    throw new Error('Expected a string in `output`.');\n  }\n\n  let s = text.trim();\n  // Remove leading ```[lang]\\n and trailing ```\n  if (s.startsWith('```')) {\n    s = s.replace(/^```[a-zA-Z0-9_-]*\\n?/, ''); // drop opening fence (with optional language)\n    s = s.replace(/```$/, '');                 // drop closing fence\n  }\n\n  // In case there is any extra wrapper text, extract the JSON block\n  const start = s.indexOf('{');\n  const end = s.lastIndexOf('}');\n  if (start !== -1 && end !== -1 && end > start) {\n    s = s.slice(start, end + 1);\n  }\n\n  return JSON.parse(s);\n}\n\nconst out = [];\n\nfor (const item of items) {\n  // Support either item.json.output or item.output\n  const raw = (item.json && item.json.output) ?? item.output;\n  try {\n    const parsed = parseFencedJson(raw);\n    out.push({ json: parsed });\n  } catch (e) {\n    // If parsing fails, return a helpful error payload\n    out.push({\n      json: {\n        error: true,\n        message: e.message,\n        original: raw,\n      },\n    });\n  }\n}\nreturn out;"
      },
      "typeVersion": 2
    },
    {
      "id": "8730e70e-6c20-434c-844e-6fc6b6a1a1e8",
      "name": "Agregar Filtro de Etiqueta de Flujo",
      "type": "n8n-nodes-base.code",
      "position": [
        3104,
        -544
      ],
      "parameters": {
        "jsCode": "// Input can be [ { ... } ] or { ... }\nlet root = items[0]?.json;\nif (Array.isArray(root)) root = root[0] || {};\n\nconst out = [];\n\n// Helpers for relational schema\nconst invoices = Array.isArray(root.invoices) ? root.invoices : [];\nconst txns = Array.isArray(root.bank_transactions) ? root.bank_transactions : [];\nconst invById = new Map(invoices.map(i => [i.id, i]));\nconst txnById = new Map(txns.map(t => [t.id, t]));\n\n// ---- 1) MATCHED ----\n// Legacy: root.matched\nif (Array.isArray(root.matched)) {\n  root.matched.forEach(r => out.push({ json: { stream: 'matched', ...r } }));\n}\n\n// Relational: root.matches\nif (Array.isArray(root.matches)) {\n  root.matches.forEach(m => {\n    const inv = invById.get(m.invoice_id) || {};\n    const tx = txnById.get(m.txn_id) || {};\n    out.push({\n      json: {\n        stream: 'matched',\n        // keep original relational fields\n        ...m,\n        // enrich with row numbers & dates if available\n        invoice_row_number: inv.invoice_row_number ?? m.invoice_row_number,\n        txn_row_number: tx.txn_row_number ?? m.txn_row_number,\n        date_invoice: inv.date_invoice ?? m.date_invoice,\n        date_txn: tx.date_txn ?? m.date_txn\n      }\n    });\n  });\n}\n\n// ---- 2) UNMATCHED ----\n// Legacy nested object\nif (root?.unmatched) {\n  (root.unmatched.invoices ?? []).forEach(r =>\n    out.push({ json: { stream: 'unmatched', subtype: 'invoice', ...r } })\n  );\n  (root.unmatched.bank_transactions ?? []).forEach(r =>\n    out.push({ json: { stream: 'unmatched', subtype: 'bank_transaction', ...r } })\n  );\n}\n\n// Relational separate arrays\nif (Array.isArray(root.unmatched_invoices)) {\n  root.unmatched_invoices.forEach(r =>\n    out.push({ json: { stream: 'unmatched', subtype: 'invoice', ...r } })\n  );\n}\nif (Array.isArray(root.unmatched_bank_transactions)) {\n  root.unmatched_bank_transactions.forEach(r =>\n    out.push({ json: { stream: 'unmatched', subtype: 'bank_transaction', ...r } })\n  );\n}\n\n// ---- 3) POSSIBLE MATCHES ----\n// Legacy: already combined\nif (Array.isArray(root.possible_matches)) {\n  root.possible_matches.forEach(r =>\n    out.push({ json: { stream: 'possible_matches', ...r } })\n  );\n}\n\n// Relational: groups + candidates → combine by group\nif (Array.isArray(root.possible_match_groups) || Array.isArray(root.possible_match_candidates)) {\n  const groups = root.possible_match_groups ?? [];\n  const cands = root.possible_match_candidates ?? [];\n  const byGroup = new Map();\n  cands.forEach(c => {\n    const arr = byGroup.get(c.match_group_id) || [];\n    // enrich candidate with txn_row_number/date if available\n    const tx = txnById.get(c.txn_id) || {};\n    arr.push({\n      ...c,\n      txn_row_number: tx.txn_row_number ?? c.txn_row_number,\n      date_txn: c.date_txn ?? tx.date_txn\n    });\n    byGroup.set(c.match_group_id, arr);\n  });\n\n  groups.forEach(g => {\n    const inv = invById.get(g.invoice_id) || {};\n    out.push({\n      json: {\n        stream: 'possible_matches',\n        ...g,\n        // surface invoice info if available\n        invoice_no: inv.invoice_no,\n        invoice_row_number: inv.invoice_row_number,\n        date_invoice: inv.date_invoice,\n        candidates: byGroup.get(g.id) || []\n      }\n    });\n  });\n}\n\n// ---- 4) SUMMARY ----\n// Legacy: root.summary\nif (root.summary) {\n  out.push({ json: { stream: 'summary', ...root.summary } });\n}\n\n// Relational: same key name (already handled above)\n\n// Done\nreturn out;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "3feb7656-b1f8-47e7-807b-a0aca26da41e",
      "name": "Nota Adhesiva",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        752,
        -672
      ],
      "parameters": {
        "width": 304,
        "height": 320,
        "content": "## Manual Trigger \n**Workflow starts manually to initiate the reconciliation process on demand."
      },
      "typeVersion": 1
    },
    {
      "id": "6ae4a0ab-7afe-4a05-a275-aeeec1e3ce0a",
      "name": "Nota Adhesiva1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1168,
        -848
      ],
      "parameters": {
        "height": 704,
        "content": "## Fetch Invoices & Bank Statements \n**Retrieves invoice data and bank statement data from Google Sheets for comparison."
      },
      "typeVersion": 1
    },
    {
      "id": "2a18094a-8941-4481-9970-c66dfcbcb8d1",
      "name": "Nota Adhesiva2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1488,
        -688
      ],
      "parameters": {
        "height": 352,
        "content": "## Merge Data\nCombines both datasets into a single structured dataset for processing."
      },
      "typeVersion": 1
    },
    {
      "id": "2a645f55-b778-44a0-b4ca-6c925c0300ea",
      "name": "Nota Adhesiva3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1824,
        -720
      ],
      "parameters": {
        "width": 272,
        "height": 448,
        "content": "## Format Payload for AI\nFunction node prepares and structures the merged data into a clean JSON payload for AI analysis."
      },
      "typeVersion": 1
    },
    {
      "id": "c96fa17a-cd05-414b-96dd-ccdb5da631fa",
      "name": "Nota Adhesiva4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2192,
        -816
      ],
      "parameters": {
        "width": 512,
        "height": 672,
        "content": "## AI Reconciliation\nAI Agent analyzes the invoice and bank statement data to identify matches, discrepancies, and reconciled entries."
      },
      "typeVersion": 1
    },
    {
      "id": "f706ab77-4ffd-4d85-9d54-c65025753014",
      "name": "Nota Adhesiva5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2816,
        -720
      ],
      "parameters": {
        "width": 464,
        "height": 400,
        "content": "## Parse AI Output\n**Parses the AI response into a structured format suitable for adding back to Google Sheets."
      },
      "typeVersion": 1
    },
    {
      "id": "b7029e44-31d1-46a3-852b-a67956ff85ff",
      "name": "Nota Adhesiva6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3424,
        -912
      ],
      "parameters": {
        "width": 896,
        "height": 832,
        "content": "## Update Sheets\nAdds the reconciled data and reconciliation results into the target Google Sheet for recordkeeping."
      },
      "typeVersion": 1
    },
    {
      "id": "76021357-252b-4df9-b192-01563a251745",
      "name": "Nota Adhesiva7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -256,
        -960
      ],
      "parameters": {
        "color": 3,
        "width": 768,
        "height": 864,
        "content": "🧾 Prerequisites\n\n✅ OpenAI API Credentials\n\nRequired for the AI Reconciliation node to process and match transactions.\n\nAdd your OpenAI API key in n8n → Credentials → OpenAI.\n\n✅ Google Sheets Credentials\nNeeded to read invoice and bank statement data and to write reconciled results.\nAdd credentials in n8n → Credentials → Google Sheets.\n\n✅ Google Sheets Setup\nThe connected spreadsheet must contain the following tabs:\nInvoices – for invoice data\nBank_Statement – for bank transaction data\nReconciled_Data – for storing the AI-processed reconciliation output\n\n✅ Tab Structure & Required Headers\nInvoices Sheet Columns:\nInvoice_ID\nInvoice_Date\nCustomer_Name\nAmount\nStatus\nBank_Statement Sheet Columns:\nTransaction_ID\nTransaction_Date\nDescription\nDebit/Credit\nAmount\nReconciled_Data Sheet Columns:\nInvoice_ID\nTransaction_ID\nMatched_Status\nRemarks\nConfidence_Score\n\n⚙️ n8n Environment Setup\nEnsure all nodes are connected correctly and the workflow has permission to access the required sheets.\nTest each fetch and write operation before running the full workflow.\n\n\nTab Name and headers can be adjusted accordingly. Changes have to be made in the sheets as well accordingly"
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "975828e6-ce78-4c58-9a12-f8c53430eb85",
  "connections": {
    "079696bc-1b88-42ac-89f2-535df24add51": {
      "main": [
        [
          {
            "node": "1bdab5fa-bc29-461c-a07b-10228d27ed83",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "33a28f43-8d0c-480d-bf19-8cbd2436b929",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "36e5a7c1-aed9-4d9a-92bc-7145d5bcc302": {
      "main": [
        [
          {
            "node": "149bd1b7-90e6-4309-8f52-3c73d0711199",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1bc4151f-8b9f-402c-a4c1-f30bf6e09bc9": {
      "main": [
        [
          {
            "node": "7c3a7f39-a4dd-4dc3-a43a-1293481668b4",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "079696bc-1b88-42ac-89f2-535df24add51",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "135102bc-cbdf-4a6d-a0a6-0266bda6f006",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "9b83a407-6011-4024-b203-b53e3b0cc3fa",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "00eaa2bc-f104-4666-b071-f0de28ec1049": {
      "main": [
        [
          {
            "node": "36e5a7c1-aed9-4d9a-92bc-7145d5bcc302",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "ae6b1aee-33a9-48ea-b11e-6fd267b8e544": {
      "main": [
        [
          {
            "node": "36e5a7c1-aed9-4d9a-92bc-7145d5bcc302",
            "type": "main",
            "index": 1
          }
        ]
      ]
    },
    "1ddece06-81b0-4b81-a78e-c91ddd21b6bf": {
      "ai_languageModel": [
        [
          {
            "node": "1896c41e-96e9-4a00-be6e-ca1325fd137a",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "7605189e-60e8-4f02-8336-e924806a2b7a": {
      "main": [
        [
          {
            "node": "8730e70e-6c20-434c-844e-6fc6b6a1a1e8",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "8730e70e-6c20-434c-844e-6fc6b6a1a1e8": {
      "main": [
        [
          {
            "node": "1bc4151f-8b9f-402c-a4c1-f30bf6e09bc9",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "149bd1b7-90e6-4309-8f52-3c73d0711199": {
      "main": [
        [
          {
            "node": "1896c41e-96e9-4a00-be6e-ca1325fd137a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "87eb23a7-ed90-458f-9cd3-c98315ac8070": {
      "main": [
        [
          {
            "node": "00eaa2bc-f104-4666-b071-f0de28ec1049",
            "type": "main",
            "index": 0
          },
          {
            "node": "ae6b1aee-33a9-48ea-b11e-6fd267b8e544",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1896c41e-96e9-4a00-be6e-ca1325fd137a": {
      "main": [
        [
          {
            "node": "7605189e-60e8-4f02-8336-e924806a2b7a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}