{"id":"QOePbDNCilLhfzbs","meta":{"instanceId":"workflow-4a4928e6","versionId":"1.0.0","createdAt":"2025-09-29T07:08:01.342947","updatedAt":"2025-09-29T07:08:01.342958","owner":"n8n-user","license":"MIT","category":"automation","status":"active","priority":"high","environment":"production"},"name":"LINE BOT - Google Sheets Record Receipt","nodes":[{"id":"c9a6882e-8971-4f8b-8dc4-730e217200f9","name":"Sticky Note","type":"n8n-nodes-base.stickyNote","position":[-1260,100],"parameters":{"width":400,"height":500,"content":"## Prepare data\n**- Get content image from Line** \n{{ $env.API_BASE_URL }}\n\n**- Get image URL to Binary**"},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"b766ad37-ec63-4006-80a7-048307afd23a","name":"Image slip 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":"={{ $env.API_BASE_URL }}{{ $json.body.events[0].message.id }}/content"}]}},"typeVersion":3.4,"notes":"This set node performs automated tasks as part of the workflow."},{"id":"172ed09e-8caf-4bee-9f09-a9b8b00470f7","name":"Get image to Binary","type":"n8n-nodes-base.httpRequest","position":[-1000,300],"parameters":{"url":"{{ $env.BASE_URL }}","options":{},"authentication":"{{ $credentials.genericCredentialType }}","genericAuthType":"httpHeaderAuth"},"credentials":{"httpHeaderAuth":{"id":"byY3kI23lMe4ewnM","name":"Header Auth account - Maid"}},"typeVersion":4.2,"notes":"This httpRequest node performs automated tasks as part of the workflow."},{"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,"notes":"This webhook node performs automated tasks as part of the workflow."},{"id":"91837828-c24d-4999-a6db-9323394b8e77","name":"Sticky Note1","type":"n8n-nodes-base.stickyNote","position":[-840,100],"parameters":{"color":2,"width":220,"height":500,"content":"## Upload image to Google Drive\n"},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"94be83d7-5070-4f94-ae33-0a9695fc0b25","name":"Sticky Note2","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**\n{{ $env.API_BASE_URL }}\n\n**- Parse Transaction Details**"},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"5e269f18-c666-4ba3-bb92-e60f5761cf0e","name":"Sticky Note3","type":"n8n-nodes-base.stickyNote","position":[-40,100],"parameters":{"color":5,"width":220,"height":500,"content":"## Store Data in Google Sheets"},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"aa5312d8-304c-4d64-839b-a4464cb0d60e","name":"Sticky Note4","type":"n8n-nodes-base.stickyNote","position":[-1500,100],"parameters":{"color":5,"width":220,"height":500,"content":"## LINE Webhook Trigger \n**(Receive Image)**"},"typeVersion":1,"notes":"This stickyNote node performs automated tasks as part of the workflow."},{"id":"802a7b11-38bf-4dd1-ae32-cd6b6071b9dd","name":"Upload image to Google Drive","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":"{{ $env.WEBHOOK_URL }}"}},"credentials":{"googleDriveOAuth2Api":{"id":"QVrgALkld7whKIgB","name":"Google Drive account - Peakwave"}},"typeVersion":3,"notes":"This googleDrive node performs automated tasks as part of the workflow."},{"id":"b37b4b7a-1030-44d0-8f57-90acca085e5a","name":"Record in Google Sheets","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":"{{ $env.WEBHOOK_URL }}","cachedResultName":"data"},"documentId":{"__rl":true,"mode":"url","value":"{{ $env.WEBHOOK_URL }}"}},"credentials":{"googleSheetsOAuth2Api":{"id":"0RVWjnYzlWor2bMu","name":"Google Sheets account"}},"typeVersion":4.5,"notes":"This googleSheets node performs automated tasks as part of the workflow."},{"id":"22fbba4f-ad1f-43a5-99de-db7084cd3fc5","name":"Send Image URL to OCR Space for Text Extraction","type":"n8n-nodes-base.httpRequest","position":[-520,300],"parameters":{"url":"{{ $env.BASE_URL }}","options":{}},"typeVersion":4.2,"notes":"This httpRequest node performs automated tasks as part of the workflow."},{"id":"678993d0-8301-42d5-93cd-7839d42b71bc","name":"Extract Transaction Details","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,"notes":"This code node performs automated tasks as part of the workflow."}],"active":false,"settings":{"executionOrder":"v1","saveManualExecutions":true,"callerPolicy":"workflowsFromSameOwner","errorWorkflow":null,"timezone":"UTC","executionTimeout":3600,"maxExecutions":1000,"retryOnFail":true,"retryCount":3,"retryDelay":1000},"versionId":"e1708774-49cf-4cbb-a4c4-9fefccd0fedb","connections":{"Get image to Binary":{"main":[[],[],[],[],[],[],[],[],[]]},"Line Chat Bot":{"main":[[],[],[],[],[],[],[],[],[]]},"Send Image URL to OCR Space for Text Extraction":{"main":[[],[],[],[],[],[],[],[],[]]},"Upload image to Google Drive":{"main":[[]]},"Record in Google Sheets":{"main":[[]]}},"description":"Automated workflow: LINE BOT - Google Sheets Record Receipt. This workflow integrates 8 different services: webhook, stickyNote, httpRequest, code, googleDrive. It contains 20 nodes and follows best practices for error handling and security.","notes":"Excellent quality workflow: LINE BOT - Google Sheets Record Receipt. This workflow has been optimized for production use with comprehensive error handling, security, and documentation."}