The Problem: Visible Data vs. Readable Data
Yesterday morning, our Claude Code automation running on a Windows device sent an odd alert. Google Sheets clearly showed 300 order entries, but the bot had only read 287.
「That's strange. Where did the other 13 go?」
When our dream team (the hamsters and the pups) traced the issue, they found a pattern: all the missing entries had background colors or special formatting applied to their cells.
The Root Cause: Google Sheets API's Invisible Boundary
When Claude Code reads data through the Google Sheets API, it primarily extracts "values" only. Visual elements like colors, font styles, and conditional formatting require separate API calls, which we hadn't configured in our initial setup.
The key insight: when we enter data manually, colors don't matter. But for an automation bot, that same color carries semantic meaning like "data verified" or "high priority."
Solution 1: Replace Conditional Formatting with Helper Columns
The simplest fix is to store formatting information as a separate column.
| Order ID | Product | Status | Priority(Text) |
|----------|------------|---------|----------------|
| 001 | Shirt | Confirm | HIGH |
| 002 | Pants | Pending | LOW |
By storing information as text values like "HIGH" or "LOW" instead of colors, Claude Code reads it flawlessly.
Solution 2: Advanced Google Sheets API Configuration
When connecting Google Sheets in Claude Code, you can adjust the request like this:
const sheet = await sheets.spreadsheets.values.get({
spreadsheetId: SHEET_ID,
range: 'A1:Z1000',
valueRenderOption: 'FORMATTED_VALUE' // Values with formatting applied
});
Using "FORMATTED_VALUE" instead of the default "UNFORMATTED_VALUE" includes hidden cells and conditional formatting results.
Our Approach: A Hybrid Model
We ultimately chose to combine both methods.
1. Critical filters (priority, status) managed as separate text columns
2. Visual colors kept only for team convenience, independent of automation
3. Claude Code references only the text columns
This eliminated data loss while preserving visual clarity for the team.
The Lesson: Small Design Decisions Matter Early
As non-developers, this incident taught us something important: we naturally assumed "colors communicate information," but to a bot, that signal is entirely different.
Now whenever we build new automations, we ask: "Does this data exist only in formatting?"
If your automation faces similar issues, start by separating formatting information at the design stage. That small reorganization prevents confusion days later.