Topic guide
▦ Google Sheets as a database for bots
Our claims log is a Google Sheet. The support team reads it and fixes it all day, the bots write to it, and the monthly settlement reads it at the end. That is a lot of jobs for a spreadsheet, and these posts are what we learned keeping it fast and correct.
Start with the database post: the audit that found the sheet at 85.5% of Google's cell limit and the layout rule that followed. The volatile-formulas post is the 20,320 formulas that froze it, and a fresh audit of the old and new files. The onEdit post is about two columns that must always agree, and the Undo key that breaks them. The service-account post is the five walls a bot hits when it writes to a sheet it doesn't own.
85.5%of the cell limit, before
20,320 → 2volatile formulas
1,734 / 1,735id and name pairs agree today
Read them in this order
Google Sheets as a Bot Database: What Broke at 85% of the Cell LimitOur bots wrote to one Google Sheet for a year. It hit 85.5% of the cell limit and 20,000 volatile formulas. The audit, what we kept, and the fix.
Volatile Formulas Killed Our Google Sheet: The Audit Found 20,000Our claims sheet had 20,320 volatile formulas and sat at 85.5% of the cell limit. How we counted them, what replaced them, and a re-run of the audit today.
onEdit Triggers That Keep Two Columns in Sync (and the Undo Bug)Two column pairs in our claims sheet must always match. onEdit fills them, Undo breaks them, a one-minute sweep repairs them. With the real numbers.
Writing to Google Sheets From a Bot: 5 Service-Account Walls I HitA bot writes to our claims sheet through a service account. Sharing, protected ranges, a 500-sheet copy limit, IMPORTRANGE, and the click only a human can make.