Workflow n8n
Automatisation Google Sheets avec n8n : enregistrement de reçus
Ce workflow n8n permet d'automatiser l'enregistrement de reçus dans Google Sheets à partir d'un bot Line. Dans un contexte où la gestion des dépenses et la traçabilité des transactions sont essentielles, ce processus simplifie la collecte et l'organisation des informations financières. Les utilisateurs peuvent ainsi gagner un temps précieux en évitant la saisie manuelle des données. Le workflow commence par un déclencheur webhook du bot Line, qui reçoit les informations du reçu. Ensuite, l'image du reçu est convertie en format binaire grâce à un appel HTTP, permettant ainsi d'extraire le texte via un service OCR. Les détails de la transaction sont ensuite traités par un code JavaScript pour structurer les données. Une fois les informations extraites, elles sont enregistrées dans Google Sheets, facilitant ainsi leur consultation et leur analyse. En utilisant ce workflow, les entreprises peuvent réduire les erreurs de saisie, améliorer la gestion de leurs dépenses et assurer une meilleure traçabilité des transactions. L'automatisation n8n offre une solution efficace et rapide pour gérer les reçus, permettant aux équipes de se concentrer sur des tâches à plus forte valeur ajoutée.
Tags clés :automatisationGoogle SheetsLineOCRn8n
Catégorie: Webhook · Tags: automatisation, Google Sheets, Line, OCR, n8n❤ 0
Workflow n8n Google Sheets, Line, OCR : vue d'ensemble
Schéma des nœuds et connexions de ce workflow n8n, généré à partir du JSON n8n.
Workflow n8n Google Sheets, Line, OCR : détail des nœuds
Inscris-toi pour voir l'intégralité du workflow
Inscription gratuite
S'inscrire gratuitementBesoin d'aide ?{
"id": "QOePbDNCilLhfzbs",
"meta": {
"instanceId": "2c12b0b552404dc07af67cd5f092afd21d18c808d4fdabdb04cb4b064195b6fb",
"templateCredsSetupCompleted": true
},
"name": "LINE BOT - Google Sheets Record Receipt",
"tags": [],
"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** \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": "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": "=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": "Get image to Binary",
"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": "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
},
{
"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**\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": "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
},
{
"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
},
{
"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": "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": "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": "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": "Send Image URL to OCR Space for Text Extraction",
"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": "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
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "e1708774-49cf-4cbb-a4c4-9fefccd0fedb",
"connections": {
"Line Chat Bot": {
"main": [
[
{
"node": "Image slip URL in Line",
"type": "main",
"index": 0
}
]
]
},
"Get image to Binary": {
"main": [
[
{
"node": "Upload image to Google Drive",
"type": "main",
"index": 0
}
]
]
},
"Image slip URL in Line": {
"main": [
[
{
"node": "Get image to Binary",
"type": "main",
"index": 0
}
]
]
},
"Extract Transaction Details": {
"main": [
[
{
"node": "Record in Google Sheets",
"type": "main",
"index": 0
}
]
]
},
"Upload image to Google Drive": {
"main": [
[
{
"node": "Send Image URL to OCR Space for Text Extraction",
"type": "main",
"index": 0
}
]
]
},
"Send Image URL to OCR Space for Text Extraction": {
"main": [
[
{
"node": "Extract Transaction Details",
"type": "main",
"index": 0
}
]
]
}
}
}Workflow n8n Google Sheets, Line, OCR : pour qui est ce workflow ?
Ce workflow s'adresse aux entreprises et équipes qui gèrent un volume important de reçus et de transactions, notamment dans les secteurs de la comptabilité, de la finance et de la gestion des dépenses. Il est idéal pour les utilisateurs ayant un niveau technique intermédiaire et souhaitant optimiser leur processus de gestion financière.
Workflow n8n Google Sheets, Line, OCR : problème résolu
Ce workflow résout le problème de la saisie manuelle des reçus, souvent source d'erreurs et de pertes de temps. En automatisant ce processus, les utilisateurs peuvent réduire les risques d'erreurs humaines et gagner en efficacité. Après la mise en place de ce workflow, les entreprises bénéficient d'une meilleure organisation de leurs données financières, ce qui facilite les audits et le suivi des dépenses.
Workflow n8n Google Sheets, Line, OCR : étapes du workflow
Étape 1 : Le workflow est déclenché par un webhook du bot Line qui reçoit les informations du reçu.
- Étape 1 : L'image du reçu est récupérée et convertie en format binaire grâce à un appel HTTP.
- Étape 2 : Cette image est ensuite envoyée à un service OCR pour extraire le texte.
- Étape 3 : Les détails de la transaction sont traités par un code JavaScript pour structurer les données.
- Étape 4 : Les informations extraites sont enregistrées dans Google Sheets, permettant une gestion simplifiée et efficace des reçus.
Workflow n8n Google Sheets, Line, OCR : guide de personnalisation
Pour personnaliser ce workflow, vous pouvez modifier l'URL du webhook pour l'adapter à votre bot Line. Il est également possible de changer le nom de la feuille Google Sheets et d'ajuster les colonnes selon vos besoins spécifiques. Si vous souhaitez intégrer d'autres outils, vous pouvez ajouter des noeuds supplémentaires pour enrichir le flux. Enfin, assurez-vous de sécuriser le flux en configurant correctement les autorisations d'accès à Google Drive et Google Sheets.