Cutting LLM tokens on big spreadsheets

I am a data engineer in e-commerce. I often need to work with big Google Sheets tables: orders, customers, reports, financial data. Tens and hundreds of thousands of rows are normal.

I wanted Claude to work with these tables: read, write, calculate. And I wanted the data to go nowhere except Google. I did not find anything ready like this, and third-party MCP servers were not an option because of data privacy. So I made my own, gsheets-mcp. It is a local MCP server. It works directly with the Google Sheets API and does not send data anywhere else. I built it for myself and made it public.

For testing I use a synthetic Amazon Settlement report with 50 thousand rows. It has the same structure as a real one, but the products and IDs are made up. The sheet is public, so you can repeat the question. The question is: "Where did the money go? Break it down by amount type." This is a common task in e-commerce: to understand where the money went between the sales and the payout.

Below are the steps I went through and what each of them gave in tokens.

Why you can't just give the table to the model

The test file has 50,009 rows and 24 columns. In the most compact form (TSV) it is 5.8M tokens, in "pretty" JSON almost 19M. The context window of Opus 5.5 is 1M. But even if it fit, there are three more problems:

1. The client will not let it through. Claude Code limits the result of one tool call to 25 thousand tokens, and everything bigger goes to a file. The model does not get the data. It gets a note:

Error: result (965,076 characters across 5,003 lines) exceeds maximum allowed tokens. Output has been saved to …

2. Money. Input tokens for Opus 5.5 cost $4 per million. Reading the tab once costs about $23, and an agent re-reads its context at every step.
3. Arithmetic. Even if everything fit, adding up 50 thousand amounts "in its head" is not something I would trust with accounting.

One detail surprises almost everyone: in tables like this, one token is about 1.7 characters, not 4 like in normal text. Order IDs, dates and amounts are cut into many small tokens.

How I measured. One file, one question, every release of the server from v0.1 to v0.4.4. Each release ran against a copy of the sheet in memory, so the output is byte for byte what the model gets. Tokens were counted with the Claude Opus 5.5 tokenizer. Everything can be reproduced with one command from the repo: python bench/tokens.py.

Starting point: v0.1

The first version (August 10) returned the sheet as JSON with indentation. To answer the question, the model needs two columns, the amount type and the amount, for all rows. With the most careful approach this is 11 calls and 1.83M tokens. And if the model just "reads the sheet", it gets 995K tokens, and only the first 5,000 rows of 50,009. That is 10% of the data.

1. Less data: format and pages

Format

The same 200 rows in different formats, tokens per row:

What this shows:

1. Repeating keys costs the most. JSON records carry 24 column names in every row: 303 tokens against 125 for the same data as an array.
2. Indentation is second. indent=2 puts every cell on its own line: 199 against 125. The fix is one argument when you serialize.
3. Between flat formats the difference is small. CSV, TSV and a compact JSON array are within 8% of each other. The "JSON or CSV" argument is almost about nothing. What matters is to not repeat keys and not indent.

Pages

v0.1 downloaded the whole sheet from Google (13 MB) and gave the model the first 5,000 rows, with a note that the rest did not fit. v0.2 asks Google only for the needed page (1.3 MB, 0.7 seconds) and tells the model which offset to continue from.

But to be honest, pages do not reduce the total. It is still the same 11 calls. And rows are the wrong unit. A page of 5,000 rows of this sheet is 579K tokens, 23 times more than the Claude Code limit. About 216 rows fit under the limit.

Result of the step: a row got cheaper from 199 to 116 tokens (−42%), and the whole question went from 1.83M to 1.03M tokens, 1.8 times smaller.

Takeaway: do not underestimate basic and simple optimizations, they noticeably reduce the volume. But it is still only 1.8 times. The sheet is still almost 6M tokens. Format does not solve the volume problem.

2. Preview and reading by columns

Preview

Before v0.4, gsheets_list_sheets returned only tab names and grid sizes: 100–130 tokens. Grid size is not data size: an empty tab "has" 1,000 rows. To see the columns, the model had to read: 1,381 tokens if it thought to take a piece like A1:X11, or 579K if it read the whole sheet.

Since v0.4.0, one call describes every tab: the letter, name and type of each column, three sample rows, the real number of rows, and a flag if there is a totals row under the table. For our sheet it is 633 tokens (340 without sample rows). It is three requests to Google for the whole document, no matter how many tabs it has.

amazon_settlement_test_50k	gid=517145915	rows~50010
  A	settlement-id	number
  …
  G	transaction-type	text
  H	order-id	text
  …
  M	amount-type	text
  N	amount-description	text
  O	amount	number
  …

To be honest about the limits: types are detected from the first 10 rows. Here adjustment-id and promotion-id are shown as empty, because the first rows are all orders and refunds come later.

For the full question the preview alone changes nothing: 1.03M => 1.03M. Its value is different. The model knows the column letters and the scale. It is a map, and the next steps do not work without it.

Only the columns you need

columns: ["M", "O"] is one request to Google, and the columns are joined back into rows on the server. A row costs 11.6 tokens instead of 116, 10 times less. The whole question: 1.03M => 580K, another 1.8 times smaller.

But it is still 11 calls of about 58K tokens each, every one bigger than the Claude Code limit. And the model still has to add up 50,009 numbers.

Takeaway: giving the model an overview of the data is very important, the next requests become much more efficient. The first look at the document is 633 tokens instead of 579K blind, one call instead of two. But the saving is not the main thing, the map is. Without exact column names the model cannot ask for columns or for an aggregate.

3. Aggregation on the server: the answer instead of rows

gsheets_aggregate does grouping, metrics (count, count_distinct, sum, min, max, avg), a where filter and a limit. Only the named columns are read from Google (all rows), and only the groups go back to the model. One call:

{"sheet_name": "amazon_settlement_test_50k",
 "group_by": ["amount-type"],
 "metrics": [{"column": "amount", "fn": "sum"}]}

scanned: 50009 rows
groups: 9, shown: 9, sorted by sum(amount) desc

amount-type	sum(amount)
ItemPrice	246220.28
…
ItemFees	-87170.27

154 tokens instead of 580K, about 3,760 times fewer. It takes about a second: 2 requests to Google, the server read 100 thousand cells, and the model read none of them.

What is inside:

• It counts like a person. Empty cells do not count, and a totals row with an empty order id is not an order.
• It reads numbers the way the sheet shows them: $1,240.50, (340), 1.234,56 €, 12%.
• A bug story. In 0.4.4 I found that -$87 (the minus goes before the dollar sign) was read as text, and the sums silently lost refunds. The total still looked believable. That is the most dangerous kind of bug.
• Mixed percentages. If one group mixes 12% and 0.12, you get a warning instead of a quietly wrong sum.
• No HAVING, OR or nested groups, on purpose. A model fills in a small schema correctly.

A live run (Opus 5.5 in Claude Code, the public sheet): the preview and 5 aggregates, 1,878 tokens of results, $0.39. Here is what the model answered (shortened):

The rows add up to the payout in the report, to the cent.

Where the tokens go now, for the whole conversation:

When the data is out of the way, you pay for what you want to pay for: the model's thinking.

And the result of all steps: 1.83M => 787 tokens, 2,331 times fewer.

Takeaway: tools for processing table data give the biggest saving. The main thing is to not send the data at all, instead of compressing it. The model needs the answer, not the rows. And it is better to calculate next to the data: an LLM is an expensive and unreliable calculator.

The hidden cost: tools in every conversation

Tool descriptions sit in every conversation, even if there are no tables in it: 3,708 tokens in v0.1, 6,473 in v0.4.4. Aggregation alone added about 1,000. In read-only mode it is 3,310. It is a good deal: plus a thousand tokens per conversation against millions per question. But descriptions are a prompt, so keep them short.

Why not pandas or Google SQL

I had ideas to make a wrapper around pandas, or to let the model run SQL directly in Google Sheets. I decided to keep everything as simple as possible.

Google's query language, the one behind =QUERY(), says in its own documentation: "In case of mixed data types in a single column, the majority data type determines the data type of the column for query purposes. Minority data types are considered null values." So in a "dirty" column some values silently become null, and the total is quietly wrong. It is the same kind of bug as with -$87.

pandas inside the server is a heavy dependency. And to make it flexible, I would have to let the model run code next to cells from someone else's sheet. That is a direct road to prompt injection.

So the server does grouping and filtering. For complex analysis you export a CSV and work in pandas or DuckDB, where code already runs.

What's next

Charts are already there (gsheets_add_chart): the model can draw the result in the sheet right away. Next in the plans:

1. Search in the data: a filter when reading, the same where as in the aggregate. "All rows of order X" is 28 rows and 3,409 tokens instead of 5.8M.
2. Reconcile two tabs by a key. For example, shop orders against the settlement: the server returns only the differences to the model. A classic e-commerce task.
3. Pages by tokens, not by rows. About 216 full rows of this sheet fit under the Claude Code limit.
4. Do not repeat constants. In this report settlement-id is the same in all rows, 6 columns are empty, and merchant-order-id repeats order-id in 98.6% of rows. Without them a row is 83 tokens instead of 116 (−29%).
5. Compress or mark data that means nothing for the analysis: emails, phone numbers, addresses, long IDs. To count and group, the model does not need the address itself. A short stable label like c_0412 is enough: one address, one label. Fewer tokens, and personal data does not go to the model.
6. A smarter preview. Look not only at the start of the sheet but also at the middle and the end, and show the values of small columns so filters hit exactly.
7. Aggregates by time: by day, week and month.
8. Median and percentiles in the aggregate. The average order value lies, p50 and p90 do not.
9. Write the aggregate result straight into a new tab. The report is built on the server, the data never goes through the model, and the model only sees "done".
10. Export to a temporary file, for agents that can run code.
11. A Python package, finally. So you can install it with one command, without cloning the repo and putting three paths into the config.
12. Something else? Send ideas to GitHub issues or my email