Dropdowns Are the Schema: Letting an AI Write Rows Into Google Sheets A claims-log bot built on Claude has written 1,158 of the last 2,000 rows in a Google Sheets claims log since September, constrained by three guards that keep its output inside the sheet's dropdown lists, according to the operator's own account. A read-only check of the 2,000 live rows found the bot can write 34 of the 51 detail values the sheet allows, and the 17 missing values explain almost none of its mistakes. The guards drop any cause or detail not on the list, write "Needs check" for unrecognized results, and flag rows with a picked cause but dropped detail to a person via Slack rather than inventing a value. Google Sheets as a database https://botsinproduction.com/topics/sheets/ Dropdowns Are the Schema: Letting an AI Write Rows Into Google Sheets Three guards that keep an AI bot inside a sheet's dropdown lists, a read-only check of 2,000 live rows, and the 17 words the bot still cannot pick. Our claims sheet has a dropdown on every category column, and since September an AI bot has written most of its new rows 1,158 of the last 2,000 . This post is about the three guards that keep the bot inside those dropdowns, what a read-only check of the last 2,000 live rows found this morning, and the one problem the guards don’t catch: the sheet’s lists kept growing and the bot’s copy of them didn’t. The short version is that the dropdown is the schema, and the bot can write 34 of the 51 detail values the sheet allows . The surprise at the end is that those 17 missing words explain almost none of the bot’s mistakes. That part has a number too. Why a dropdown is a schema The sheet is a claims log: one row per customer complaint, written either by a person on the support team or by a bot that reads closed support chats https://botsinproduction.com/blog/turning-support-chats-into-a-claims-log-automatically/ . Three columns carry the category of each claim: | Column | What it holds | Size of the list | |---|---|---| | AE, cause | the broad reason out of stock, wrong delivery, quality, delivery handling, … | 7 | | AF, detail | a sub-type that depends on the cause | 2 to 15 per cause | | AH, result | what we did about it price deduction, exchange, return, cancel, … | 14 | Nobody reads these columns one row at a time. They feed filters, pivot tables and the monthly supplier review. A value that isn’t on the list doesn’t raise an error anywhere. It just becomes a category of one, invisible to every filter that was built from the list. If the bot writes “Product damaged” where the list says “Damaged product”, that row quietly drops out of the damage count. Nobody notices until somebody’s total is off and they spend an afternoon finding out why. For a person, the dropdown is a convenience. For a bot that writes with an API, it’s the only definition of a valid row that exists. Which is a slightly grand way of saying a column of words in a hidden tab is now load-bearing. Guard 1: put the list in the prompt The bot sends each closed chat to Claude and asks for one JSON object back. The allowed causes, details and results go into the prompt as a literal list, built from 9,490 existing rows on 3 September. A free-text answer like “the customer said the box was crushed” is no use to a dropdown, so the model is told to choose, not describe. A list alone wasn’t enough. By 22 September the bot had repeatedly filed “the product never arrived” under delivery handling , because the chats were full of delivery words “they left it in the hall” . The fix was a one-line rule next to the causes that kept getting mixed up: wrong delivery is for items that were missing or different, delivery handling only when the product did arrive. A list tells the model what words exist. It doesn’t tell it which one a human would pick. Guard 2: check every value on the way out The model is asked for list values, but nothing guarantees it will return them. So every category value goes through the same small function before it gets anywhere near the sheet: Three behaviours, on purpose: - Cause or detail not on the list: drop it. No fuzzy matching, no “closest word”. A wrong category that looks valid is worse than an empty one, because an empty one gets noticed. - Result not on the list: write “Needs check”. That value is on the sheet’s list, so the row still passes, and anyone can filter for it. - Cause picked but detail dropped: tell a person. The detail cell stays empty, and the bot’s Slack card for that row gets an “empty:” line naming the cause and listing the sub-types it could have been. The bot doesn’t invent one. There’s a version of this that “repairs” model output with string similarity. I didn’t build it. If Claude writes a word that isn’t on the list, the chat was usually ambiguous, and a person should look at it anyway. Guard 3: put the dropdown on the row the bot writes This one came out of a five-day gap in October. The detail column’s dropdown depends on the cause: pick Quality and AF offers the quality sub-types, pick Wrong delivery and it offers different ones. The onEdit script https://botsinproduction.com/blog/onedit-triggers-that-keep-two-columns-in-sync/ does this. When a person changes AE, it sets that row’s AF validation from a list tab. The old version did it with INDIRECT in every row, one of the formulas that made the whole sheet crawl https://botsinproduction.com/blog/volatile-formulas-killed-our-google-sheet/ . On 2 October the INDIRECT version was replaced by a rewritten onEdit script, and the dependent-dropdown part didn’t make it into the rewrite. For five days new rows got no dropdown in AF at all. Putting it back into onEdit fixed the rows people type. It could not fix the bot’s rows, because onEdit never fires for API writes . Without something else, a support person correcting a bot’s detail would find a plain text cell and type whatever came to mind. Since 7 October the bot sets the validation itself, one extra API call per row: Both the Apps Script and the Python read the same list tab, so the two can’t disagree about what a valid detail is. One thing I am not relying on: the strict flag stopping the bot itself. Strict validation rejects a bad value typed by a person. Whether it rejects a bad value sent through the API is something I haven’t tested, and I’m not going to test it on the production sheet. I tried to make a scratch spreadsheet with the bot’s service account this morning and got a 403, the same wall as before https://botsinproduction.com/blog/google-sheets-api-service-account-limits-i-hit/ . So Guard 2 runs before every write, as if the dropdown weren’t there. What the live sheet says today I ran a read-only probe against the last 2,000 rows this morning. It reads the validation rules and values, compares them with the bot’s code, and prints counts. It writes nothing. The good news first: - Every one of the 2,000 rows has strict validation on cause and result. All but one have it on detail. The exception is a single bot row whose apply detail call evidently failed. The row was still written, which is the intended failure mode. - One value in 2,000 sits outside the sheet’s own lists. It’s on a human row: a detail that belongs under a different cause. With strict validation that’s hard to type, so my guess is it was pasted, or the cause was changed later by something that doesn’t fire onEdit. I can’t tell which from the sheet. - The bot wrote 1,158 of the 2,000 rows. Not one of them has a cause or detail outside the lists. And the news this post is really about, in the yellow lines: the list tab has 17 detail values and 2 results that the bot’s code doesn’t have . The other direction is empty. The bot knows nothing the sheet doesn’t. The drift: 34 of 51 The comment above the vocabulary in the code says it was taken from the sheet on 3 September and “matches the dropdowns”. Today it doesn’t, and I can’t tell you exactly when it stopped. The list tab lives in the sheet, not in git, so it has no history I can read. The tab grew after September, or the 9,490-row count missed words nobody had used yet, or both. What git does show is the bot catching up twice, both times after a person corrected it: - 13 October: Support error / wrong advice was added to the bot’s list. The commit message says it had been in the dropdown all along. - 16 October: Expiry was added under Quality , two days after the support team changed the bot’s “quality drop” to “expiry” twice in one day. Both fixes were one line, and both were found by a person getting annoyed. Nothing found them automatically. The clearest picture of the gap is the Quality cause. The sheet offers seven sub-types for it. The bot’s prompt offers three. The bot puts 285 of its 295 quality claims in one bucket. People spread theirs over six. If you were hoping to read “how many complaints were about defects vs size” off this column, the bot has made that impossible for most of the rows it wrote. Nothing is wrong in those rows. Every value is valid. They’re just less useful than they could be, and validation can’t see that. Do the missing words cause the mistakes? This is the part I expected to be the headline, and it isn’t. Since 1 October a small job has recorded every cell a person changes in a bot-written row: the bot’s value, the person’s value, which column. Here is what it holds for the category columns, over 1,495 bot rows: - Cause was changed 58 times 3.9% of bot rows , detail 75 times 5.0% , result 195 times 13.0% . - In 7 of those 328 changes the person picked a word that isn’t in the bot’s vocabulary. That’s 2.1%. In those 7 cases the bot couldn’t have been right. In the other 321 it could have, and wasn’t. - The most common changes are judgment calls between two valid words: price deduction to cancel 22 times , return to price deduction 13 , wrong delivery to other 14 . Those are questions about what the support team actually did, and a dropdown can’t answer them. So the dropdown does its job, which is to protect the shape of the data. Nothing outside the lists has gone in. Whether the meaning is right is the job of the prompt rules, the approval step in Slack, and that correction log, which feeds a review every night. The result column has the most corrections because it’s the least visible in a chat: customers describe the problem, but rarely say how it was settled. What I’d change, and haven’t yet Being honest about the state of things: the drift is still there as I write this. The fix I have in mind is small, and I haven’t built it: 1. Read the list tab when the bot starts and compare it with the code. If the sheet has a value the code doesn’t, post one line to my DM. The bot already keeps a copy of that tab for Guard 3, refreshed hourly, so this costs almost nothing. 2. Don’t load the prompt list from the tab automatically. It’s tempting, but a new value means a new choice for the model, and the new choice needs its own one-line hint see Guard 1 . Adding a word should be a deliberate change with a test case, not a side effect of someone editing a hidden tab. 3. Run the probe from this post weekly , so “one value outside the lists” becomes a number with a trend instead of something I found on a Monday. The rules, short 1. Treat the dropdown lists as the schema. Write them into the prompt as literal values, with a one-line rule wherever two values get confused. 2. Check every category value in code before writing. Drop what isn’t on the list, never fuzzy-match, and hand the row to a person. 3. API writes don’t fire onEdit. If the dropdown is set by a trigger, your bot must set it on its own rows. 4. Keep one list source, and compare it with the bot’s copy on a timer. The sheet will grow. Nobody will tell the bot. 5. Measure corrections per column. Validation tells you the data is well-formed. Only people changing cells tell you it’s right. One line from me Two thousand rows, one value off the lists, and the bot still files 96.6% of its quality claims under one word, because nobody told it there were six others. The table layout these columns sit in is in Google Sheets as a bot database https://botsinproduction.com/blog/google-sheets-as-a-database-for-bots-cell-limit/ , and the onEdit side of the same sheet is in the two-column sync post https://botsinproduction.com/blog/onedit-triggers-that-keep-two-columns-in-sync/ . More on this topic: the Google Sheets as a database guide https://botsinproduction.com/topics/sheets/ , with every post in reading order.