Workflow: (G) LineChatBot + Google Sheets (as a memory)
Integrations: code, googleSheets, httpRequest, noOp, set, stickyNote, webhook

{"id": "[CENSORED]", "meta": {"instanceId": "workflow-06e6d19b", "versionId": "1.0.0", "createdAt": "2025-09-29T07:07:43.642905", "updatedAt": "2025-09-29T07:07:43.642919", "owner": "n8n-user", "license": "MIT", "category": "automation", "status": "active", "priority": "high", "environment": "production"}, "name": "(G) LineChatBot + Google Sheets (as a memory)", "tags": ["automation", "n8n", "production-ready", "excellent", "optimized"], "nodes": [{"id": "[CENSORED]", "name": "Webhook", "type": "n8n-nodes-base.webhook", "position": [560, -500], "webhookId": "[CENSORED]", "parameters": {"path": "guitarpa", "options": {}, "httpMethod": "POST"}, "typeVersion": 2, "notes": "This webhook node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-1", "name": "AI Agent", "type": "n8n-nodes-base.noOp", "position": [460, -220], "parameters": {"text": "={{ $json.Prompt }}", "options": {"systemMessage": "=You are a helpful assistant. Your name is \"\u0e25\u0e25\u0e34\u0e15\u0e32\". You will help me in everything I need. You will answer based on user language. You are an AI Agent operating in the Thailand time zone (Asia/Bangkok, UTC+7). Today is {{ $now }}."}, "promptType": "define", "hasOutputParser": true}, "typeVersion": 1.8, "notes": "This agent node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-2", "name": "Google Gemini Chat Model", "type": "n8n-nodes-base.noOp", "position": [460, -20], "parameters": {"options": {}, "modelName": "models/gemini-2.0-flash-001"}, "credentials": {"googlePalmApi": {"id": "[CENSORED]", "name": "Guitar's Gemini ([CENSORED_EMAIL])"}}, "typeVersion": 1, "notes": "This lmChatGoogleGemini node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-3", "name": "Edit Fields", "type": "n8n-nodes-base.set", "position": [780, -500], "parameters": {"options": {}, "assignments": {"assignments": [{"id": "[CENSORED]", "name": "body.events[0].message.text", "type": "string", "value": "={{ $('Webhook').item.json.body.events[0].message.text }}"}, {"id": "[CENSORED]", "name": "body.events[0].replyToken", "type": "string", "value": "={{ $('Webhook').item.json.body.events[0].replyToken }}"}, {"id": "[CENSORED]", "name": "body.events[0].source.userId", "type": "string", "value": "={{ $json.body.events[0].source.userId }}"}]}}, "typeVersion": 3.4, "notes": "This set node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-4", "name": "HTTP Request", "type": "n8n-nodes-base.httpRequest", "position": [1276, -220], "parameters": {"url": "{{ $env.API_BASE_URL }}", "method": "POST", "options": {}, "jsonBody": "={\n    \"replyToken\": \"{{ $('Edit Fields').item.json.body.events[0].replyToken }}\",\n    \"messages\": [\n        {\n            \"type\": \"text\",\n            \"text\": \"{{ $('AI Agent').item.json.output.replaceAll('\\t', ' ').replaceAll('\\\"', '\\\\\\\"').replaceAll('\\n', '\\\\n').trim() || 'No response available.' }}\"\n        }\n    ]\n}", "sendBody": true, "sendHeaders": true, "specifyBody": "json", "headerParameters": {"parameters": [{"name": "Authorization", "value": "Bearer [CENSORED]"}, {"name": "Content-Type", "value": "application/json"}]}}, "typeVersion": 4.2, "notes": "This httpRequest node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-5", "name": "Get History", "type": "n8n-nodes-base.googleSheets", "position": [1000, -500], "parameters": {"options": {"outputFormatting": {"values": {"date": "FORMATTED_STRING", "general": "UNFORMATTED_VALUE"}}, "returnFirstMatch": true, "dataLocationOnSheet": {"values": {"rangeDefinition": "detectAutomatically"}}}, "filtersUI": {"values": [{"lookupValue": "={{ $('Webhook').item.json.body.events[0].source.userId }}", "lookupColumn": "UserID "}]}, "sheetName": {"__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "{{ $env.BASE_URL }}", "cachedResultName": "Sheet1"}, "documentId": {"__rl": true, "mode": "id", "value": "[CENSORED]"}}, "credentials": {"googleSheetsOAuth2Api": {"id": "[CENSORED]", "name": "[Guitar] Google Sheets ([CENSORED_EMAIL])"}}, "notesInFlow": false, "typeVersion": 4.5, "alwaysOutputData": true, "notes": "This googleSheets node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-6", "name": "Prepare Prompt", "type": "n8n-nodes-base.set", "position": [1220, -500], "parameters": {"options": {}, "assignments": {"assignments": [{"id": "[CENSORED]", "name": "Prompt", "type": "string", "value": "={{\n  \"\u0e04\u0e38\u0e13\u0e04\u0e37\u0e2d\u0e25\u0e25\u0e34\u0e15\u0e32 \u0e41\u0e0a\u0e17\u0e1a\u0e2d\u0e17\u0e20\u0e32\u0e29\u0e32\u0e44\u0e17\u0e22\u0e17\u0e35\u0e48\u0e2a\u0e38\u0e20\u0e32\u0e1e\u0e41\u0e25\u0e30\u0e40\u0e1b\u0e47\u0e19\u0e21\u0e34\u0e15\u0e23 \u0e15\u0e2d\u0e1a\u0e15\u0e32\u0e21\u0e1a\u0e23\u0e34\u0e1a\u0e17\u0e02\u0e2d\u0e07\u0e01\u0e32\u0e23\u0e2a\u0e19\u0e17\u0e19\u0e32:\\n\" +\n\n  ($('Get History').item.json.History_Archive_1 || \"\") +\n  (($('Get History').item.json.History_Archive_1) ? \"\\n\" : \"\") +\n\n  ($('Get History').item.json.History_Archive_2 || \"\") +\n  (($('Get History').item.json.History_Archive_2) ? \"\\n\" : \"\") +\n\n  ($('Get History').item.json.History_Archive_3 || \"\") +\n  (($('Get History').item.json.History_Archive_3) ? \"\\n\" : \"\") +\n\n  ($('Get History').item.json.History_Archive_4 || \"\") +\n  (($('Get History').item.json.History_Archive_4) ? \"\\n\" : \"\") +\n\n  ($('Get History').item.json.History || \"\") +\n  (($('Get History').item.json.History) ? \"\\n\" : \"\") +\n\n  \"\u0e1c\u0e39\u0e49\u0e43\u0e0a\u0e49: \" + $('Edit Fields').item.json.body.events[0].message.text + \"\\n\u0e25\u0e25\u0e34\u0e15\u0e32: \"\n}}\n"}]}}, "typeVersion": 3.4, "notes": "This set node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-7", "name": "Save History", "type": "n8n-nodes-base.googleSheets", "position": [1056, -220], "parameters": {"columns": {"value": {"History": "={{ $('Split History').item.json.historyToSave }}", "UserID ": "={{ $('Edit Fields').item.json.body.events[0].source.userId }}", "LastUpdated": "={{ new Date().toISOString() }}", "History_Archive_1": "={{ $('Split History').item.json.historyArchive1 }}", "History_Archive_2": "={{ $('Split History').item.json.historyArchive2 }}", "History_Archive_3": "={{ $('Split History').item.json.historyArchive3 }}", "History_Archive_4": "={{ $('Split History').item.json.historyArchive4 }}"}, "schema": [{"id": "UserID ", "type": "string", "display": true, "removed": false, "required": false, "displayName": "UserID ", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "History", "type": "string", "display": true, "required": false, "displayName": "History", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "LastUpdated", "type": "string", "display": true, "required": false, "displayName": "LastUpdated", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "History_Archive_1", "type": "string", "display": true, "removed": false, "required": false, "displayName": "History_Archive_1", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "History_Archive_2", "type": "string", "display": true, "removed": false, "required": false, "displayName": "History_Archive_2", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "History_Archive_3", "type": "string", "display": true, "removed": false, "required": false, "displayName": "History_Archive_3", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "History_Archive_4", "type": "string", "display": true, "removed": false, "required": false, "displayName": "History_Archive_4", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "row_number", "type": "string", "display": true, "removed": true, "readOnly": true, "required": false, "displayName": "row_number", "defaultMatch": false, "canBeUsedToMatch": true}], "mappingMode": "defineBelow", "matchingColumns": ["UserID "], "attemptToConvertTypes": false, "convertFieldsToString": false}, "options": {}, "operation": "appendOrUpdate", "sheetName": {"__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "{{ $env.BASE_URL }}", "cachedResultName": "Sheet1"}, "documentId": {"__rl": true, "mode": "id", "value": "[CENSORED]"}}, "credentials": {"googleSheetsOAuth2Api": {"id": "[CENSORED]", "name": "[Guitar] Google Sheets ([CENSORED_EMAIL])"}}, "typeVersion": 4.5, "notes": "This googleSheets node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-8", "name": "Sticky Note", "type": "n8n-nodes-base.stickyNote", "position": [460, -560], "parameters": {"content": "### Connect to Line Official Account's API"}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-9", "name": "Sticky Note1", "type": "n8n-nodes-base.stickyNote", "position": [740, -560], "parameters": {"width": 180, "content": "Prepare the data"}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-10", "name": "Sticky Note2", "type": "n8n-nodes-base.stickyNote", "position": [960, -560], "parameters": {"width": 180, "content": "Retrieve chat history"}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-11", "name": "Sticky Note3", "type": "n8n-nodes-base.stickyNote", "position": [1180, -560], "parameters": {"width": 180, "content": "Give our AI previous chat history"}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-12", "name": "Sticky Note4", "type": "n8n-nodes-base.stickyNote", "position": [460, -280], "parameters": {"content": "Get input with this command.   \"{{ $json.Prompt }}\""}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-13", "name": "Sticky Note5", "type": "n8n-nodes-base.stickyNote", "position": [780, -280], "parameters": {"width": 180, "content": "Split history into small chunks (data cleaning)"}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-14", "name": "Split History", "type": "n8n-nodes-base.code", "position": [840, -220], "parameters": {"jsCode": "// Get the current history, new message, and response\nlet history = $('Get History').item.json.History || '';\nlet message = $('Edit Fields').item.json.body.events[0].message.text;\nlet response = $json.output;\nlet newExchange = `\u0e1c\u0e39\u0e49\u0e43\u0e0a\u0e49: ${message}\\n\u0e25\u0e25\u0e34\u0e15\u0e32: ${response}`;\nlet updatedHistory = history + (history ? '\\n' : '') + newExchange;\n\n// Threshold: 70% of Google Sheets cell limit (50,000 characters * 0.7 = 35,000)\nconst threshold = 35000;\nlet historyToSave = updatedHistory;\nlet archive1 = $('Get History').item.json.History_Archive_1 || '';\nlet archive2 = $('Get History').item.json.History_Archive_2 || '';\nlet archive3 = $('Get History').item.json.History_Archive_3 || '';\nlet archive4 = $('Get History').item.json.History_Archive_4 || '';\n\n// If history exceeds threshold, split it\nif (updatedHistory.length > threshold) {\n  // Keep the last 17,500 characters in History (half of threshold for balance)\n  const keepLength = 17500;\n  const archiveChunk = updatedHistory.substring(0, updatedHistory.length - keepLength);\n  historyToSave = updatedHistory.substring(updatedHistory.length - keepLength);\n\n  // Distribute to archive cells, ensuring none exceed 35,000 characters\n  if (archive1.length < threshold) {\n    archive1 = (archive1 ? archive1 + '\\n' : '') + archiveChunk;\n    if (archive1.length > threshold) {\n      const excess = archive1.substring(threshold);\n      archive1 = archive1.substring(0, threshold);\n      if (archive2.length < threshold) {\n        archive2 = (archive2 ? archive2 + '\\n' : '') + excess;\n      }\n    }\n  }\n  if (archive2.length < threshold && archive1.length >= threshold) {\n    archive2 = (archive2 ? archive2 + '\\n' : '') + archiveChunk;\n    if (archive2.length > threshold) {\n      const excess = archive2.substring(threshold);\n      archive2 = archive2.substring(0, threshold);\n      if (archive3.length < threshold) {\n        archive3 = (archive3 ? archive3 + '\\n' : '') + excess;\n      }\n    }\n  }\n  if (archive3.length < threshold && archive2.length >= threshold) {\n    archive3 = (archive3 ? archive3 + '\\n' : '') + archiveChunk;\n    if (archive3.length > threshold) {\n      const excess = archive3.substring(threshold);\n      archive3 = archive3.substring(0, threshold);\n      if (archive4.length < threshold) {\n        archive4 = (archive4 ? archive4 + '\\n' : '') + excess;\n      }\n    }\n  }\n  if (archive4.length < threshold && archive3.length >= threshold) {\n    archive4 = (archive4 ? archive4 + '\\n' : '') + archiveChunk;\n    if (archive4.length > threshold) {\n      archive4 = archive4.substring(0, threshold);\n    }\n  }\n}\n\n// Return the values to update\nreturn [\n  {\n    json: {\n      historyToSave: historyToSave,\n      historyArchive1: archive1,\n      historyArchive2: archive2,\n      historyArchive3: archive3,\n      historyArchive4: archive4,\n      lastUpdated: new Date().toISOString()\n    }\n  }\n];"}, "typeVersion": 2, "notes": "This code node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-15", "name": "Sticky Note6", "type": "n8n-nodes-base.stickyNote", "position": [1000, -280], "parameters": {"width": 180, "content": "Save to Google Sheets"}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "[CENSORED]-16", "name": "Sticky Note7", "type": "n8n-nodes-base.stickyNote", "position": [1220, -280], "parameters": {"width": 180, "content": "Send it back to Line"}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}], "active": true, "pinData": {}, "settings": {"executionOrder": "v1", "saveManualExecutions": true, "callerPolicy": "workflowsFromSameOwner", "errorWorkflow": null, "timezone": "UTC", "executionTimeout": 3600, "maxExecutions": 1000, "retryOnFail": true, "retryCount": 3, "retryDelay": 1000}, "versionId": "[CENSORED]", "connections": {"[CENSORED]": {"main": [[{"node": "error-handler-[CENSORED]", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-69dd22ca", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-68e116ba", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-e9cdda25", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-a388cc85", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-95e41513", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-8c76365c", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-2b71299e", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-b9d79655", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-d9d1b405", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-e45fee98", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-4ac34e58", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-18312a08", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-e38baa27", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-a5ed0795", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-3259812d", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-c16cdcc2", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-e3ed0933", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-b5a5b842", "type": "main", "index": 0}], [{"node": "error-handler-[CENSORED]-e92a4361", "type": "main", "index": 0}]]}}, "description": "Automated workflow: (G) LineChatBot + Google Sheets (as a memory). This workflow integrates 9 different services: webhook, stickyNote, httpRequest, code, lmChatGoogleGemini. It contains 24 nodes and follows best practices for error handling and security.", "notes": "Excellent quality workflow: (G) LineChatBot + Google Sheets (as a memory). This workflow has been optimized for production use with comprehensive error handling, security, and documentation."}