Track LLM Token Usage and Agent Observability on Google Sheets

{
  "id": "B68FORPfxycLElJZ",
  "meta": {
    "instanceId": "36fee986cc83112881fb12ec7cc2d0221d7bddd71c11715c196899b114e8b0d2"
  },
  "name": "Track LLM Token Usage and Agent Observability on Google Sheets",
  "tags": [],
  "nodes": [
    {
      "id": "74b8b8f2-8152-4a8f-991b-6eb8dea2ad4a",
      "name": "When chat message received",
      "type": "@n8n/n8n-nodes-langchain.chatTrigger",
      "position": [
        0,
        0
      ],
      "webhookId": "5e11697c-dfc4-457e-b535-43f65ba8da81",
      "parameters": {
        "public": true,
        "options": {},
        "initialMessages": "Welcome to Troopers!\n\nSo glad you’re here! 😊 Are you already a Troopers client, or just getting started?"
      },
      "typeVersion": 1.1
    },
    {
      "id": "cbcf9a4b-1f17-4dac-8ee2-0c58766923d2",
      "name": "AI Agent",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        464,
        0
      ],
      "parameters": {
        "text": "={{  $('When chat message received').item.json.chatInput }}",
        "options": {
          "systemMessage": "=You are a helpful assistant",
          "returnIntermediateSteps": true
        },
        "promptType": "define"
      },
      "typeVersion": 2.1
    },
    {
      "id": "482a8e1a-3d9e-419c-a671-932fc0724c1c",
      "name": "Token Usage Log",
      "type": "n8n-nodes-base.googleSheetsTool",
      "position": [
        528,
        432
      ],
      "parameters": {
        "columns": {
          "value": {
            "date": "={{ $fromAI(\"date\") }}",
            "model": "={{ $fromAI(\"model\") }}",
            "llm_node": "={{ $fromAI(\"llm_node\") }}",
            "client_id": "={{ $fromAI(\"client_id\") }}",
            "input_cost": "={{ $fromAI(\"input_cost\") }}",
            "total_cost": "={{ $fromAI(\"total_cost\") }}",
            "output_cost": "={{ $fromAI(\"output_cost\") }}",
            "workflow_id": "={{ $fromAI(\"workflow_id\") }}",
            "execution_id": "={{ $fromAI(\"execution_id\") }}",
            "input_tokens": "={{ $fromAI(\"input_tokens\") }}",
            "total_tokens": "={{ $fromAI(\"total_tokens\") }}",
            "output_tokens": "={{ $fromAI(\"output_tokens\") }}",
            "input_token_cost ($ / million)": "={{ $fromAI(\"input_token_cost\") }}",
            "output_token_cost ($ / million)": "={{ $fromAI(\"output_token_cost\") }}"
          },
          "schema": [
            {
              "id": "date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "llm_node",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "llm_node",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "model",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "model",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "workflow_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "workflow_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "execution_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "execution_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "client_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "client_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "input_tokens",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "input_tokens",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "input_token_cost ($ / million)",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "input_token_cost ($ / million)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "output_tokens",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "output_tokens",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "output_token_cost ($ / million)",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "output_token_cost ($ / million)",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "total_tokens",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "total_tokens",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "input_cost",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "input_cost",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "output_cost",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "output_cost",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "total_cost",
              "type": "string",
              "display": true,
              "removed": false,
              "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": "https://docs.google.com/spreadsheets/d/1O4PVOD584KWLzzj8gEym95hs8z3-Y4UjImMwKfby98c/edit#gid=0",
          "cachedResultName": "Token cost tracker"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": ""
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "wOT0EGRAXG51Ihtb",
          "name": "Google Sheets account (Augra)"
        }
      },
      "typeVersion": 4.5
    },
    {
      "id": "34d054aa-9ad4-4381-8707-77903eef3b71",
      "name": "Sticky Note8",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -832,
        304
      ],
      "parameters": {
        "width": 624,
        "height": 560,
        "content": "## Start here: Step-by Step Youtube Tutorial :star:\nFor model-specific tweaks to the LangChain Code node, follow your provider’s LangChain setup. For a walkthrough, see the attached video:\n\n[![Building an AI Personal Assistant](https://img.youtube.com/vi/JSulRS128MA/sddefault.jpg)](https://youtu.be/JSulRS128MA)\n"
      },
      "typeVersion": 1
    },
    {
      "id": "78fa52a2-c7c4-4c88-a357-57e6b03541e1",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -832,
        -368
      ],
      "parameters": {
        "width": 624,
        "height": 640,
        "content": "**Start here (required settings)**\n\n* In **Set • Workflow + Client Metadata**, set `client_id`. Keep `workflow_id` and `execution_id` as is.\n* In **LangChain Chat Model + Token Callback**, set `model`, `input_token_cost`, `output_token_cost`.\n* Ensure `OPENAI_API_KEY` or your provider key is set as an environment variable.\n* In **Token Usage Log**, select your Spreadsheet and the Metrics sheet. **Replace with…** Sheet ID and Sheet name.\n* In **Log • Token Metrics to Sheets (Tool)**, select your Spreadsheet and the Observability sheet. **Replace with…** Sheet ID and Sheet name.\n* Add **Google Sheets OAuth2** credentials.\n\n\n**What this workflow does**\n\n* Receives a chat message and runs an AI Agent.\n* Tracks tokens and computes costs in a LangChain callback.\n* Logs token metrics to a Metrics sheet.\n* Logs input, output, and tool usage to an Observability sheet.\n* Associates each row with workflow, execution, and client IDs.\n\n\n**Customize**\n\n* Change `model` and token prices to match your provider’s pricing.\n* Add more metadata fields in **Set metadata** as needed.\n* Extend the callback to include latency or provider request IDs.\n* Add a Limit or Sample node for high-volume runs.\n* Swap the Chat Trigger for Webhook Trigger if you do not need the built-in chat."
      },
      "typeVersion": 1
    },
    {
      "id": "ae40d18f-8bc8-4a4f-8ddb-f31099fcd24b",
      "name": "Set • Workflow + Client Metadata",
      "type": "n8n-nodes-base.set",
      "position": [
        224,
        0
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "ca1744cf-69e7-4a85-81d3-2fb2b936e496",
              "name": "workflow_id",
              "type": "string",
              "value": "={{ $workflow.id }}"
            },
            {
              "id": "205c7c4d-2dfa-471f-9306-33d30e16722a",
              "name": "execution_id",
              "type": "string",
              "value": "={{ $execution.id }}"
            },
            {
              "id": "3fa4932f-c3cc-41a8-90a4-d1b3a2d32cdd",
              "name": "client_id",
              "type": "string",
              "value": "123"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "0b9d9c4b-7bc7-4057-a78f-bc5fc9b6f857",
      "name": "LangChain Chat Model + Token Callback",
      "type": "@n8n/n8n-nodes-langchain.code",
      "position": [
        448,
        224
      ],
      "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 = process.env.OPENAI_API_KEY;\nconst model = \"gpt-5-mini-2025-08-07\";\nconst input_token_cost = 0.25;\nconst output_token_cost = 2.00;\n\n// 2. Customize LLM token tracker\nconst tools = await this.getInputConnectionData('ai_tool', 0);\nconst googleSheetTool = tools[0];\n\nconst {\n  workflow_id,\n  execution_id,\n  client_id } = $('Set metadata').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          model,\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
    },
    {
      "id": "383dc17c-d479-4f3d-965f-f708931c4865",
      "name": "Branch • Tool Used?",
      "type": "n8n-nodes-base.if",
      "position": [
        816,
        0
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "9c8947e8-fa44-4e3e-b869-9284afc03a61",
              "operator": {
                "type": "array",
                "operation": "empty",
                "singleValue": true
              },
              "leftValue": "={{ $json.intermediateSteps }}",
              "rightValue": ""
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "e9f7bef2-c21e-45e1-ade5-33fdcee5fc0a",
      "name": "Log • Token Metrics to Sheets (Tool)",
      "type": "n8n-nodes-base.googleSheets",
      "position": [
        1040,
        0
      ],
      "parameters": {
        "columns": {
          "value": {
            "date": "={{ $now }}",
            "input": "={{ $('When chat message received').item.json.chatInput }}",
            "output": "={{ $json.output }}",
            "Tool use": "={{ $json.intermediateSteps[0].action.tool }}",
            "client_id": "={{ $('Set • Workflow + Client Metadata').item.json.client_id }}",
            "workflow_id": "={{ $('Set • Workflow + Client Metadata').item.json.workflow_id }}",
            "execution_id": "={{ $('Set • Workflow + Client Metadata').item.json.execution_id }}"
          },
          "schema": [
            {
              "id": "date",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "date",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "workflow_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "workflow_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "execution_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "execution_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "client_id",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "client_id",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "input",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "input",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "Tool use",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "Tool use",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            },
            {
              "id": "output",
              "type": "string",
              "display": true,
              "removed": false,
              "required": false,
              "displayName": "output",
              "defaultMatch": false,
              "canBeUsedToMatch": true
            }
          ],
          "mappingMode": "defineBelow",
          "matchingColumns": [],
          "attemptToConvertTypes": false,
          "convertFieldsToString": false
        },
        "options": {},
        "operation": "append",
        "sheetName": {
          "__rl": true,
          "mode": "list",
          "value": 94625050,
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1O4PVOD584KWLzzj8gEym95hs8z3-Y4UjImMwKfby98c/edit#gid=94625050",
          "cachedResultName": "Observability"
        },
        "documentId": {
          "__rl": true,
          "mode": "list",
          "value": "1O4PVOD584KWLzzj8gEym95hs8z3-Y4UjImMwKfby98c",
          "cachedResultUrl": "https://docs.google.com/spreadsheets/d/1O4PVOD584KWLzzj8gEym95hs8z3-Y4UjImMwKfby98c/edit?usp=drivesdk",
          "cachedResultName": "Track LLM Tokens"
        }
      },
      "credentials": {
        "googleSheetsOAuth2Api": {
          "id": "wOT0EGRAXG51Ihtb",
          "name": "Google Sheets account (Augra)"
        }
      },
      "typeVersion": 4.6
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "67022965-a398-44c9-b241-bd09709760ec",
  "connections": {
    "AI Agent": {
      "main": [
        [
          {
            "node": "Branch • Tool Used?",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Token Usage Log": {
      "ai_tool": [
        [
          {
            "node": "LangChain Chat Model + Token Callback",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "Branch • Tool Used?": {
      "main": [
        [],
        [
          {
            "node": "Log • Token Metrics to Sheets (Tool)",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "When chat message received": {
      "main": [
        [
          {
            "node": "Set • Workflow + Client Metadata",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Set • Workflow + Client Metadata": {
      "main": [
        [
          {
            "node": "AI Agent",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "LangChain Chat Model + Token Callback": {
      "ai_languageModel": [
        [
          {
            "node": "AI Agent",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    }
  }
}