KI-gesteuerte Rechnungsextraktion und Automatisierungssystem

• •Google-Konto + Gmail API-Anmeldedaten
• •Google Drive API-Anmeldedaten
• •Google Sheets API-Anmeldedaten
• •Google Gemini API Key

{
  "id": "EFHIQBQltrt7yPxS",
  "meta": {
    "instanceId": "3caab7a077d6a24bf913833250143556c3033c05ff2ea30885e13d0164c0cec2",
    "templateCredsSetupCompleted": true
  },
  "name": "AI-Powered Invoice Extraction & Automation System",
  "tags": [],
  "nodes": [
    {
      "id": "a6a71e48-04d8-4a73-af89-dc5ab7720788",
      "name": "Bei Chat-Nachrichteneingang",
      "type": "@n8n/n8n-nodes-langchain.chatTrigger",
      "position": [
        -256,
        672
      ],
      "webhookId": "8c5b49b9-7491-497f-9cd5-22515ea25ba0",
      "parameters": {
        "options": {
          "allowFileUploads": true,
          "allowedFilesMimeTypes": " image/*, text/*, application/pdf"
        }
      },
      "executeOnce": false,
      "typeVersion": 1.3
    },
    {
      "id": "1f5025c5-043e-4934-8ba9-9964becf178c",
      "name": "Bild1 analysieren",
      "type": "@n8n/n8n-nodes-langchain.googleGemini",
      "position": [
        128,
        672
      ],
      "parameters": {
        "text": "What's in this image? Extract all the data And give a very formatted. Output Which has Differently listed Things of invoice",
        "modelId": {
          "__rl": true,
          "mode": "list",
          "value": "models/gemini-1.5-flash",
          "cachedResultName": "models/gemini-1.5-flash"
        },
        "options": {},
        "resource": "image",
        "inputType": "binary",
        "operation": "analyze",
        "binaryPropertyName": "data0"
      },
      "credentials": {
        "googlePalmApi": {
          "id": "sSV7NS6JXz0qjL2i",
          "name": "Google Gemini(PaLM) Api account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "f504934f-3551-4399-a610-f9f8ec1cd246",
      "name": "KI-Agent1",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        448,
        672
      ],
      "parameters": {
        "text": "=Here is the invoice data - {{ $json.content.parts[0].text }}",
        "options": {
          "systemMessage": "=You have invoice data.  \nYour task is to structure that data strictly in JSON.  \n\nRules:   \n- Do not add \\n or \\ in the output.  \n\nOutput format:\n\n{\n  \"invoice_id\": \"\",\n  \"shop_name\": \"\",\n  \"date\": \"\",\n  \"Total\": \"\",\n  \"items\": [\n    {\n      \"Item_Name\": \"\",\n      \"Quantity\": \"\",\n      \"Unit_Price\": \"\",\n      \"Currency\": \"\"\n    }\n  ],\n  \"optional_fields\": {\n    \"Entry_ID\": \"\",\n    \"Time\": \"\",\n    \"Type\": \"\",\n    \"Category\": \"\",\n    \"Subcategory\": \"\",\n    \"Description\": \"\",\n    \"Shop_Address\": \"\",\n    \"Payment_Method\": \"\",\n    \"GST_Rate\": \"\",\n    \"GST_Amount\": \"\",\n    \"Receipt_Link\": \"\",\n    \"Receipt_Number\": \"\",\n    \"Remarks\": \"\",\n    \"Tags\": \"\",\n    \"Created_Date\": \"\",\n    \"Modified_Date\": \"\",\n    \"Status\": \"\",\n    \"Duplicate_Check\": \"\"\n  }\n}\n"
        },
        "promptType": "define"
      },
      "typeVersion": 2.2
    },
    {
      "id": "f0999a88-8eb2-407f-8d50-6ca4118bb3df",
      "name": "Google Gemini Chat Model1",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        512,
        928
      ],
      "parameters": {
        "options": {}
      },
      "credentials": {
        "googlePalmApi": {
          "id": "sSV7NS6JXz0qjL2i",
          "name": "Google Gemini(PaLM) Api account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "0ee67ef8-d38c-4a78-9267-5e482ef7b7fd",
      "name": "Daten abrufen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1280,
        672
      ],
      "parameters": {
        "options": {},
        "filtersUI": {
          "values": [
            {
              "lookupValue": "={{ $json.output.invoice_id }}",
              "lookupColumn": "Entry_ID"
            }
          ]
        },
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1240712847,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1T1oFlLqrauskPHAHbRixnIbx4wSK7pY3J5EGlpFzZTQ/edit#gid=1240712847",
          "cachedResultName": "Expense_Tracker"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1T1oFlLqrauskPHAHbRixnIbx4wSK7pY3J5EGlpFzZTQ",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1T1oFlLqrauskPHAHbRixnIbx4wSK7pY3J5EGlpFzZTQ/edit?usp=drivesdk",
          "cachedResultName": "expense_tracker_n8n"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "2cLBwxQBfcaJ1DCN",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.7,
      "alwaysOutputData": true
    },
    {
      "id": "6c465765-d7ca-4f09-9720-8799132ec5d0",
      "name": "Make Daten im json Strukturformat",
      "type": "n8n-nodes-base.set",
      "position": [
        912,
        672
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "9f859c68-cdaa-41d0-bc15-a6ab7db24906",
              "name": "output",
              "type": "object",
              "value": "={{ $json.output }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "c25a3bdb-deef-4d9f-a2e3-2471bc1107fa",
      "name": "Prüfen ob Daten in Tabelle existieren",
      "type": "n8n-nodes-base.if",
      "position": [
        1680,
        672
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "3a5e1594-1d46-4a0c-81c9-dcdc5243931c",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ Object.keys($items(\"Get data\")[0].json).length === 0 }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "0997e665-a8c5-4bdb-90f5-5d5304a0f355",
      "name": "Rechnung in Drive hochladen",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        80,
        912
      ],
      "parameters": {
        "name": "=invoice_data",
        "driveId": {
          "__rl": true,
          "mode": "list",
          "value": "My Drive"
        },
        "options": {},
        "folderId": {
          "__rl": true,
          "mode": "list",
          "value": "root",
          "cachedResultUrl": "https://drive.google.com/drive",
          "cachedResultName": "/ (Root folder)"
        },
        "inputDataFieldName": "data0"
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "dpqimR5rZMDgJjvs",
          "name": "Google Drive account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "65f8bf39-6813-4def-8cac-274eb5db6c9e",
      "name": "Haftnotiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -384,
        352
      ],
      "parameters": {
        "width": 368,
        "height": 496,
        "content": "## Node 1: **When chat message received**\n\nThis is a *trigger node* that starts the workflow whenever a new chat message is received in n8n.\nIt supports file uploads, meaning users can directly send invoices or related files in the chat.\nThe `webhookId` ensures it listens for incoming events, and it runs every time a new message comes (not just once).\nSo essentially, this node is the *entry point* that kicks off the entire invoice-processing workflow."
      },
      "typeVersion": 1
    },
    {
      "id": "bb06ce88-cbc3-4003-a2a8-3fb9b576d371",
      "name": "Keine fehlenden Felder - Neue Daten mit Payload 2 hinzufügen",
      "type": "n8n-nodes-base.code",
      "position": [
        3152,
        480
      ],
      "parameters": {
        "jsCode": "// Function Item node\n// Ensures you only pass through the payload JSON if status = \"ok\"\n\n// 1. Get current item\nconst input = $json;\n\n// 2. Check status\nif (input.status && input.status === \"ok\") {\n  // ✅ Status is ok → use the payload JSON\n  // 🔽 PLACEHOLDER: replace this with the node where your invoice payload lives\n  const payload = $('Make data in json structure format').first().json.output;\n\n  return payload;\n\n} else {\n  // ❌ Status not ok → return error message\n  return {\n    status: \"error\",\n    message: \"Missing Mandatory field(s) or status not ok\"\n  };\n}\n"
      },
      "typeVersion": 2
    },
    {
      "id": "070e102e-3fd3-4caf-81b6-82eae65a3b00",
      "name": "Wenn - Fehlende Felder prüfen",
      "type": "n8n-nodes-base.if",
      "position": [
        2768,
        496
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "6b4e00d8-0504-44a0-bf5b-5d5dd867138e",
              "operator": {
                "name": "filter.operator.equals",
                "type": "string",
                "operation": "equals"
              },
              "leftValue": "={{ $json.status }}",
              "rightValue": "ok"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "84f5c157-36a7-491c-ba9e-52e5d3b08039",
      "name": "Fehlende-Felder-Fehler per Mail senden",
      "type": "n8n-nodes-base.gmail",
      "position": [
        3440,
        976
      ],
      "webhookId": "acbee7e7-047d-404b-a6c1-c09861fc3c67",
      "parameters": {
        "sendTo": "xyz@gmail.com",
        "message": "=Missing filed - {{ $json.missing_fields.map(field => field).join(', ') }}\n",
        "options": {},
        "subject": "Missing filed error"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "zystIXitEOECXALa",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "c33ae480-922e-4596-bd5a-dcd3a6b4aa93",
      "name": "Doppelteintrag per Mail senden",
      "type": "n8n-nodes-base.gmail",
      "position": [
        2080,
        992
      ],
      "webhookId": "6ef65cb6-499f-4893-9739-b5f277bd8931",
      "parameters": {
        "sendTo": "xyz@gmail.com",
        "message": "=<html>\n  <body style=\"font-family: Arial, sans-serif; color: #333;\">\n    <h2 style=\"color:#d9534f;\">⚠ Invoice Already Exists</h2>\n    <p>Hello,</p>\n    <p>The invoice you attempted to add already exists in the Google Sheet. Here are the details:</p>\n\n    <table style=\"border-collapse: collapse; width: 100%; margin: 16px 0;\">\n      <tr>\n        <td style=\"border: 1px solid #ccc; padding: 8px;\"><strong>Invoice ID</strong></td>\n        <td style=\"border: 1px solid #ccc; padding: 8px;\">{{ $json.invoice_id }}</td>\n      </tr>\n      <tr>\n        <td style=\"border: 1px solid #ccc; padding: 8px;\"><strong>Company Name</strong></td>\n        <td style=\"border: 1px solid #ccc; padding: 8px;\">{{ $json.Shop_Name }}</td>\n      </tr>\n      <tr>\n        <td style=\"border: 1px solid #ccc; padding: 8px;\"><strong>Date</strong></td>\n        <td style=\"border: 1px solid #ccc; padding: 8px;\"> {{ $json.Date }} </td>\n      </tr>\n      <tr>\n        <td style=\"border: 1px solid #ccc; padding: 8px;\"><strong>Total</strong></td>\n        <td style=\"border: 1px solid #ccc; padding: 8px;\">{{ $json.Total_Amount }}</td>\n      </tr>\n      <tr>\n        <td style=\"border: 1px solid #ccc; padding: 8px;\"><strong>Items</strong></td>\n        <td style=\"border: 1px solid #ccc; padding: 8px;\">{{ $json.Item_Name }}</td>\n      </tr>\n    </table>\n\n    <p style=\"margin-top:16px;\">✅ No changes were made to your sheet, since this record already exists.</p>\n    <p>Kind regards,<br>Your Automated Invoice System</p>\n  </body>\n</html>\n",
        "options": {},
        "subject": "=Invoice Already Exists – {{ $json.invoice_id }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "zystIXitEOECXALa",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "444d68a3-9a32-4caf-a930-2b4b83cc92d9",
      "name": "Neue Daten mit Payload hinzufügen",
      "type": "n8n-nodes-base.code",
      "position": [
        2064,
        496
      ],
      "parameters": {
        "jsCode": "// Function Item node\n// This makes sure you always get the invoice JSON payload, even if Get data is empty.\n\n// 1. Get what Google Sheets returned\nconst sheetItem = $items(\"Get data\") && $items(\"Get data\")[0] \n  ? $items(\"Get data\")[0].json \n  : {};\n\n// 2. Check if it's empty\nconst sheetEmpty = Object.keys(sheetItem).length === 0;\n\n// 3. If empty → use your original invoice JSON instead\n//    >>> REPLACE the placeholder below with the node that has your invoice JSON <<<\n// Example: $items(\"Edit Fields\")[0].json OR paste your raw invoice object\nconst invoicePayload = sheetEmpty \n  ? (\n      // 🔽 PLACEHOLDER: replace this line with your real JSON source\n      $('Make data in json structure format').first().json.output\n    )\n  : sheetItem;\n\n// 4. Return only the payload JSON\nreturn invoicePayload;\n"
      },
      "typeVersion": 2
    },
    {
      "id": "5f78f6aa-9b9a-46b5-8d92-5b2d8cc89be2",
      "name": "Daten zu Sheet hinzufügen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        3568,
        480
      ],
      "parameters": {
        "columns": {
          "value": {
            "Date": "={{ $json.date }}",
            "Time": "=",
            "Type": "=",
            "Status": "={{ $json.optional_fields.Status }}",
            "Category": "=",
            "Currency": "={{ $json.items[3].Currency }}",
            "Entry_ID": "={{ $json.payload.invoice_id }}",
            "GST_Rate": "={{ $json.optional_fields.GST_Rate }}",
            "Quantity": "={{ $json.items.map(item => item.Quantity).join(', ') }}",
            "Item_Name": "={{ $json.items.map(item => item.Item_Name).join(', ') }}",
            "Shop_Name": "={{ $json.shop_name }}",
            "GST_Amount": "={{ $json.optional_fields.GST_Amount }}",
            "Unit_Price": "={{ $json.items.map(item => item.Unit_Price).join(', ') }}\n",
            "invoice_id": "={{ $json.invoice_id }}",
            "Description": "={{ $json.optional_fields.Description }}",
            "Subcategory": "=",
            "Created_Date": "={{ $json.date }}",
            "Shop_Address": "={{ $json.optional_fields.Shop_Address }}",
            "Total_Amount": "={{ $json.Total }}",
            "Modified_Date": "={{ $json.optional_fields.Created_Date }}",
            "Payment_Method": "={{ $json.optional_fields.Payment_Method }}"
          },
          "schema": [
            {
              "id": "Entry_ID",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Entry_ID",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Time",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Type",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Type",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Category",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Category",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Subcategory",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Subcategory",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Item_Name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Item_Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Description",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Description",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Quantity",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Quantity",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Unit",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Unit",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Unit_Price",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Unit_Price",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Total_Amount",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Total_Amount",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Currency",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Currency",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Shop_Name",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Shop_Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Shop_Address",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Shop_Address",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Payment_Method",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Payment_Method",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "GST_Rate",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "GST_Rate",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "GST_Amount",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "GST_Amount",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Receipt_Link",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Receipt_Link",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Receipt_Number",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Receipt_Number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Remarks",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Remarks",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Tags",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Tags",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Created_Date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Created_Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Modified_Date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Modified_Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Status",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Status",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Duplicate_Check",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Duplicate_Check",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "invoice_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "invoice_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "shop_name",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "shop_name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "date",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Total",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Total",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "items",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "items",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "optional_fields",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "optional_fields",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [
            "Entry_ID"
          ],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 1240712847,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1T1oFlLqrauskPHAHbRixnIbx4wSK7pY3J5EGlpFzZTQ/edit#gid=1240712847",
          "cachedResultName": "Expense_Tracker"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1T1oFlLqrauskPHAHbRixnIbx4wSK7pY3J5EGlpFzZTQ",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1T1oFlLqrauskPHAHbRixnIbx4wSK7pY3J5EGlpFzZTQ/edit?usp=drivesdk",
          "cachedResultName": "expense_tracker_n8n"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "2cLBwxQBfcaJ1DCN",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.7
    },
    {
      "id": "5042aa5c-5d27-4e91-bb34-48adbb466148",
      "name": "Pflichtfelder prüfen",
      "type": "n8n-nodes-base.code",
      "position": [
        2400,
        496
      ],
      "parameters": {
        "jsCode": "// Function Item node\n// Check if mandatory fields are present before appending to Google Sheet\n\n// 1. Get the invoice payload (replace with your correct source node if needed)\nconst payload = $('Make data in json structure format').first().json.output;\n\n// 2. Define mandatory fields\nconst mandatoryFields = [\"invoice_id\", \"shop_name\", \"date\", \"Total\", \"items\"];\n\n// 3. Check missing fields\nconst missing = mandatoryFields.filter(f => {\n  if (f === \"items\") {\n    return !payload.items || !Array.isArray(payload.items) || payload.items.length === 0;\n  }\n  return !payload[f] || payload[f].toString().trim() === \"\";\n});\n\n// 4. If missing → return error\nif (missing.length > 0) {\n  return {\n    json: {\n      status: \"error\",\n      message: \"Missing Mandatory field(s)\",\n      missing_fields: missing\n    }\n  };\n}\n\n// 5. If all present → return full payload\nreturn {\n  json: {\n    status: \"ok\",\n    payload // 🔽 This is the invoice JSON you’ll pass to Google Sheets\n  }\n};\n"
      },
      "typeVersion": 2
    },
    {
      "id": "e9f03e55-74ba-4576-8807-0b34490a809f",
      "name": "Erfolgsmail senden",
      "type": "n8n-nodes-base.gmail",
      "position": [
        3968,
        480
      ],
      "webhookId": "6ef65cb6-499f-4893-9739-b5f277bd8931",
      "parameters": {
        "sendTo": "xyz@gmail.com",
        "message": "=<!doctype html>\n<html>\n<head>\n<meta charset=\"utf-8\" />\n<meta name=\"viewport\" content=\"width=device-width,initial-scale=1\" />\n<style>\n  /* Basic reset for email */\n  body, table, td, a { -webkit-text-size-adjust: 100%; -ms-text-size-adjust: 100%; }\n  table { border-collapse: collapse !important; }\n  img { border: 0; height: auto; line-height: 100%; outline: none; text-decoration: none; display: block; }\n  body { margin: 0; padding: 0; width: 100% !important; font-family: -apple-system, BlinkMacSystemFont, \"Segoe UI\", Roboto, \"Helvetica Neue\", Arial, sans-serif; background-color: #f4f6fb; color: #222; }\n\n  /* Container */\n  .email-wrap {\n    width: 100%;\n    padding: 28px 16px;\n    background: linear-gradient(180deg, #f7f9ff 0%, #f4f6fb 100%);\n  }\n  .card {\n    max-width: 680px;\n    margin: 0 auto;\n    background: #ffffff;\n    border-radius: 14px;\n    box-shadow: 0 10px 30px rgba(29,41,81,0.08);\n    overflow: hidden;\n    border: 1px solid rgba(29,41,81,0.04);\n  }\n  .card-header {\n    padding: 28px 28px 18px 28px;\n    background: linear-gradient(90deg,#5b8cff,#2d6df6);\n    color: #fff;\n  }\n  .logo {\n    font-weight: 700;\n    letter-spacing: -0.2px;\n    font-size: 18px;\n  }\n  .title {\n    margin: 8px 0 0 0;\n    font-size: 20px;\n    font-weight: 700;\n  }\n  .card-body {\n    padding: 22px 28px;\n  }\n\n  /* Info block */\n  .info {\n    display: flex;\n    gap: 14px;\n    flex-wrap: wrap;\n    margin-bottom: 18px;\n  }\n  .info .info-item {\n    flex: 1 1 240px;\n    background: linear-gradient(180deg,#fbfdff,#f7f9ff);\n    border-radius: 10px;\n    padding: 12px 14px;\n    border: 1px solid rgba(13,34,84,0.04);\n  }\n  .info .label {\n    display: block;\n    font-size: 12px;\n    color: #5b6b8a;\n    margin-bottom: 6px;\n  }\n  .info .value {\n    font-weight: 600;\n    color: #17233d;\n    font-size: 15px;\n  }\n\n  /* Items & totals */\n  .items {\n    width: 100%;\n    border-collapse: collapse;\n    margin-top: 8px;\n    margin-bottom: 14px;\n    font-size: 14px;\n  }\n  .items th {\n    text-align: left;\n    font-size: 12px;\n    font-weight: 700;\n    color: #4b5b7a;\n    padding: 8px 0;\n    border-bottom: 1px solid rgba(72,84,112,0.06);\n  }\n  .items td {\n    padding: 10px 0;\n    color: #243142;\n    vertical-align: top;\n  }\n  .items .muted { color: #73809a; font-weight: 500; font-size: 13px; }\n\n  .total-row {\n    display: flex;\n    justify-content: space-between;\n    align-items: center;\n    padding-top: 14px;\n    border-top: 1px dashed rgba(29,41,81,0.06);\n    margin-top: 10px;\n  }\n  .total-label { color: #5b6b8a; font-weight: 600; }\n  .total-value {\n    background: linear-gradient(90deg,#fff0f6,#fff);\n    color: #d6336c;\n    font-weight: 800;\n    font-size: 18px;\n    padding: 8px 14px;\n    border-radius: 8px;\n    border: 1px solid rgba(214,51,108,0.08);\n  }\n\n  /* Footer */\n  .footer {\n    padding: 18px 28px;\n    font-size: 13px;\n    color: #627089;\n    background: #fbfdff;\n    border-top: 1px solid rgba(29,41,81,0.02);\n  }\n  .cta {\n    display:inline-block;\n    margin-top: 10px;\n    padding: 8px 14px;\n    border-radius: 10px;\n    font-weight: 700;\n    text-decoration: none;\n    color: #fff;\n    background: linear-gradient(90deg,#2d6df6,#5b8cff);\n  }\n\n  /* Responsive */\n  @media (max-width:480px) {\n    .card-header { padding: 20px; }\n    .card-body { padding: 16px; }\n    .info { gap: 8px; }\n    .info .info-item { flex-basis: 100%; }\n  }\n</style>\n</head>\n<body>\n  <div class=\"email-wrap\" role=\"article\" aria-label=\"Invoice notification\">\n    <div class=\"card\" role=\"article\">\n      <div class=\"card-header\">\n        <div class=\"logo\">Invoice Bot</div>\n        <div class=\"title\">New Invoice Added</div>\n      </div>\n\n      <div class=\"card-body\">\n        <!-- Basic info -->\n        <div class=\"info\" aria-hidden=\"false\">\n          <div class=\"info-item\" role=\"group\" aria-label=\"Invoice ID\">\n            <span class=\"label\">Invoice ID</span>\n            <div class=\"value\">New INVOICE - {{ $json.invoice_id }}</div>\n          </div>\n\n          <div class=\"info-item\" role=\"group\" aria-label=\"Company name\">\n            <span class=\"label\">Company</span>\n            <div class=\"value\">{{ $json.Shop_Name }}</div>\n          </div>\n\n          <div class=\"info-item\" role=\"group\" aria-label=\"Date\">\n            <span class=\"label\">Date</span>\n            <div class=\"value\">{{ $json.Date }}</div>\n          </div>\n        </div>\n\n        <!-- Items table -->\n        <table class=\"items\" role=\"table\" aria-label=\"Invoice items\">\n          <thead>\n            <tr>\n              <th>Items</th>\n              <th style=\"text-align:right\">Price</th>\n            </tr>\n          </thead>\n          <tbody>\n            <tr>\n              <td class=\"muted\">{{ $json.Item_Name }}</td>\n              <td style=\"text-align:right;\" class=\"muted\">{{ $json.Total_Amount }}</td>\n            </tr>\n          </tbody>\n        </table>\n\n        <!-- Total -->\n        <div class=\"total-row\" role=\"note\" aria-live=\"polite\">\n          <div class=\"total-label\">Total</div>\n          <div class=\"total-value\">{{ $json.Total_Amount }}</div>\n        </div>\n\n        <!-- Call to action / small note -->\n        <div style=\"margin-top:16px; font-size:13px; color:#5b6b8a;\">\n          Saved to your sheet and Drive. <a href=\"#\" class=\"cta\">Open Dashboard</a>\n        </div>\n      </div>\n\n      <div class=\"footer\">\n        <div>Auto-synced by your Invoice System • <strong>{{ $json.invoice_id }}</strong></div>\n        <div style=\"margin-top:8px;\">If something looks off, check the original file in your Drive or open the dashboard to edit.</div>\n      </div>\n    </div>\n  </div>\n</body>\n</html>\n",
        "options": {},
        "subject": "=New invoice - {{ $json.invoice_id }}  added in the sheet. "
      },
      "credentials": {
        "gmailOAuth2": {
          "id": "zystIXitEOECXALa",
          "name": "Gmail account"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "3a1e1818-7feb-4a7a-9d9d-a98b7bbb3c77",
      "name": "Haftnotiz2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        368,
        352
      ],
      "parameters": {
        "color": 5,
        "width": 384,
        "height": 512,
        "content": "# Node 3: AI Agent1\n\nThis node acts as an AI processing step that takes the raw extracted invoice text and restructures it into a strict **JSON format**.\nIt applies a *system message* that defines clear rules, like avoiding unwanted symbols (`\\n`, `\\`) and following a fixed JSON schema with fields such as `invoice_id`, `shop_name`, `date`, `Total`, and `items`.\nIt also includes an `optional_fields` object to capture extra details like `GST`, `Payment_Method`, or `Remarks`.\nIn short, this node ensures the invoice data is properly formatted and standardized for further processing.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "016dec77-8044-4bf1-91e2-04347f01b81e",
      "name": "Haftnotiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        0,
        352
      ],
      "parameters": {
        "color": 3,
        "width": 352,
        "height": 496,
        "content": "## Node 2: **Analyze image1**\n\nThis node uses *Google Gemini (PaLM API)* to analyze the uploaded invoice image.\nIt takes the binary file (`data0`) and processes it through the **Gemini Vision Model** (`gemini-1.5-flash`).\nThe prompt clearly asks Gemini to *extract all data* from the invoice and return it in a *well-formatted output* with different listed invoice fields.\nIn short, this node is responsible for converting the *raw invoice image* into *structured text data* that the next steps can use.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "d04bddae-b8e8-4bdb-a12b-4cad195b739f",
      "name": "Haftnotiz3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        368,
        880
      ],
      "parameters": {
        "color": 6,
        "width": 384,
        "height": 544,
        "content": "\n\n\n\n\n   \n\n\n\n\n\n\n\n\n\n\n\n## Node 4: Google Gemini Chat Model1\n\nThis node provides the **language model backend** for the AI Agent.\nIt connects to Google Gemini (PaLM API) and powers the reasoning, formatting, and restructuring done in the previous step.\nWhile the AI Agent defines the instructions, this node actually executes them by generating the structured response.\nIn short, it serves as the *engine* that interprets prompts and produces the cleaned JSON invoice data.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "7e2078a0-b8ee-48dc-9649-06695fa7b10b",
      "name": "Haftnotiz4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1152,
        352
      ],
      "parameters": {
        "width": 368,
        "height": 512,
        "content": "## Node 6: **Get data**\n\nThis node connects to a **Google Sheet** that acts as the *invoice database*.\nIt performs a lookup using the `Entry_ID` column and compares it with the current invoice’s `invoice_id`.\nIf a matching record is found, the invoice already exists; otherwise, it means the invoice is new.\nIn short, this node prevents duplication by checking if the invoice is already present in the sheet.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "b147bb54-67f2-41fd-9f58-e68f58e82dfc",
      "name": "Haftnotiz5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        768,
        352
      ],
      "parameters": {
        "color": 2,
        "width": 368,
        "height": 512,
        "content": "## Node 5: **Make data in JSON structure format**\n\nThis node is a **Set node** that organizes the AI Agent’s output into a clean JSON object.\nIt assigns the extracted invoice data to a single field called *output* for easier handling in the following steps.\nBy doing this, all structured invoice details are stored in one consistent object.\nIn short, this node acts as a *packaging step* that ensures the data is well-prepared before database checks.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "4fe6abc8-d092-4ae5-aab9-76b31d157e65",
      "name": "Haftnotiz6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1936,
        160
      ],
      "parameters": {
        "color": 4,
        "width": 336,
        "height": 496,
        "content": "## Node 8: **New data add using payload**\n\nThis node is a **Code node** that ensures proper handling of the invoice data before moving forward.\nIt first checks if the Google Sheets lookup returned anything or not.\nIf no data exists, it uses the freshly created invoice JSON payload from the *Make data in JSON structure format* node.\nIn short, this node guarantees that the workflow always has a valid invoice JSON to work with, even when the sheet is empty.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "65ff906c-24b0-43fb-af3a-ed8b2382057f",
      "name": "Haftnotiz7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2288,
        160
      ],
      "parameters": {
        "color": 3,
        "width": 336,
        "height": 496,
        "content": "## Node 9: **Check Mandatory fields**\n\nThis is a **Code node** that validates the invoice data.\nIt checks if all *mandatory fields* (`invoice_id`, `shop_name`, `date`, `Total`, and `items`) are present and not empty.\nIf any of these fields are missing, it returns an error response with the list of missing fields.\nIn short, this node acts as a *validation checkpoint* to make sure only complete invoices move forward.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "257b48c4-2f8d-4ead-9d45-37b5130151a6",
      "name": "Haftnotiz8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2640,
        160
      ],
      "parameters": {
        "color": 5,
        "width": 352,
        "height": 496,
        "content": "## Node 10: **If – check missing field**\n\nThis is an **If node** that decides the workflow path based on the validation status.\nIt checks the `status` field returned by the *Check Mandatory fields* node.\nIf the status is `\"ok\"`, the workflow continues to add the invoice to the sheet; if not, it sends an error notification.\nIn short, this node acts as a *decision gate* for handling complete vs. incomplete invoice data.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "d36fab00-4dc9-44e9-b071-e77500233c3c",
      "name": "Haftnotiz9",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3008,
        160
      ],
      "parameters": {
        "width": 400,
        "height": 496,
        "content": "## Node 11: **no missing field – new data add using payload 2**\n\nThis is a **Code node** that passes forward only the valid invoice JSON when the status is `\"ok\"`.\nIt retrieves the structured payload from the *Make data in JSON structure format* node and ignores any invalid data.\nIf the status is not okay, it instead returns an error message.\nIn short, this node ensures that only *clean and verified invoice data* moves on to the sheet append step.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "c1e60d32-3270-4440-ab23-dd8f0a3fe778",
      "name": "Haftnotiz10",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3424,
        160
      ],
      "parameters": {
        "color": 5,
        "width": 384,
        "height": 496,
        "content": "## Node 12: **Append data to sheet**\n\nThis node writes the validated invoice data into the **Google Sheet**.\nIt uses the *append* operation to add a new row with mapped fields such as `Entry_ID`, `Date`, `Shop_Name`, `Item_Name`, `Quantity`, `Total_Amount`, and more.\nOptional fields like `GST`, `Payment_Method`, or `Shop_Address` are also included if available.\nIn short, this node is responsible for *saving the final invoice record* into the expense tracker sheet.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "7641cc35-64e1-439d-84bb-4631737d7dd8",
      "name": "Haftnotiz11",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3824,
        160
      ],
      "parameters": {
        "width": 384,
        "height": 496,
        "content": "## Node 13: **Send successful email**\n\nThis node sends a confirmation email via **Gmail** once a new invoice is successfully added to the sheet.\nThe email includes key details like *Invoice ID, Company Name, Date, Total Price,* and a list of *Items*.\nIt uses dynamic placeholders so that each email is customized with the actual invoice data.\nIn short, this node notifies the user that the invoice has been recorded successfully.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "89bd82ca-4f6b-42eb-8899-517915dfb83e",
      "name": "Haftnotiz12",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1536,
        352
      ],
      "parameters": {
        "color": 3,
        "width": 384,
        "height": 512,
        "content": "## Node 7: **check if Data exist or not in table**\n\nThis is an **If node** that checks whether the invoice already exists in the Google Sheet.\nIt does this by verifying if the `Get data` node returned any results.\nIf the sheet is empty for that `invoice_id`, it means the invoice is new; if not, it’s a duplicate.\nIn short, this node decides whether to add new data or handle the case of an existing invoice.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "6b96884c-eefe-4ec5-a5ce-e148e2130901",
      "name": "Haftnotiz13",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1936,
        672
      ],
      "parameters": {
        "width": 400,
        "height": 512,
        "content": "## Node 8.1: **Duplicate entry send mail**\n\nThis node sends an **email notification via Gmail** when an invoice already exists in the sheet.\nThe email is formatted in *HTML* and includes details like *Invoice ID, Company Name, Date, Total Amount,* and *Items*.\nIt clearly states that no changes were made since the record is a duplicate.\nIn short, this node alerts the user about duplicate invoices to prevent unnecessary data duplication.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "9b5aeb91-0f7f-465a-b774-bb1f5fed0869",
      "name": "Haftnotiz14",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        3264,
        672
      ],
      "parameters": {
        "color": 3,
        "width": 448,
        "height": 496,
        "content": "## Node 11.1: **Send missing field error on mail**\n\nThis node sends an **error notification email via Gmail** when mandatory fields are missing from the invoice.\nThe email subject highlights the error, and the body lists all the missing fields in a readable format.\nThis helps the user quickly identify what information was not captured or provided.\nIn short, this node ensures the user is informed whenever incomplete invoice data is detected.\n"
      },
      "typeVersion": 1
    },
    {
      "id": "374ea423-b33d-4e4e-9a3e-ec3adbffbbbe",
      "name": "Haftnotiz15",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -96,
        864
      ],
      "parameters": {
        "color": 5,
        "width": 448,
        "height": 480,
        "content": "\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n## Node 2.1: **Upload invoice to drive**\n\nThis node uploads the original invoice file to **Google Drive** for safe storage and backup.\nIt takes the uploaded file (`data0`) and saves it into the user’s Google Drive, typically in the root folder unless specified otherwise.\nThe stored invoice acts as a reference for future verification or auditing.\nIn short, this node ensures that every invoice has a *permanent copy* stored in Drive alongside the processed data.\n"
      },
      "typeVersion": 1
    }
  ],
  "active": true,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "d48a8383-b848-421a-885c-6a06a77392e2",
  "connections": {
    "0ee67ef8-d38c-4a78-9267-5e482ef7b7fd": {
      "main": [
        [
          {
            "node": "c25a3bdb-deef-4d9f-a2e3-2471bc1107fa",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f504934f-3551-4399-a610-f9f8ec1cd246": {
      "main": [
        [
          {
            "node": "6c465765-d7ca-4f09-9720-8799132ec5d0",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "1f5025c5-043e-4934-8ba9-9964becf178c": {
      "main": [
        [
          {
            "node": "f504934f-3551-4399-a610-f9f8ec1cd246",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5f78f6aa-9b9a-46b5-8d92-5b2d8cc89be2": {
      "main": [
        [
          {
            "node": "e9f03e55-74ba-4576-8807-0b34490a809f",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "5042aa5c-5d27-4e91-bb34-48adbb466148": {
      "main": [
        [
          {
            "node": "070e102e-3fd3-4caf-81b6-82eae65a3b00",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "0997e665-a8c5-4bdb-90f5-5d5304a0f355": {
      "main": [
        []
      ]
    },
    "f0999a88-8eb2-407f-8d50-6ca4118bb3df": {
      "ai_languageModel": [
        [
          {
            "node": "f504934f-3551-4399-a610-f9f8ec1cd246",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "070e102e-3fd3-4caf-81b6-82eae65a3b00": {
      "main": [
        [
          {
            "node": "bb06ce88-cbc3-4003-a2a8-3fb9b576d371",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "84f5c157-36a7-491c-ba9e-52e5d3b08039",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "444d68a3-9a32-4caf-a930-2b4b83cc92d9": {
      "main": [
        [
          {
            "node": "5042aa5c-5d27-4e91-bb34-48adbb466148",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a6a71e48-04d8-4a73-af89-dc5ab7720788": {
      "main": [
        [
          {
            "node": "1f5025c5-043e-4934-8ba9-9964becf178c",
            "type": "main",
            "index": 0
          },
          {
            "node": "0997e665-a8c5-4bdb-90f5-5d5304a0f355",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "6c465765-d7ca-4f09-9720-8799132ec5d0": {
      "main": [
        [
          {
            "node": "0ee67ef8-d38c-4a78-9267-5e482ef7b7fd",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "c25a3bdb-deef-4d9f-a2e3-2471bc1107fa": {
      "main": [
        [
          {
            "node": "444d68a3-9a32-4caf-a930-2b4b83cc92d9",
            "type": "main",
            "index": 0
          }
        ],
        [
          {
            "node": "c33ae480-922e-4596-bd5a-dcd3a6b4aa93",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "bb06ce88-cbc3-4003-a2a8-3fb9b576d371": {
      "main": [
        [
          {
            "node": "5f78f6aa-9b9a-46b5-8d92-5b2d8cc89be2",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}