Outgrown Google Sheets? Count the Slow Formulas Before You Decide

Our guide to outgrowing Google Sheets lists six signs that a business needs a real system. Sign 3 is the one people reach for first: the file is slow, scripts time out, a filter takes eight seconds. It is also the easiest one to get wrong, because "the sheet feels slow" is an opinion, and an opinion is a weak reason to start a custom software project. The guide's own advice is to fix the sheet first, because the soft limits arrive long before the hard ones. So before anyone books a scoping call, I wanted a number. I wrote one Apps Script function that reads every formula in the file, counts the patterns that make a sheet slow, and writes the result to a tab called Sheet health. While testing it, I also ran the guide's Sign 1 code (the locked recordSale() function) against a fake sheet, and three ordinary inputs got through that should not have. Why "it feels slow" is not enough There are two ways a sheet hits a wall, and they need different answers. The hard limits are published. Google's Drive limits page puts a spreadsheet at up to 20 million cells. One Apps Script run stops at 6 minutes (our 6-minute limit guide covers the workarounds). Those numbers almost never decide anything for a small business. The soft limits are the formulas. Google's own help page on Sheets performance names three patterns: Volatile functions. In Google's words, "TODAY(), RAND(), RANDBETWEEN(), and NOW() are volatile functions". Google says they refresh frequently to stay up to date, so every cell that uses one keeps recalculating. Open ranges. Google's example is SUM(A:A), which makes Sheets read every row in column A, against SUM(A1:A10), which reads ten. IMPORTRANGE. Each one fetches data from another file, so the page describes it as a round trip. Our guide also lists INDIRECT() as volatile. Google's page does not. I count INDIRECT and OFFSET in their own column, so the report does not claim more than Google says. None of this tells you whether a sheet has outgrown its shape. It tells you whether a slow sheet is slow because of how it is built. That is the question you need to answer before Sign 3 can count. The scanner: one formula at a time The first block is plain JavaScript with no Google services, so all of it can be tested outside Sheets. scanFormula() takes one formula string and returns five counts. // Names Google lists as volatile on its performance help page. const VOLATILE = ['NOW', 'TODAY', 'RAND', 'RANDBETWEEN']; // Not on Google's list, but they re-read whatever they point at. const INDIRECT_LIKE = ['INDIRECT', 'OFFSET']; // Blank out "string literals" and 'quoted sheet names' so a // formula like ="Call at 10:30" or 'A:A notes'!B2 is not misread. function stripLiterals(f) { return String(f) .replace(/"(?:[^"]|"")*"/g, '""') .replace(/'(?:[^']|'')*'!/g, "S!"); } function countCalls(f, names) { const re = new RegExp( '(?