Google Sheets formulas vs Apps Script: when to use which
Both formulas and Apps Script can transform your spreadsheet. Here's a practical rule for when each is the right tool — and when a formula is secretly a liability.
The honest rule of thumb
Formula: value changes based on what's already in the sheet. Apps Script: something needs to happen outside the sheet — email, file, message, approval, or an action that's not in a cell.
If your answer is 'sum the sales for the last 30 days', that's a formula. If it's 'email the owner once the sale clears 50,000 rupees', that's a script. Everything below is detail on that core rule.
When formulas are clearly better
- Live calculation — totals, ratios, ageing, lookups that recompute as data changes.
- Formatting and conditional colouring — data bars, highlights, colour scales.
- Data validation — dropdowns and input rules that keep your sheet clean.
- Transparency — a Formula: QUERY or a simple SUM that anyone can read and edit.
When Apps Script wins
- Sending anything — emails, WhatsApp, notifications, documents.
- Creating files — invoices, PDFs, contracts, folder structures.
- Reacting to time — daily/weekly/monthly jobs ('every Monday at 9').
- Reacting to changes — 'when this cell changes, do that'.
- Talking to other systems — APIs, CRMs, payment gateways, databases.
- Cleaning messy data — scaling, trimming, deduping across tens of thousands of rows whose formats are all over the place.
The formula that's secretly a liability
There's a class of 'viral formulas' — the ten-line QUERY/ARRAYFORMULA/IMPORTXML monster that one person built and nobody can edit. They break when a column moves, run slower every month, and become the least understood part of your business.
If a formula is (a) longer than a paragraph, (b) critical to money, and (c) only understood by one person, ask whether a script (or a cleaner structure) should replace it. Code can be tested, documented, and handed over; a monster formula can't.
Using both, deliberately
The best spreadsheets use formulas for the visible, live layer and scripts for the 'do things' layer. Screenshot-ready dashboard? Formulas. Weekend data pipeline that fills it? Script. Learn to label each layer and you'll stop fighting Google Sheets and start using it.
For the automation layer, that's where Google Apps Script development comes in — we build, test, and document the script side so your formulas keep working on good data.