I once handed a monthly sales sheet to Gemini and asked for category-level trends. The prose came back fluent and confident, and the categories were wrong.
My first instinct was to blame the prompt. Tightening the instructions changed the shape of the error but never removed it. Switching models did the same. When I finally stopped editing the prompt and read the string I was actually sending, the problem turned out to sit well upstream of the model. The table had already fallen apart during extraction.
If you build anything solo on top of spreadsheets, you know the layout I mean. It was built for a human eye: category labels merged down a column, a month header spanning two columns, a spacer row in the middle, a total row at the bottom. What follows is a count of exactly what that layout loses when you read it programmatically, and the Python I now run before anything reaches the API.
Half the rows lose their key the moment you read the file
The fixture I used mirrors my own wallpaper app sales summary. The category column merges runs of identical values vertically. Month names sit on row 1, with unit and revenue columns underneath on row 2. There is a blank separator row, and a total row at the end.
Reading it with openpyxl and dumping it straight to CSV gives this:
Category,Product,July 2026,,August 2026,
,,Units,Revenue,Units,Revenue
Wallpaper,Four Seasons,1200,84000,1310,91700
,Ukiyo-e,940,65800,1005,70350
,Night Views,610,42700,588,41160
Calm,Campfire,430,30100,402,28140
,Ocean Waves,380,26600,411,28770
,,,,,
Tools,Unit Converter,220,15400,198,13860
,Compass,160,11200,175,12250
Total,,3940,275800,4089,286230Counted in the same script:
| Metric | Raw extraction |
|---|---|
| Data rows | 8 |
| Rows with an empty category | 4 (50% of data rows) |
| Duplicate column names | Units ×2 / Revenue ×2 |
| Rows consumed by the header | 2 |
A merged range stores its value only in the top-left cell. Every other cell in the range is genuinely empty. On screen, Wallpaper appears to span three rows; in the file, two of those three rows have nothing. Ukiyo-e and Night Views were reaching the model as rows with no category at all.
The header has the same problem from the other direction. July 2026 exists only in column C, and column D is blank. Row 2 then repeats Units and Revenue twice. Send those two rows as separate lines and you have handed over a table with two columns named Units and no way to tell which month is which.
It is worth saying what does not fix this. Switching the serialization from CSV to a Markdown table, or to JSON, or to an indented outline, changes none of the numbers above. The loss happens when the file is read, not when the text is formatted, so every downstream representation faithfully reproduces the same four rows with no category. I spent an evening cycling through output formats before that occurred to me, which is the main reason this article exists.
Google Docs tables fail in a related but distinct way, with a different repair. I covered that case in The Table Was There, but the Rows and Columns Weren't.
Expand merged ranges immediately after reading
The fix is not clever, which is the good news. The merge geometry is still in the file, so you can broadcast each top-left value across its whole range right after loading.
from openpyxl import load_workbook
def expand_merges(ws):
"""Broadcast each merged range's top-left value across the range."""
grid = [[c.value for c in row] for row in ws.iter_rows()]
for rng in ws.merged_cells.ranges:
top = grid[rng.min_row - 1][rng.min_col - 1]
for r in range(rng.min_row - 1, rng.max_row):
for c in range(rng.min_col - 1, rng.max_col):
grid[r][c] = top
return grid
ws = load_workbook("sales.xlsx").active
grid = expand_merges(ws)ws.merged_cells.ranges reports vertical and horizontal merges in the same shape, which is why one loop handles both the stacked category labels and the spanning month headers.
At this point the four empty category cells are filled. The header is still two rows tall.
Collapse the two-row header column by column
The awkward part of flattening a header is repetition. Column B holds Product merged vertically, so after expansion both header rows say Product. Join them mechanically and you get a column called Product / Product.
Dropping duplicates before joining, then numbering any names that still collide, handles both cases in one pass.
def flatten_header(grid, depth):
"""Collapse the first `depth` rows into one header; return it with the body."""
head = grid[:depth]
names, seen = [], {}
for col in range(len(grid[0])):
parts = [str(head[r][col]).strip() for r in range(depth)
if head[r][col] not in (None, "")]
uniq = []
for p in parts:
if p not in uniq:
uniq.append(p)
name = " / ".join(uniq) or f"col{col + 1}"
seen[name] = seen.get(name, 0) + 1
if seen[name] > 1:
name = f"{name}#{seen[name]}"
names.append(name)
return names, grid[depth:]
def drop_blank(rows):
return [r for r in rows if any(v not in (None, "") for v in r)]depth stays an explicit argument because three-tier headers are real and I have several. I did try detecting the depth automatically and abandoned it. When the first data row is a string, it is indistinguishable from a header row, and silently eating one row of data is a worse failure than typing a number. Passing 2 or 3 per sheet also documents the sheet for whoever reads the code next.
The result:
Category,Product,July 2026 / Units,July 2026 / Revenue,August 2026 / Units,August 2026 / Revenue
Wallpaper,Four Seasons,1200,84000,1310,91700
Wallpaper,Ukiyo-e,940,65800,1005,70350
Wallpaper,Night Views,610,42700,588,41160
Calm,Campfire,430,30100,402,28140
Calm,Ocean Waves,380,26600,411,28770
Tools,Unit Converter,220,15400,198,13860
Tools,Compass,160,11200,175,12250
Total,,3940,275800,4089,286230Empty categories went from 4 to 0, duplicate column names from two pairs to none, and the spacer row is gone.
Leave the total row in and the numbers double exactly
I felt finished at that point, which was premature. The last row is still a total.
Summing the July units column on the flattened table:
| How you sum | July units |
|---|---|
| Every row as-is | 7,880 |
| Excluding the total row | 3,940 |
Exactly double, because the total row now looks like an ordinary data row. A human reads the word Total and stops. String matching does not survive contact with reality: my sheets variously say Total, 合計, Subtotal, or nothing at all in that cell.
Checking the arithmetic turned out to be far more durable. If a row's value in some column equals the sum of every other row in that column, it is almost certainly an aggregate.
def looks_like_total(rows, col):
"""Return indexes of rows whose value equals the sum of all other rows."""
hits = []
column = [r[col] for r in rows]
for i, r in enumerate(rows):
others = sum(x for j, x in enumerate(column) if j != i)
if r[col] == others:
hits.append(i)
return hits
hits = looks_like_total(body, 2) # 2 = July 2026 / Units
clean = [r for i, r in enumerate(body) if i not in hits]On this table hits picks out the last row only, and the remaining sum is 3,940. One numeric column with few gaps is enough. Requiring agreement across several columns made the check miss more often on short tables, not less.
One honest limitation: this assumes a single grand total. Sheets with per-category subtotals at the end of each block will defeat it, because the grand total no longer matches the sum of the rows above once subtotals are mixed in. For those, re-exporting the raw rows without aggregate columns is faster than teaching the detector to be clever.
Cleaning the table does not make it smaller
This is where my expectation was simply backwards. Filling blanks and deleting a spacer row felt like it should shrink the payload. Measured:
| Form | Characters |
|---|---|
| Raw extraction (CSV) | 281 |
| Flattened (CSV) | 302 |
Twenty-one characters more, a 7.5% increase. Refilling the category column and prefixing the month onto four column names outweighed everything the cleanup removed.
So this is not a token-saving technique, and it should not be sold as one. Those 21 characters buy back the keys for 4 of 8 rows and disambiguate two pairs of identically named columns. For my workloads that trade has been worth it every time. It also implies the obvious limit: pasting a flattened table with tens of thousands of rows is a volume problem no amount of tidying will solve. At that size, aggregate first and summarize the aggregate.
Sending the cleaned table
Once the table is honest, the call itself can stay plain. I put the flattened CSV in the prompt body and take structured output back.
from google import genai
from google.genai import types
from pydantic import BaseModel
class RowInsight(BaseModel):
category: str
product: str
delta_units: int
note: str
client = genai.Client(api_key="YOUR_API_KEY")
config = types.GenerateContentConfig(
system_instruction=(
"The input is CSV with a header on line 1. "
"In a column name, the text before ' / ' is the month. "
"Total rows have already been removed."
),
response_mime_type="application/json",
response_schema=list[RowInsight],
)
response = client.models.generate_content(
model="gemini-3.7-flash",
contents=f"List the rows with the largest month-over-month change.\n\n{csv_text}",
config=config,
)
rows = response.parsedThe line about total rows already being removed is aimed at me as much as at the model. If the preprocessing ever stops removing them, that sentence becomes a lie sitting in plain sight in the code, which is roughly the earliest warning I can arrange for myself.
Sampling parameters such as temperature were deprecated in August 2026, so they are absent here on purpose. Running config.model_dump(exclude_none=True) locally shows exactly three keys going out: response_mime_type, response_schema, and system_instruction. Looking at that list once makes every later addition to the config easy to spot.
If you want the whole extract-transform-call path as one pipeline, Google Sheets API × Gemini API: A Python Data Pipeline goes further on the plumbing. For deciding how much to leave to Sheets' own features versus your own code, Sheets canvas Takes the Entry Point, Not the Execution Boundary lays out the criteria I use.
What to do next
Take one sheet you actually use and count the blank cells in its key column before and after expand_merges. If the count stays at zero, you do not need any of this. If it drops even by one, that difference is the exact amount of ambiguity you have been asking the model to resolve on your behalf.