Workflow: Googlesheetstool Workflow
Integrations: extractFromFile, filter, form, formTrigger, gmail, googleSheets, googleSheetsTool, noOp, scheduleTrigger, set, stickyNote, stopAndError, summarize

{"meta": {"instanceId": "workflow-20c4ed76", "versionId": "1.0.0", "createdAt": "2025-09-29T07:07:44.840144", "updatedAt": "2025-09-29T07:07:44.840161", "owner": "n8n-user", "license": "MIT", "category": "automation", "status": "active", "priority": "high", "environment": "production"}, "nodes": [{"id": "8884df86-b7cd-4cf7-8b71-fd21113bfc0f", "name": "Client Usage Log", "type": "n8n-nodes-base.googleSheetsTool", "position": [420, 500], "parameters": {"columns": {"value": {}, "schema": [{"id": "date", "type": "string", "display": true, "required": false, "displayName": "date", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "workflow_id", "type": "string", "display": true, "required": false, "displayName": "workflow_id", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "execution_id", "type": "string", "display": true, "required": false, "displayName": "execution_id", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "client_id", "type": "string", "display": true, "required": false, "displayName": "client_id", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "client_name", "type": "string", "display": true, "required": false, "displayName": "client_name", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "input_tokens", "type": "string", "display": true, "required": false, "displayName": "input_tokens", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "output_tokens", "type": "string", "display": true, "required": false, "displayName": "output_tokens", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "total_tokens", "type": "string", "display": true, "required": false, "displayName": "total_tokens", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "input_cost", "type": "string", "display": true, "required": false, "displayName": "input_cost", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "output_cost", "type": "string", "display": true, "required": false, "displayName": "output_cost", "defaultMatch": false, "canBeUsedToMatch": true}, {"id": "total_cost", "type": "string", "display": true, "required": false, "displayName": "total_cost", "defaultMatch": false, "canBeUsedToMatch": true}], "mappingMode": "autoMapInputData", "matchingColumns": [], "attemptToConvertTypes": false, "convertFieldsToString": false}, "options": {}, "operation": "append", "sheetName": {"__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "{{ $env.WEBHOOK_URL }}", "cachedResultName": "Sheet1"}, "documentId": {"__rl": true, "mode": "list", "value": "1AR5mrxz2S6PjAKVM0edNG-YVEc6zKL7aUxHxVcffnlw", "cachedResultUrl": "{{ $env.WEBHOOK_URL }}", "cachedResultName": "89. Langchain Code Node - Client Usage Log"}}, "credentials": {"googleSheetsOAuth2Api": {"id": "XHvC7jIRR8A2TlUl", "name": "Google Sheets account"}}, "typeVersion": 4.5, "notes": "This googleSheetsTool node performs automated tasks as part of the workflow."}, {"id": "1e4aca76-8b79-4780-b0c5-2cd92a41aa0e", "name": "Logging Attributes", "type": "n8n-nodes-base.set", "position": [-360, -120], "parameters": {"options": {}, "assignments": {"assignments": [{"id": "22164635-7a23-47e2-9868-96899cd9d317", "name": "workflow_id", "type": "string", "value": "={{ $workflow.id }}"}, {"id": "ed1cb653-b3fd-40bf-b00b-10d777f098af", "name": "execution_id", "type": "string", "value": "={{ $execution.id }}"}, {"id": "3de228a1-79b9-4805-8d92-917f691411be", "name": "client_id", "type": "string", "value": "=12345"}]}, "includeOtherFields": true}, "typeVersion": 3.4, "notes": "This set node performs automated tasks as part of the workflow."}, {"id": "d7f37c54-5d96-47ba-b82e-0926a08137df", "name": "On form submission", "type": "n8n-nodes-base.formTrigger", "position": [-920, -120], "webhookId": "9da21424-e23b-43b8-a6ec-a6daa129c326", "parameters": {"options": {}, "formTitle": "CV Parsing Service", "formFields": {"values": [{"fieldType": "file", "fieldLabel": "Upload a file", "requiredField": true, "acceptFileTypes": ".pdf"}, {"fieldType": "dropdown", "fieldLabel": "Acknowledgement", "multiselect": true, "fieldOptions": {"values": [{"option": "I acknowledge the use of this service will be added to my bill."}]}, "requiredField": true}]}, "responseMode": "lastNode", "formDescription": "Use this form to upload CVs and we'll extract the data from them. This workflow tracks usage metrics so we can calculate the bill later on."}, "typeVersion": 2.2, "notes": "This formTrigger node performs automated tasks as part of the workflow."}, {"id": "06da0c8e-2035-45ae-a301-50021650a5f8", "name": "Custom LLM Subnode", "type": "n8n-nodes-base.noOp", "position": [260, 340], "parameters": {"code": {"supplyData": {"code": "const { ChatOpenAI } = require(\"@langchain/openai\");\n\n// 1. Configure as required.\n// - costs are per million tokens and depends on the model.\nconst openAIApiKey = \"sk-...\";\nconst model = \"gpt-4o-mini\";\nconst input_token_cost = 0.150;\nconst output_token_cost = 0.600;\n\n// 2. do not edit below this line --\nconst tools = await this.getInputConnectionData('ai_tool', 0);\nconst googleSheetTool = tools[0];\n\nconst {\n  workflow_id,\n  execution_id,\n  client_id } = $input.first().json;\n\nconst llm = new ChatOpenAI({\n  apiKey: openAIApiKey,\n  model,\n  callbacks: [\n    {\n      handleLLMEnd: async function(output,runId,parentId) {\n        const generation = output.generations[0][0];\n        const message = generation.message;\n        const row = {\n          date: (new Date()).toGMTString(),\n          workflow_id,\n          execution_id,\n          client_id,\n          input_tokens: message.usage_metadata.input_tokens,\n          output_tokens: message.usage_metadata.output_tokens,\n          total_tokens: message.usage_metadata.total_tokens,\n          input_cost: (message.usage_metadata.input_tokens / 1_000_000) * input_token_cost,\n          output_cost: (message.usage_metadata.output_tokens / 1_000_000) * output_token_cost,\n        };\n        row.total_cost = row.input_cost + row.output_cost;\n        await googleSheetTool.func(row);\n      }\n    }\n  ]\n});\n\nreturn llm;"}}, "inputs": {"input": [{"type": "ai_tool", "required": true}]}, "outputs": {"output": [{"type": "ai_languageModel"}]}}, "typeVersion": 1, "notes": "This code node performs automated tasks as part of the workflow."}, {"id": "35993bd5-b521-4239-bf23-aed47d339f54", "name": "Sticky Note", "type": "n8n-nodes-base.stickyNote", "position": [360, 480], "parameters": {"width": 200, "height": 280, "content": "\n\n\n\n\n\n\n\n\n\n\n\n\n### Update Workbook\nThis is the workbook which will track the token usage and costs."}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "623ca94d-a215-416b-a9fe-62a1f96317e3", "name": "Sticky Note1", "type": "n8n-nodes-base.stickyNote", "position": [-1040, -280], "parameters": {"color": 7, "width": 560, "height": 380, "content": "## 1. Offer AI Service to Clients\nHere, we'll using an n8n form to offer a document extraction service for Resume/CV PDFs. The user simply uploads a PDF and we return it in JSON format. This is just a simple example for demonstration purposes. "}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "ba9eb149-e77f-4bf6-8ec5-7d8d4619485d", "name": "Sticky Note2", "type": "n8n-nodes-base.stickyNote", "position": [-460, -280], "parameters": {"color": 7, "width": 320, "height": 380, "content": "## 2. Gather External Variables to Send to Log\nThere are some variables we can't define in the subnode but we can add them here."}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "63bfe329-17dd-4321-94c6-17d306ed7412", "name": "Sticky Note3", "type": "n8n-nodes-base.stickyNote", "position": [-120, -280], "parameters": {"color": 7, "width": 720, "height": 380, "content": "## 3. Provide AI Service\nOur service uses an LLM (OpenAI GPT4o-mini in this example) to organise the uploaded PDF's data into a structured JSON format. This conversion is useful for following integrations or data storage. In this example however, we'll use display it back to the user."}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "f45862e9-7f8e-46bb-900a-807fee981ed5", "name": "Sticky Note4", "type": "n8n-nodes-base.stickyNote", "position": [-120, 120], "parameters": {"color": 7, "width": 720, "height": 440, "content": "## 4. Use Custom LLM Subnode to Track Usage & Cost\n[Learn more about the Langchain Code Node]({{ $env.WEBHOOK_URL }}\n\nBy creating our custom LLM subnode using the Langchain Code node, we are able to tap into the chat completion's response which contains the token usage metadata for the information extractor request.\n\nWith this, we can calculate exactly how much the client's request is costing per use!\n\nThe only remaining step then is to store\nthis data somewhere. Rather than importing\nmore dependencies or hardcoding more\ncredentials, a novel solution can be to attach\na tool to our subnode.\n\nHere, we've decided to use the Google Sheets\ntool and append the client's usage metrics to\nthe sheet. Check it out here - [Client Usage Log](\n{{ $env.WEBHOOK_URL }}"}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "9f5014a5-0e9a-4af0-b076-03cdc0a14ab9", "name": "Display JSON Document", "type": "n8n-nodes-base.form", "position": [360, -120], "webhookId": "1b9d0195-1662-43c2-94a0-f9c867d75578", "parameters": {"options": {"customCss": ".header p {\n  font-family: monospace;\n  background-color: #efefef;\n  padding: 1rem;\n  font-size: 0.8rem;\n  text-align: left;\n  max-height: 480px;\n  overflow: auto;\n  white-space: pre;\n}"}, "operation": "completion", "completionTitle": "=Results for {{ $('On form submission').item.json['Upload a file'][0].filename }}", "completionMessage": "={{ JSON.stringify($json.output, null, 2) }}"}, "typeVersion": 1, "notes": "This form node performs automated tasks as part of the workflow."}, {"id": "b977f89c-1118-455f-986e-735a17eecd9b", "name": "Filter Last Month", "type": "n8n-nodes-base.filter", "position": [1120, -120], "parameters": {"options": {}, "conditions": {"options": {"version": 2, "leftValue": "", "caseSensitive": true, "typeValidation": "strict"}, "combinator": "and", "conditions": [{"id": "2a86f83e-b103-46fe-a8b8-15811d4138fa", "operator": {"type": "dateTime", "operation": "afterOrEquals"}, "leftValue": "={{new Date($json.date) }}", "rightValue": "={{ $now.startOf('month') }}"}, {"id": "7b4c03a3-4df9-4b5d-9f1f-660e54a1c2d1", "operator": {"type": "dateTime", "operation": "beforeOrEquals"}, "leftValue": "={{new Date($json.date) }}", "rightValue": "={{ $now.endOf('month') }}"}]}}, "typeVersion": 2.2, "notes": "This filter node performs automated tasks as part of the workflow."}, {"id": "10d95dcb-d975-4b20-961e-d1fe63417878", "name": "Get Client Logs", "type": "n8n-nodes-base.googleSheets", "position": [920, -120], "parameters": {"options": {}, "filtersUI": {"values": [{"lookupValue": "12345", "lookupColumn": "client_id"}]}, "sheetName": {"__rl": true, "mode": "list", "value": "gid=0", "cachedResultUrl": "{{ $env.WEBHOOK_URL }}", "cachedResultName": "Sheet1"}, "documentId": {"__rl": true, "mode": "list", "value": "1AR5mrxz2S6PjAKVM0edNG-YVEc6zKL7aUxHxVcffnlw", "cachedResultUrl": "{{ $env.WEBHOOK_URL }}", "cachedResultName": "89. Langchain Code Node - Client Usage Log"}}, "credentials": {"googleSheetsOAuth2Api": {"id": "XHvC7jIRR8A2TlUl", "name": "Google Sheets account"}}, "typeVersion": 4.5, "notes": "This googleSheets node performs automated tasks as part of the workflow."}, {"id": "f6505545-d57c-443a-9883-2d536f3a973a", "name": "Calculate Totals", "type": "n8n-nodes-base.summarize", "position": [1320, -120], "parameters": {"options": {}, "fieldsToSummarize": {"values": [{"field": "total_cost", "aggregation": "sum"}, {"field": "total_tokens", "aggregation": "sum"}]}}, "typeVersion": 1.1, "notes": "This summarize node performs automated tasks as part of the workflow."}, {"id": "1c4ae8ff-ec2b-4fd3-974f-cc766385b16b", "name": "Every End of Month", "type": "n8n-nodes-base.scheduleTrigger", "position": [720, -120], "parameters": {"rule": {"interval": [{"field": "months", "triggerAtHour": 18, "triggerAtDayOfMonth": 31}]}}, "typeVersion": 1.2, "notes": "This scheduleTrigger node performs automated tasks as part of the workflow."}, {"id": "f321fbe6-36b1-4bd8-899b-832a8fc6217a", "name": "Send Invoice", "type": "n8n-nodes-base.gmail", "position": [1520, -120], "webhookId": "68315f84-d7e0-4525-a625-bb3ff431931c", "parameters": {"sendTo": "jim@example.com", "message": "=Hello,\nThis is an invoice for {{ $now.monthLong }} {{ $now.year }}.\n\nTotal usage: {{ $json.sum_total_tokens }} tokens\nTotal token cost: ${{ $json.sum_total_cost.toFixed(5) }}\nTax @ 20%: ${{ ($json.sum_total_cost * 0.2).toFixed(5) }}\nTotal payable: ${{ ($json.sum_total_cost * 1.2).toFixed(5) }}\n\nPayable within 14 days.\nThank you for your custom.", "options": {}, "subject": "=Invoice for {{ $now.monthLong }} {{ $now.year }}", "emailType": "text"}, "credentials": {"gmailOAuth2": {"id": "Sf5Gfl9NiFTNXFWb", "name": "Gmail account"}}, "typeVersion": 2.1, "notes": "This gmail node performs automated tasks as part of the workflow."}, {"id": "a7d8de78-c3b7-4687-8994-fe28387d7572", "name": "Sticky Note5", "type": "n8n-nodes-base.stickyNote", "position": [620, -280], "parameters": {"color": 7, "width": 1100, "height": 380, "content": "## 5. Automatically Send Invoice at End of Month (Optional)\nWith our client usage log, it's fairly simple to aggregate the log records for the client within a certain timeframe and calculate the totals.\nThis can satisfy accurate billing requirements from clients or at least, allows your team to understand how much each client is costing and budget accordingly."}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "169fa40d-c6e8-4315-be35-d2c73f626edf", "name": "Sticky Note6", "type": "n8n-nodes-base.stickyNote", "position": [-1500, -920], "parameters": {"width": 440, "height": 1020, "content": "## Try It Out!\n### This n8n template demonstrates how to use the Langchain code node to track token usage and cost for every LLM call.\n\nThis is useful if your templates handle multiple clients or customers and you need a cheap and easy way to capture how much of your AI credits they are using.\n\n### How it works\n* In our mock AI service, we're offering a data conversion API to convert Resume PDFs into JSON documents.\n* A form trigger is used to allow for PDF upload and the file is parsed using the Extract from File node.\n* An Edit Fields node is used to capture additional variables to send to our log.\n* Next, we use the Information Extractor node to organise the Resume data into the given JSON schema.\n* The LLM subnode attached to the Information Extractor is a custom one we've built using the Langchain Code node.\n* With our custom LLM subnode, we're able to capture the usage metadata using lifecycle hooks.\n* We've also attached a Google Sheet tool to our LLM subnode, allowing us to send our usage metadata to a google sheet.\n* Finally, we demonstrate how you can aggregate from the google sheet to understand how much AI tokens/costs your clients are liable for.\n\n\n**Check out the example Client Usage Log** - {{ $env.WEBHOOK_URL }}\n\n### How to use\n* **SELF-HOSTED N8N ONLY** - the Langchain Code node is only available in the self-hosted version of n8n. It is not available in n8n cloud.\n* The LLM subnode can only be attached to non-\"AI agent\" nodes; Basic LLM node, Information Extractor, Question & Answer Chain, Sentiment Analysis, Summarization Chain and Text Classifier.\n\n### Need Help?\nJoin the [Discord]({{ $env.WEBHOOK_URL }} or ask in the [Forum]({{ $env.WEBHOOK_URL }}"}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "922710e3-f92b-4a7f-9ff2-c3d7d55f04d5", "name": "Sticky Note7", "type": "n8n-nodes-base.stickyNote", "position": [-1040, -420], "parameters": {"color": 3, "width": 280, "height": 120, "content": "### SELF-HOSTED N8N ONLY\nPlease note, this template only works in the self-hosted version of n8n only. It will not work in the cloud version."}, "typeVersion": 1, "notes": "This stickyNote node performs automated tasks as part of the workflow."}, {"id": "56c23cb5-818f-434d-96a7-0029f6607299", "name": "Parse PDF Upload", "type": "n8n-nodes-base.extractFromFile", "position": [-700, -120], "parameters": {"options": {}, "operation": "pdf", "binaryPropertyName": "Upload_a_file"}, "typeVersion": 1, "notes": "This extractFromFile node performs automated tasks as part of the workflow."}, {"id": "f4cc9870-a73e-487c-a131-aca2735b2e60", "name": "Extract Resume Data", "type": "n8n-nodes-base.noOp", "position": [0, -120], "parameters": {"text": "={{ $json.text }}", "options": {}, "schemaType": "manual", "inputSchema": "{\n  \"type\": \"object\",\n  \"properties\": {\n    \"name\": { \"type\": \"string\" },\n    \"label\": { \"type\": \"string\" },\n    \"email\": { \"type\": \"string\" },\n    \"phone\": { \"type\": \"string\" },\n    \"url\": { \"type\": \"string\" },\n    \"summary\": { \"type\": \"string\" },\n    \"location\": {\n      \"type\": \"object\",\n      \"properties\": {\n        \"address\": { \"type\": \"string\" },\n        \"postalCode\": { \"type\": \"string\" },\n        \"city\": { \"type\": \"string\" },\n        \"countryCode\": { \"type\": \"string\" },\n        \"region\": { \"type\": \"string\" }\n      }\n    },\n    \"work\": {\n      \"type\": \"array\",\n      \"items\": {\n        \"type\": \"object\",\n        \"properties\": {\n          \"name\": { \"type\": \"string\" },\n          \"location\": { \"type\": \"string\" },\n          \"description\": { \"type\": \"string\" },\n          \"position\": { \"type\": \"string\" },\n          \"url\": { \"type\": \"string\" },\n          \"startDate\": { \"type\": \"string\" },\n          \"endDate\": { \"type\": \"string\" },\n          \"summary\": { \"type\": \"string\" },\n          \"highlights\": {\n            \"type\": \"array\",\n            \"items\": { \"type\": \"string\" }\n          }\n        }\n      }\n    },\n    \"education\": {\n      \"type\": \"array\",\n      \"items\": {\n        \"type\": \"object\",\n        \"properties\": {\n          \"institution\": { \"type\": \"string\" },\n          \"url\": { \"type\": \"string\" },\n          \"area\": { \"type\": \"string\" },\n          \"studyType\": { \"type\": \"string\" },\n          \"startDate\": { \"type\": \"string\" },\n          \"endDate\": { \"type\": \"string\" },\n          \"score\": { \"type\": \"string\" },\n          \"courses\": {\n            \"type\": \"array\",\n            \"items\": { \"type\": \"string\" }\n          }\n        }\n      }\n    },\n    \"skills\": {\n      \"type\": \"array\",\n      \"items\": {\n        \"type\": \"object\",\n        \"properties\": {\n          \"name\": { \"type\": \"string\" },\n          \"level\": { \"type\": \"string\" },\n          \"keywords\": {\n            \"type\": \"array\",\n            \"items\": { \"type\": \"string\" }\n          }\n        }\n      }\n    }\n  }\n}"}, "typeVersion": 1, "notes": "This informationExtractor node performs automated tasks as part of the workflow."}, {"id": "error-1f073de8", "name": "Error Handler", "type": "n8n-nodes-base.stopAndError", "typeVersion": 1, "position": [1000, 400], "parameters": {"message": "Workflow execution error", "options": {}}}], "pinData": {}, "connections": {"8884df86-b7cd-4cf7-8b71-fd21113bfc0f": {"main": [[{"node": "error-handler-8884df86-b7cd-4cf7-8b71-fd21113bfc0f-38063a2b", "type": "main", "index": 0}]]}, "10d95dcb-d975-4b20-961e-d1fe63417878": {"main": [[{"node": "error-handler-10d95dcb-d975-4b20-961e-d1fe63417878-0d016b57", "type": "main", "index": 0}]]}, "56c23cb5-818f-434d-96a7-0029f6607299": {"main": [[{"node": "error-handler-56c23cb5-818f-434d-96a7-0029f6607299-c1697ca7", "type": "main", "index": 0}]]}}, "name": "Googlesheetstool Workflow", "settings": {"executionOrder": "v1", "saveManualExecutions": true, "callerPolicy": "workflowsFromSameOwner", "errorWorkflow": null, "timezone": "UTC", "executionTimeout": 3600, "maxExecutions": 1000, "retryOnFail": true, "retryCount": 3, "retryDelay": 1000}, "description": "Automated workflow: Googlesheetstool Workflow. This workflow integrates 14 different services: stickyNote, formTrigger, filter, code, scheduleTrigger. It contains 23 nodes and follows best practices for error handling and security.", "tags": ["automation", "n8n", "production-ready", "excellent", "optimized"], "notes": "Excellent quality workflow: Googlesheetstool Workflow. This workflow has been optimized for production use with comprehensive error handling, security, and documentation."}