LINE Bot - Google Sheets Quittungen erfassen

• •HTTP Webhook-Endpunkt (wird von n8n automatisch generiert)
• •Google Drive API-Anmeldedaten
• •Möglicherweise sind Ziel-API-Anmeldedaten erforderlich
• •Google Sheets API-Anmeldedaten

{
  "id": "QOePbDNCilLhfzbs",
  "meta": {
    "instanceId": "2c12b0b552404dc07af67cd5f092afd21d18c808d4fdabdb04cb4b064195b6fb",
    "templateCredsSetupCompleted": true
  },
  "name": "LINE BOT - Google Sheets Record Receipt",
  "tags": [],
  "nodes": [
    {
      "id": "c9a6882e-8971-4f8b-8dc4-730e217200f9",
      "name": "Notiz",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1260,
        100
      ],
      "parameters": {
        "width": 400,
        "height": 500,
        "content": "## Prepare data\n**- Get content image from Line** \nhttps://api-data.line.me/v2/bot/message/xxx/content\n\n**- Get image URL to Binary**"
      },
      "typeVersion": 1
    },
    {
      "id": "b766ad37-ec63-4006-80a7-048307afd23a",
      "name": "Belegbild-URL in Line",
      "type": "n8n-nodes-base.set",
      "position": [
        -1200,
        300
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "f8b8ac7c-5c5f-452f-a84d-e068bb248eb5",
              "name": "file_url",
              "type": "string",
              "value": "=https://api-data.line.me/v2/bot/message/{{ $json.body.events[0].message.id }}/content"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "172ed09e-8caf-4bee-9f09-a9b8b00470f7",
      "name": "Bild in Binär umwandeln",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -1000,
        300
      ],
      "parameters": {
        "url": "={{ $json.file_url }}",
        "options": {},
        "authentication": "genericCredentialType",
        "genericAuthType": "httpHeaderAuth"
      },
      "credentials": {
        "httpHeaderAuth": {
          "id": "byY3kI23lMe4ewnM",
          "name": "Header Auth account - Maid"
        }
      },
      "typeVersion": 4.2
    },
    {
      "id": "79753b3d-d6a9-4047-af48-947e6221de48",
      "name": "Line Chat Bot",
      "type": "n8n-nodes-base.webhook",
      "position": [
        -1440,
        300
      ],
      "webhookId": "23ba996d-3242-42a1-946c-f04a680b320a",
      "parameters": {
        "path": "23ba996d-3242-42a1-946c-f04a680b320a",
        "options": {},
        "httpMethod": "POST"
      },
      "typeVersion": 1
    },
    {
      "id": "91837828-c24d-4999-a6db-9323394b8e77",
      "name": "Notiz1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -840,
        100
      ],
      "parameters": {
        "color": 2,
        "width": 220,
        "height": 500,
        "content": "## Upload image to Google Drive\n"
      },
      "typeVersion": 1
    },
    {
      "id": "94be83d7-5070-4f94-ae33-0a9695fc0b25",
      "name": "Notiz2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -600,
        100
      ],
      "parameters": {
        "color": 3,
        "width": 540,
        "height": 500,
        "content": "## OCR and get value\n**- OCR API by SpaceOCR**\nhttps://api.ocr.space/parse/imageurl?apikey=YOURAPI&language=tha&isOverlayRequired=false&OCREngine=2&filetype=JPG&url=xxx\n\n**- Parse Transaction Details**"
      },
      "typeVersion": 1
    },
    {
      "id": "5e269f18-c666-4ba3-bb92-e60f5761cf0e",
      "name": "Notiz3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -40,
        100
      ],
      "parameters": {
        "color": 5,
        "width": 220,
        "height": 500,
        "content": "## Store Data in Google Sheets"
      },
      "typeVersion": 1
    },
    {
      "id": "aa5312d8-304c-4d64-839b-a4464cb0d60e",
      "name": "Notiz4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1500,
        100
      ],
      "parameters": {
        "color": 5,
        "width": 220,
        "height": 500,
        "content": "## LINE Webhook Trigger \n**(Receive Image)**"
      },
      "typeVersion": 1
    },
    {
      "id": "802a7b11-38bf-4dd1-ae32-cd6b6071b9dd",
      "name": "Bild in Google Drive hochladen",
      "type": "n8n-nodes-base.googleDrive",
      "position": [
        -780,
        300
      ],
      "parameters": {
        "name": "={{ $('Line Chat Bot').item.json.body.events[0].message.id }}.jpg",
        "driveId": {
          "__rl": true,
          "mode": "list",
          "value": "My Drive"
        },
        "options": {},
        "folderId": {
          "__rl": true,
          "mode": "url",
          "value": "https://drive.google.com/drive/folders/1M-j_Gt6yKM1K8SISWknaGQyPQn52AaK1"
        }
      },
      "credentials": {
        "googleDriveOAuth2Api": {
          "id": "QVrgALkld7whKIgB",
          "name": "Google Drive account - Peakwave"
        }
      },
      "typeVersion": 3
    },
    {
      "id": "b37b4b7a-1030-44d0-8f57-90acca085e5a",
      "name": "In Google Sheets erfassen",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        20,
        300
      ],
      "parameters": {
        "columns": {
          "value": {
            "Fee": "={{ $json.fee }}",
            "Amount": "={{ $json.amount }}",
            "Date & Time": "={{ $json.date_time }}",
            "Sender Name": "={{ $json.sender_name }}",
            "Receiver Bank": "={{ $json.receiver_bank }}",
            "Receiver Name": "={{ $json.receiver_name }}",
            "Sender Account": "={{ $json.sender_account }}",
            "Transaction ID": "={{ $json.transaction_id }}",
            "Receiver Account": "={{ $json.receiver_account }}",
            "Transaction Type": "={{ $json.transaction_type }}"
          },
          "schema": [
            {
              "id": "Transaction Type",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Transaction Type",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Date & Time",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Date & Time",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Bank",
              "type": "string",
              "display": true,
              "removed": true,
              "required": false,
              "displayName": "Bank",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Sender Name",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Sender Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Sender Account",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Sender Account",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Receiver Name",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Receiver Name",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Receiver Bank",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Receiver Bank",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Receiver Account",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Receiver Account",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Transaction ID",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Transaction ID",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Amount",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Amount",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Fee",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Fee",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": "gid=0",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1IpvzcnWmb-aLpSleTIF0xoF8xzbOOJQhuT6ITAeEQks/edit#gid=0",
          "cachedResultName": "data"
        },
        "documentId": {
          "__rl": true,
          "mode": "url",
          "value": "https://docs.google.com/spreadsheets/d/1IpvzcnWmb-aLpSleTIF0xoF8xzbOOJQhuT6ITAeEQks/edit?gid=0#gid=0"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "0RVWjnYzlWor2bMu",
          "name": "Google Sheets account"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "22fbba4f-ad1f-43a5-99de-db7084cd3fc5",
      "name": "Bild-URL an OCR Space zur Textextraktion senden",
      "type": "n8n-nodes-base.httpRequest",
      "position": [
        -520,
        300
      ],
      "parameters": {
        "url": "=https://api.ocr.space/parse/imageurl?apikey=K82173083188957&language=tha&isOverlayRequired=false&OCREngine=2&filetype=JPG&url={{ \"https://drive.google.com/uc?id=\" + $json[\"id\"] }}\n",
        "options": {}
      },
      "typeVersion": 4.2
    },
    {
      "id": "678993d0-8301-42d5-93cd-7839d42b71bc",
      "name": "Transaktionsdetails extrahieren",
      "type": "n8n-nodes-base.code",
      "position": [
        -260,
        300
      ],
      "parameters": {
        "jsCode": "const text = $json[\"ParsedResults\"][0][\"ParsedText\"];\n\n// Split text by line breaks and trim spaces\nconst lines = text.split(\"\\n\").map(line => line.trim());\n\n// Debugging: Log extracted lines for verification\nconsole.log(\"Extracted Lines:\", lines);\n\n// Helper function to find text after a keyword, with OCR variations\nfunction getValueAfterKeyword(keywords, offset = 1) {\n    let index = lines.findIndex(line => keywords.some(keyword => line.includes(keyword)));\n    return index !== -1 && lines[index + offset] ? lines[index + offset] : null;\n}\n\n// **Extracting Data for Both Standard & PromptPay Transactions**\nconst transaction_type = lines[0] || null;  // First line\nconst date_time = lines[1] || null;  // Second line\n\n// **Sender Details**\nconst sender_name_index = lines.findIndex(line => line.startsWith(\"นาย\"));\nconst sender_name = sender_name_index !== -1 ? lines[sender_name_index] : null;\nconst sender_bank = sender_name_index !== -1 ? lines[sender_name_index + 1] : null;\nconst sender_account = sender_name_index !== -1 ? lines[sender_name_index + 2] : null;\n\n// **Determine if it's a Standard Bank Transfer or PromptPay**\nconst isPromptPay = lines.some(line => line.includes(\"Prompt\") || line.includes(\"รหัสพร้อมเพย์\"));\nlet receiver_name = null;\nlet receiver_bank = null;\nlet receiver_account = null;\n\nif (isPromptPay) {\n    // **Handling PromptPay Transactions**\n    const receiver_index = lines.findIndex(line => line.includes(\"Prompt\"));\n    receiver_bank = \"PromptPay\"; // Fixed for PromptPay transactions\n    receiver_name = receiver_index !== -1 ? lines[receiver_index + 2] : null; // Receiver's actual name\n\n    // **Fix Receiver Account for PromptPay**\n    const receiver_account_index = lines.findIndex(line => line.includes(\"รหัสพร้อมเพย์\"));\n    receiver_account = receiver_account_index !== -1 ? lines[receiver_account_index + 1] : null; // The actual account number\n\n} else {\n    // **Handling Standard Bank Transfers**\n    const receiver_index = lines.findIndex(line => line.includes(\"นิติบุคคล\") || line.includes(\"บริษัท\") || line.includes(\"นาย\"));\n    receiver_name = receiver_index !== -1 ? lines[receiver_index] : null;\n    receiver_bank = receiver_index !== -1 ? lines[receiver_index + 2] : null;\n    receiver_account = receiver_index !== -1 ? lines[receiver_index + 3] : null;\n}\n\n// **Fix Transaction ID Extraction**\nlet transaction_id = null;\n\n// **First, try \"เลขที่รายการ:\" for Standard Transactions**\nconst transaction_index = lines.findIndex(line => line.includes(\"เลขที่รายการ:\"));\nif (transaction_index !== -1) {\n    if (/\\d{10,}/.test(lines[transaction_index])) {\n        // If the same line contains the transaction ID, extract it\n        transaction_id = lines[transaction_index].match(/\\d{10,}/)[0];\n    } else if (transaction_index + 1 < lines.length && /\\d{10,}/.test(lines[transaction_index + 1])) {\n        // If transaction ID is on the next line, extract it\n        transaction_id = lines[transaction_index + 1];\n    }\n}\n\n// ✅ **If transaction_id is still missing, use \"จำนวน:\" or possible OCR errors (\"จำนวนะ\")**\nif (!transaction_id) {\n    let amount_index = lines.findIndex(line => line.includes(\"จำนวน\") || line.includes(\"จำนวนะ\"));\n    if (amount_index !== -1) {\n        for (let i = amount_index + 1; i < lines.length; i++) {\n            if (/^[A-Za-z0-9]+$/.test(lines[i])) { // Ensure it's a valid ID\n                transaction_id = lines[i];\n                break; // **Break early for efficiency**\n            }\n        }\n    }\n}\n\n// **Extract Amount Correctly**\nconst amount_index = lines.findIndex(line => line.includes(\"บาท\") && !line.includes(\"ค่าธรรมเนียม\"));\nconst amount = amount_index !== -1 ? lines[amount_index].replace(\" บาท\", \"\").replace(/[^0-9.]/g, \"\") : null;\n\n// **Extract Fee Correctly**\nconst fee_index = lines.findIndex(line => line.includes(\"ค่าธรรมเนียม\"));\nconst fee = fee_index !== -1 && lines[fee_index + 1] ? lines[fee_index + 1].replace(\" บาท\", \"\").replace(/[^0-9.]/g, \"\") : null;\n\n// **Ensure Essential Details Exist**\nif (transaction_type && date_time && sender_name && sender_bank && sender_account && receiver_name && receiver_bank && receiver_account && transaction_id && amount) {\n    return [\n        {\n            json: {\n                \"transaction_type\": transaction_type,\n                \"date_time\": date_time,\n                \"sender_name\": sender_name,\n                \"sender_bank\": sender_bank,\n                \"sender_account\": sender_account,\n                \"receiver_name\": receiver_name,\n                \"receiver_bank\": receiver_bank,\n                \"receiver_account\": receiver_account,\n                \"transaction_id\": transaction_id,\n                \"amount\": amount,\n                \"fee\": fee\n            }\n        }\n    ];\n} else {\n    return [\n        {\n            json: {\n                \"error\": \"Some values could not be extracted\",\n                \"raw_text\": text\n            }\n        }\n    ];\n}\n"
      },
      "typeVersion": 2
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "e1708774-49cf-4cbb-a4c4-9fefccd0fedb",
  "connections": {
    "79753b3d-d6a9-4047-af48-947e6221de48": {
      "main": [
        [
          {
            "node": "b766ad37-ec63-4006-80a7-048307afd23a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "172ed09e-8caf-4bee-9f09-a9b8b00470f7": {
      "main": [
        [
          {
            "node": "802a7b11-38bf-4dd1-ae32-cd6b6071b9dd",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "b766ad37-ec63-4006-80a7-048307afd23a": {
      "main": [
        [
          {
            "node": "172ed09e-8caf-4bee-9f09-a9b8b00470f7",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "678993d0-8301-42d5-93cd-7839d42b71bc": {
      "main": [
        [
          {
            "node": "b37b4b7a-1030-44d0-8f57-90acca085e5a",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "802a7b11-38bf-4dd1-ae32-cd6b6071b9dd": {
      "main": [
        [
          {
            "node": "22fbba4f-ad1f-43a5-99de-db7084cd3fc5",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "22fbba4f-ad1f-43a5-99de-db7084cd3fc5": {
      "main": [
        [
          {
            "node": "678993d0-8301-42d5-93cd-7839d42b71bc",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}