Query a large Google Sheet from an n8n AI agent

By Killian, founder · Updated 27 October 2026 · Markdown

The usual n8n pattern for “let the AI Agent look things up in a sheet” is a Google Sheets node that returns every row, sometimes a Code node to filter, and then the whole result handed to the model. On a big sheet that’s slow, it eats tokens, and the agent often only sees the first items.

A better pattern is to give the agent a tool that queries the sheet server-side: it sends a filter or a group-by and gets back only the answer. This guide does that with datatomcp and n8n’s HTTP Request Tool.

HTTP Request Tool or MCP Client Tool?

n8n has two ways to give an AI Agent an outside tool:

datatomcp offers both a REST API (API keys) and an MCP server (OAuth sign-in, built for chat and coding agents like Claude, ChatGPT and Cursor). For n8n we recommend the REST API: an API key doesn’t expire, needs no browser sign-in on the n8n server, and you can see exactly what the agent sends. The MCP route is the one to use in chat apps; see how to add a remote MCP server to any AI client.

1. Make the sheet queryable

1. Sign in at app.datatomcp.com, Add data → Google Sheet, and pick the sheet. Private sheets work: you grant access to that file only.
2. Note the dataset id (ds_…) on the dataset page and the column ids in the columns table.
3. Under Account → API keys, create a key named n8n.

2. Add the tool to your AI Agent

1. In your workflow, add an HTTP Request Tool to the AI Agent node’s tools.
2. Method POST, URL https://app.datatomcp.com/v1/datasets/ds_YOUR_ID/query.
3. Authentication: a header credential with name Authorization and value Bearer dtm_….
4. Send a JSON body and let the model fill it in. Describe the tool so the agent knows the format, for example:

Queries the sales sheet. Body is JSON: where (list of {column, op, value}; ops eq, neq, gt, gte, lt, lte, in, contains, between), optional group_by (column ids), metrics (list of {fn: count|sum|avg|min|max|count_distinct, column, as}), order_by, limit. Columns: order_date (date), country (text), product (text), total (number).

3. Try it

Ask the agent:

Which three products sold the most in Germany in September?

It sends:

{
  "where": [
    { "column": "country", "op": "eq", "value": "Germany" },
    { "column": "order_date", "op": "between", "value": ["2026-09-01", "2026-09-30"] }
  ],
  "group_by": ["product"],
  "metrics": [{ "fn": "sum", "column": "total", "as": "revenue" }],
  "order_by": [{ "column": "revenue", "dir": "desc" }],
  "limit": 3
}

and receives three rows, however large the sheet is.

Tips

• Add a second HTTP Request Tool for GET /v1/datasets/ds_YOUR_ID/values/{column} so the agent can look up exact category names before filtering.
• Errors come back as JSON with a readable message (for example an unknown column id), which the agent can use to correct its next call.
• The sheet stays current with Refresh now in the app, a POST /v1/datasets/:id/refresh step in your workflow, or hourly sync on Pro.

Without the AI Agent

The same HTTP Request (non-tool) node works in ordinary workflows: one node that returns filtered rows or totals, instead of read-all-then-filter. See Google Sheets as an API. The same request works from Make and Zapier.