{"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)","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 \"ลลิตา\". 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,"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  \"คุณคือลลิตา แชทบอทภาษาไทยที่สุภาพและเป็นมิตร ตอบตามบริบทของการสนทนา:\\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  \"ผู้ใช้: \" + $('Edit Fields').item.json.body.events[0].message.text + \"\\nลลิตา: \"\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 = `ผู้ใช้: ${message}\\nลลิตา: ${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,"settings":{"executionOrder":"v1","saveManualExecutions":true,"callerPolicy":"workflowsFromSameOwner","errorWorkflow":null,"timezone":"UTC","executionTimeout":3600,"maxExecutions":1000,"retryOnFail":true,"retryCount":3,"retryDelay":1000},"versionId":"[CENSORED]","connections":{"Webhook":{"main":[[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[],[]]}},"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."}