Google Sheets remains the most practical place to build a client-facing SEO report, mostly because clients already know how to use it. The trouble starts when the sheet stops being a report and starts being a system.
The pattern that works
Keep three kinds of tab and never mix them:
- Raw — written by automation, never touched by a human, never formatted
- Working — formulas that shape raw into the numbers you need
- Report — what the client sees, referencing working tabs only
When someone reformats the report tab, nothing breaks. When the data refreshes, the report updates. Most broken reporting sheets fail because formatting and data live in the same cells.
Where Apps Script belongs, and where it does not
Apps Script is good at moving data in on a schedule. It is poor at being the place your business logic lives, because it is invisible in the sheet, hard to review, and silently fails on a trigger nobody is watching.
Fetch with script; calculate with formulas. If a number in the report cannot be traced by clicking through cells, someone will eventually distrust the whole sheet.
Quotas and failure
Scheduled triggers fail. The API rate-limits, the token expires, the sheet hits its cell limit. Build for that: write a timestamp on every refresh, and have the report tab show plainly when the data was last updated. A stale report that admits it is stale is far less damaging than one that looks current.
Know the ceiling
Sheets slows badly past roughly a hundred thousand rows with live formulas across them. If your raw tab is growing every day and never being pruned, you are building toward a wall. Aggregate on write, keep only what the report needs, and archive the rest somewhere that is designed to hold it.