Rechnungsdatenverarbeitung-von-Telegram-zu-Google-Sheets-mit-OCR-und-Gemini-KI

• •Telegram Bot Token
• •Google Drive API-Anmeldedaten
• •Möglicherweise sind Ziel-API-Anmeldedaten erforderlich
• •Google Sheets API-Anmeldedaten
• •Google Gemini API Key

{
  "id": "cuxPaflo88ySZROw",
  "meta": {
    "instanceId": "db31bea69a53eada00777682bd2f0392fe4ec4364a135252e7113d3d2d2c1eb4",
    "templateCredsSetupCompleted": true
  },
  "name": "Process & Extract Invoice Data from Telegram to Google Sheets with OCR & Gemini AI",
  "tags": [],
  "nodes": [
    {
      "id": "a1331d56-97bd-4860-87b8-a34823684def",
      "name": "Telegram-Trigger1",
      "type": "n8n-nodes-base.telegramTrigger",
      "position": [
        -96,
        1024
      ],
      "webhookId": "c6a9a358-6607-4e0e-8bb9-3d065f1716aa",
      "parameters": {
        "updates": [
          "message"
        ],
        "additionalFields": {}
      },
      "credentials": {
        "telegramApi": {
          "id": "zoS34yaqfznbZ6AL",
          "name": "Telegram account"
        }
      },
      "typeVersion": 1.1
    },
    {
      "id": "bd2ea7b4-0d94-4db5-bd0a-4c673ce99e40",
      "name": "Datei herunterladen1",
      "type": "n8n-nodes-base.telegram",
      "position": [
        96,
        1024
      ],
      "webhookId": "ebd8e13c-36b7-4617-996b-73f2324524de",
      "parameters": {
        "fileId": "={{ $json.message.document.file_id }}",
        "resource": "file",
        "additionalFields": {}
      },
      "credentials": {
        "telegramApi": {
          "id": "zoS34yaqfznbZ6AL",
          "name": "Telegram account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "f73b5682-1048-465b-85ba-c7ba3735ad58",
      "name": "Update Database1",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        976,
        1008
      ],
      "parameters": {
        "columns": {
          "value": {
            "Date": "={{ $json.output.invoiceDate }}",
            "Notes": "={{ $json.output.notes }}",
            "Due Date": "={{ $json.output.dueDate }}",
            "Invoice Number": "={{ $json.output.invoiceNumber }}",
            "Billing Address": "={{ $json.output.billingAddress }}",
            "Total Amount ($)": "={{ $json.output.totalAmount }}"
          },
          "schema": [
            {
              "id": "Invoice Number",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Invoice Number",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Date",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Total Amount ($)",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Total Amount ($)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Billing Address",
              "type": "string",
              "display": true,
              "required": false,
              "displayName": "Billing Address",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Due Date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Due Date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Notes",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Notes",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Link",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Link",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {
          "useAppend": true
        },
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/15FOh4i1wwy7zv-k4foZc8ubMkMLz_-BibPJKTZ-IziM/edit#gid=0",
          "cachedResultName": "Sheet1"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1q0lbD2QTWpjQmRaRvPjLvhc_opSlOA5v9Rng8cC7XJ4",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1q0lbD2QTWpjQmRaRvPjLvhc_opSlOA5v9Rng8cC7XJ4/edit?usp=drivesdk",
          "cachedResultName": "Invoice Database"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "sPZTi75OujS3qDy2",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "177b24b3-42c3-4b2e-b9d3-4e9b83ad54b7",
      "name": "Analyze Image1",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        400,
        1024
      ],
      "parameters": {
        "url": "https://api.ocr.space/parse/image",
        "method": "POST",
        "options": {},
        "sendBody": true,
        "contentType": "multipart-form-data",
        "sendHeaders": true,
        "bodyParameters": {
          "parameters": [
            {
              "name": "file",
              "parameterType": "formBinaryData",
              "inputDataFieldName": "data"
            }
          ]
        },
        "headerParameters": {
          "parameters": [
            {
              "name": "apikey",
              "value": "your_key_here"
            }
          ]
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "077028da-6643-40a7-ada4-08d76f188adf",
      "name": "Setzen1",
      "type": "n8n-nodes-base.set",
      "position": [
        864,
        1424
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "a88c44a6-0d32-4678-947a-de5e0d217534",
              "name": "Invoice Information",
              "type": "string",
              "value": "={{ $('Analyze Image1').item.json.ParsedResults[0].ParsedText }}"
            },
            {
              "id": "845587cf-94fc-4d59-9f77-5ad77c4901c0",
              "name": "File",
              "type": "string",
              "value": "={{ $('Add Invoice Image to Drive1').item.json.name }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "fe69af99-9a00-4152-a3ce-11bbcededbe3",
      "name": "Add Invoice Image to Drive1",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        288,
        1424
      ],
      "parameters": {
        "name": "=Invoice [{{ $now.format('MMMM-dd-yyyy')}}]",
        "driveId": {
          "__rl": true,
          "mode": "list",
          "value": "My Drive"
        },
        "options": {},
        "folderId": {
          "__rl": true,
          "mode": "list",
          "value": "1VdaeaE6emIxFn62qzXTiYOyC2TvvWUKE",
          "cachedResultUrl": "https://drive.google.com/drive/folders/1VdaeaE6emIxFn62qzXTiYOyC2TvvWUKE",
          "cachedResultName": "Invoices"
        }
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "jtE8vU0TUyfje3N9",
          "name": "Google Drive account"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "a229a3fb-0a65-4333-abdf-ecffabbd4311",
      "name": "Telegram1",
      "type": "n8n-nodes-base.telegram",
      "position": [
        -32,
        1424
      ],
      "webhookId": "7afd636c-26d4-4a93-ba24-ccc150bb66ff",
      "parameters": {
        "fileId": "={{ $('Download File1').item.json.result.file_id }}",
        "resource": "file",
        "additionalFields": {}
      },
      "credentials": {
        "telegramApi": {
          "id": "zoS34yaqfznbZ6AL",
          "name": "Telegram account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "f25af9d9-2720-4ba4-8409-3467375bc416",
      "name": "Reply1",
      "type": "n8n-nodes-base.telegram",
      "position": [
        1520,
        1248
      ],
      "webhookId": "aae2cfdf-646c-4bb4-a266-64768113acdf",
      "parameters": {
        "text": "={{ $json.output }}",
        "chatId": "={{ $('Telegram Trigger1').item.json.message.from.id }}",
        "additionalFields": {
          "appendAttribution": false
        }
      },
      "credentials": {
        "telegramApi": {
          "id": "zoS34yaqfznbZ6AL",
          "name": "Telegram account"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "69fd3fb1-f976-4107-b54c-9c253167bbc7",
      "name": "Invoice Agent1",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        1136,
        1248
      ],
      "parameters": {
        "text": "=[Invoice Information]\n{{ $json['Invoice Information'] }}\n\n[File Name]\n{{ $json.File }}\n\n[Link to the Invoice Database]\n{{ $('Add Invoice Image to Drive1').item.json.webContentLink }}",
        "options": {
          "systemMessage": "=# System Role\nYou are an efficient invoice assistant that extracts key information from invoices and generates concise user responses.\n\n# Task Specification\nThank the user for submitting an invoice. Extract the total amount, due date, and a summary of the notes from the invoice. Inform the user that the original invoice has been added to the Google Drive, include the file name, and provide the link to the invoice database.\n\n# Specifics and Context\nStart off by thanking the user for submitting an invoice. Respond clearly and concisely to help users quickly understand their invoice details and access the database if needed.\n\n# Example\n## Input\n[Invoice Information]  \nInvoice Number: INV-12345  \nDate: 2024-12-01  \nTotal Amount: 950.00  \nBilling Address: 123 Business Lane  \nDue Date: 2024-12-15  \nNotes:  \n- Please make payment within the due date to avoid late fees.  \n- For questions about this invoice, contact billing@business.com or call (123) 456-7890.  \n- Payment methods accepted: Bank transfer, credit card, or PayPal.  \n- Late payments are subject to a 5% penalty fee  \n\n[File Name]  \nInvoice [December-05-2024]  \n\n[Link to the Invoice Database]  \nhttps://docs.google.com/spreadsheets/d/15FOh41iwvy7zv-k4foZc8ubMkMLz_BiBPXJTZ-IziM/edit?usp=sharing  \n\n## Output\nThanks for submitting!\n\nThe total amount of the invoice is **$950.00**, and the due date is **2024-12-15**.  \n\n**Notes:**  \n- Please make payment on time to avoid late fees.  \n- Contact billing@business.com or call (123) 456-7890 for any questions.  \n- Accepted payment methods: Bank transfer, credit card, and PayPal.  \n\nThe original invoice has been added to Google Drive with the file name **\"Invoice [December-05-2024]\"**.  \n\nYou can access the invoice database [here](https://docs.google.com/spreadsheets/d/15FOh41iwvy7zv-k4foZc8ubMkMLz_BiBPXJTZ-IziM/edit?usp=sharing).\n"
        },
        "promptType": "define"
      },
      "typeVersion": 1.7
    },
    {
      "id": "965f8391-dfb5-4aee-a046-fe6a54dcb914",
      "name": "Google Gemini-Chat-Modell1",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        1072,
        1472
      ],
      "parameters": {
        "options": {}
      },
      "credentials": {
        "googlePalmApi": {
          "id": "5CctkrjhvRisPmai",
          "name": "Google Gemini(PaLM) Api account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "f8c30863-5ec3-44ea-ab7c-a40975c2371d",
      "name": "KI-Agent",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        592,
        1008
      ],
      "parameters": {
        "text": "={{ $json.ParsedResults[0].ParsedText }}\ntake the text from the image analyzer and put it on this form:\n    invoiceNumber: \n    invoiceDate: \n    totalAmount: \n    billingAddress: \n    dueDate: \n    notes: \n\nif there's no due date, just put the invoice date. and if there's no note, just put no notes",
        "options": {},
        "promptType": "define",
        "hasOutputParser": true
      },
      "typeVersion": 2.2
    },
    {
      "id": "9513502b-eab5-43a5-9ca4-a3d2495ceef3",
      "name": "Google Gemini-Chat-Modell2",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        592,
        864
      ],
      "parameters": {
        "options": {}
      },
      "credentials": {
        "googlePalmApi": {
          "id": "5CctkrjhvRisPmai",
          "name": "Google Gemini(PaLM) Api account"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "54b4fb81-272a-4d89-bf4e-ccb635e42874",
      "name": "Structured Output Parser",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        816,
        1216
      ],
      "parameters": {
        "jsonSchemaExample": "{\n\t\n   \"invoiceNumber\": \"\",\n   \"invoiceDate\": \"\",\n    \"totalAmount\": \"\",\n    \"billingAddress\": \"\",\n   \"dueDate\": \"\",\n    \"notes\": \"\"\n\n}"
      },
      "typeVersion": 1.3
    },
    {
      "id": "0dd95784-02de-4e47-82f6-6c056480cdf7",
      "name": "Haftnotiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        144,
        160
      ],
      "parameters": {
        "width": 944,
        "height": 592,
        "content": "# Before you start\n\n## Create/select credentials:\n\n- Telegram Bot: add your bot token as “Telegram account”.\n- Google Sheets OAuth: must have edit access to your target spreadsheet.\n- Google Drive OAuth: must have write access to your target folder.\n- Gemini API (or any desired AI): set a valid API key in the Gemini Chat Model credentials.\n\n## External API:\n\n- OCR.space: get an API key and place it in the HTTP Request header “apikey”.\n\n## Replace resource IDs:\n\n- Google Sheet ID: set the “documentId” to your own Invoice Sheet.\n- Sheet tab: confirm the “sheetName/gid” matches your target tab.\n- Drive folder ID: set the folderId to your “Invoices” folder.\n- Sheet columns: ensure your sheet has headers exactly as: Invoice Number | Date | Total Amount ($) | Billing Address | Due Date | Notes ( or edit the columns the way you desire ) "
      },
      "typeVersion": 1
    },
    {
      "id": "e6584eb9-c6af-4c66-929f-78eaee434c12",
      "name": "Haftnotiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -768,
        1168
      ],
      "parameters": {
        "color": 5,
        "width": 496,
        "height": 256,
        "content": "## What to edit \n\n- HTTP Request (OCR): set header apikey to your OCR.space key.\n- Google Sheets node: paste your Sheet ID and correct gid/tab.\n- Google Drive node: paste your Drive folder ID.\n\n- Optional: adjust the output message style in “Invoice Agent” system prompt.\n- Optional: tweak file naming format in Drive node (currently Invoice [MMMM-dd-yyyy])."
      },
      "typeVersion": 1
    },
    {
      "id": "a6387f1d-8e79-46a2-a132-418fbede5688",
      "name": "Haftnotiz2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -160,
        1776
      ],
      "parameters": {
        "color": 4,
        "width": 816,
        "height": 480,
        "content": "## How to run\n\n- Make the workflow Active.\n- In Telegram, “Send as file” a PDF or image to your bot (not a photo compressed upload).\n- Watch Executions: you should see the flow download → OCR → AI extract → Sheets append → Drive upload → Telegram reply.\n\n## Result:\n- New row in your Google Sheet with extracted fields.\n- Original file saved in Drive folder.\n- Telegram reply summarizing total amount, due date, notes, file name, and a link to the sheet.\n\n## Inputs and outputs\n- Input: Telegram document (PDF/JPG/PNG) containing invoice text.\n- Output (Google Sheets row): invoiceNumber, invoiceDate, totalAmount, billingAddress, dueDate, notes.\n- Output (Drive): original file stored in your specified folder with date-based name.\n- Output (Telegram): friendly summary message with details and database link."
      },
      "typeVersion": 1
    },
    {
      "id": "32926f3f-3886-4676-b9b4-19f1431abfd3",
      "name": "Haftnotiz3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        928,
        1776
      ],
      "parameters": {
        "color": 3,
        "width": 848,
        "height": 448,
        "content": "## Behavior and logic: \n\n- Trigger: Telegram document message starts the flow.\n- OCR: File binary is sent to OCR.space; the text is returned as ParsedText.\n- AI extraction: Gemini parses the OCR text into structured fields; if no due date is found, uses invoiceDate; if no notes, sets “no notes”.\n- Database update: Fields are appended to the Google Sheet using the defined column mapping.\n- Storage: The original file is saved to Drive; its link is used in the reply context.\n- Reply: A concise message is composed and sent back to the sender with key details and the sheet link.\n\n## Troubleshooting\n- Empty or bad OCR: ensure you sent a document (not a compressed photo), try clearer scans, or set OCR parameters like language=eng, isTable=true.\n- Sheet not updating: verify credentials, Sheet ID, tab gid, and header names match exactly.\n- Drive not saving: check folder permissions and that Drive credentials can write to that folder.\n- Reply missing: confirm the previous step returned output and the bot can message the user (user must have started the bot).\n- Rate/size issues: OCR.space free tier has limits; add retries/backoff or queue heavy loads."
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {
    "Telegram Trigger1": [
      {
        "json": {
          "message": {
            "chat": {
              "id": 5656858022,
              "type": "private",
              "username": "NotLuckyyyyy",
              "last_name": "Luckyyy",
              "first_name": "Not"
            },
            "date": 1759841669,
            "from": {
              "id": 5656858022,
              "is_bot": false,
              "username": "NotLuckyyyyy",
              "last_name": "Luckyyy",
              "first_name": "Not",
              "language_code": "fr"
            },
            "document": {
              "file_id": "BQACAgQAAxkBAAOqaOUNhYf7Fo8HG_tl7wZGHU-8OHsAArMaAALjjyhTOzuY0KjIs-c2BA",
              "file_name": "Invoice - 0020 - Camillo Kessler.pdf",
              "file_size": 164066,
              "mime_type": "application/pdf",
              "file_unique_id": "AgADsxoAAuOPKFM"
            },
            "message_id": 170
          },
          "update_id": 767479639
        }
      }
    ]
  },
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "50933dd8-08c3-47b1-8f2a-2aa4acb292f9",
  "connections": {
    "Set1": {
      "main": [
        [
          {
            "node": "69fd3fb1-f976-4107-b54c-9c253167bbc7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "AI Agent": {
      "main": [
        [
          {
            "node": "f73b5682-1048-465b-85ba-c7ba3735ad58",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "a229a3fb-0a65-4333-abdf-ecffabbd4311": {
      "main": [
        [
          {
            "node": "fe69af99-9a00-4152-a3ce-11bbcededbe3",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "177b24b3-42c3-4b2e-b9d3-4e9b83ad54b7": {
      "main": [
        [
          {
            "node": "AI Agent",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Download File1": {
      "main": [
        [
          {
            "node": "177b24b3-42c3-4b2e-b9d3-4e9b83ad54b7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "69fd3fb1-f976-4107-b54c-9c253167bbc7": {
      "main": [
        [
          {
            "node": "f25af9d9-2720-4ba4-8409-3467375bc416",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "f73b5682-1048-465b-85ba-c7ba3735ad58": {
      "main": [
        [
          {
            "node": "a229a3fb-0a65-4333-abdf-ecffabbd4311",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Telegram Trigger1": {
      "main": [
        [
          {
            "node": "Download File1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "54b4fb81-272a-4d89-bf4e-ccb635e42874": {
      "ai_outputParser": [
        [
          {
            "node": "AI Agent",
            "type": "ai_outputParser",
            "index": 0
          }
        ]
      ]
    },
    "Google Gemini Chat Model1": {
      "ai_languageModel": [
        [
          {
            "node": "69fd3fb1-f976-4107-b54c-9c253167bbc7",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Google Gemini Chat Model2": {
      "ai_languageModel": [
        [
          {
            "node": "AI Agent",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "fe69af99-9a00-4152-a3ce-11bbcededbe3": {
      "main": [
        [
          {
            "node": "Set1",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}