Scraping a Website into Google Sheets: What Works, What Breaks, What Comes Next

Google Sheets offers functions that pull data from a web page with no code, and they work well for a narrow case: a table visible in static HTML. They break on modern sites for one clear reason, and it is worth understanding before blaming the formula.

The built-in functions and what each does

The common mistake is trying IMPORTHTML on something that looks like a table but is built from styled div elements. To the browser it is a table; to the document there is no <table>, so the function finds nothing.

Why this breaks on modern sites

There is one central reason: these functions request the HTML file from the server and parse it as it arrives. They are not a browser - they do not run scripts and do not wait for further requests.

On a site where content arrives after page load - a shop, a listings board, a dashboard - the initial HTML contains an empty shell. What you see in the sheet is #N/A or empty cells, while in the browser everything looks fine. That is not a formula fault but a fundamental difference between two things.

The check that confirms it in ten seconds: turn JavaScript off in the browser and reload. What remains on screen is roughly what the sheet will see.

Three further problems you meet along the way

• Caching - values do not refresh immediately, and the refresh is not fully under your control.
• Usage limits - a sheet with dozens of live import formulas gets slow, and some return errors intermittently.
• Structure changes - IMPORTXML depends on a path inside the document, and any redesign at the source drops it with no warning.

The second surprises the most users. A sheet that worked beautifully with five formulas starts behaving unpredictably with forty, and the symptom - values disappearing and reappearing - looks entirely random. It is also the clearest hint that the sheet has moved from being a destination to being a collection tool.

What Apps Script adds, and what it does not

When the built-in functions are not enough but the source is still static, there is a middle step that does not require leaving the spreadsheet at all.

Apps Script lets you write a function that fetches a page and processes it in code, and run it on a schedule. That solves three things: control over what is extracted, processing before writing to the sheet, and scheduling that does not depend on the file being open.

What it does not solve: it still fetches HTML rather than running the page. A site requiring rendering stays out of reach, as does one with a login or a human challenge. In other words, Apps Script widens the range within the same category - it does not move you to the next one.

How to build the sheet so it does not fall apart

Even when the case suits the built-in functions perfectly, the structure of the sheet itself decides whether it lasts six months:

• A separate raw tab - every import formula lives on one tab that nobody edits by hand.
• A separate working tab - calculations happen there, so a refresh cannot overwrite work.
• A timestamp - one cell recording when the import last ran successfully.
• A control cell - a count of rows that arrived, so you see immediately when it collapses.

The second point breaks fastest. When somebody adds a notes column beside imported data, the next import pushes the data down and shifts the notes by a row - and nobody notices until the notes no longer match the rows they describe.

When a spreadsheet stops being the right place

A sheet is an excellent destination: people know how to work in it, it is shareable, and it is enough for most needs. What it is not is a database. Four signs the crossover has arrived:

• The sheet has grown until it opens slowly.
• You need history - not only the current state but what changed and when.
• More than one system reads from it.
• Somebody has to check every morning that the data refreshed.

The third sign is the critical one. Once the sheet feeds an automation or a dashboard, a temporary import error is not a visual nuisance but wrong data flowing onward - which is exactly what justifies a structure with monitoring, as described in why web scrapers break.

The usual path then is a scraper running separately that writes into the sheet - the sheet stays the familiar interface, and collection moves somewhere it can be monitored. The dynamic-page case is covered in scraping a dynamic page into a spreadsheet.

What to check before starting

• That the content exists in the HTML without JavaScript.
• That there is a real <table> or list, if you are using IMPORTHTML.
• That the URL is public and needs no login.
• That the frequency you need is compatible with a refresh that is not immediate.
• That the number of formulas in the sheet will stay small.

Five minutes of checking saves the hour spent working out why the formula returns #N/A - which is almost never about the formula itself.

Sources

• Google Sheets - import function help
• Google Apps Script
• Scraping a dynamic page into a spreadsheet
• Why web scrapers break
• Book a call