Google Sheets can pull a table off a web page with a single formula, which is genuinely better than any manual method when it works. It is worth ten minutes to learn where the boundary is, because the failure is silent and confusing: the formula returns #N/A and the message does not tell you which of four quite different things went wrong.
IMPORTHTML — a <table> or a list on a page:
=IMPORTHTML("https://example.com/prices", "table", 1)The second argument is "table" or "list"; "list" reads <ul> and <ol>. The third is which one, counting from 1 in document order. You cannot see the numbering in advance, so start at 1 and increase until the right thing appears.
IMPORTXML — anything you can point an XPath at:
=IMPORTXML("https://example.com/prices", "//table[@id='results']//tr")Use this when IMPORTHTML gives you the wrong table and you cannot find its index, or when what you want is not a table at all. //table[@id='results'] targets one table by id; //div[@class='price'] pulls a set of values.
IMPORTDATA — a CSV or TSV file at a URL:
=IMPORTDATA("https://example.com/export.csv")If the site has a download link that ends in .csv, use this one. It is the most reliable of the three by a wide margin, because there is no markup to guess at.
Put the formula in the top-left cell of an empty area. The result spills down and right, and it will not overwrite anything — if there is a value in the way you get #REF! and the note "Array result was not expanded".
Four causes, and they need four different responses.
1. The table is drawn by JavaScript. Google fetches the HTML the server sends, the same as curl would. It does not run the page's scripts. So any grid that appears after the page loads — most dashboards, most admin panels, anything built with React or Angular — is simply not in what Google fetched. This is the most common cause and there is no formula that gets around it.
To check: open the page, view source (Ctrl+U, not Inspect), and search the source for a value you can see in the table. Inspect shows you the live page after scripts; view-source shows you what Google gets. If the value is not in view-source, IMPORTHTML cannot reach it.
2. The page needs a login. Google fetches anonymously. Your session cookie is in your browser, not in Google's fetcher. A page behind a sign-in returns the sign-in page, which has no table on it.
3. The site blocks the fetcher. Cloudflare and similar services turn away requests that do not look like a browser. Google's fetcher does not. Some sites also disallow it in robots.txt, which Sheets respects.
4. The index is wrong. Less dramatic than the others: there is a table, just not the one you asked for. Try 1, 2, 3 in three cells at once and see which lands.
The distinction that matters is between cause 1 and the rest. Causes 2, 3 and 4 have workarounds. Cause 1 means you need something that reads the page after it has rendered, which means the browser you are already sitting in front of.
IMPORTHTML, IMPORTXML, IMPORTDATA and IMPORTFEED refresh roughly once an hour on their own. There is no refresh button and pressing F5 does not do it: the result is cached against the exact formula text.
The usual trick is to make the formula text change. Point the URL at a cell, and add a parameter the site will ignore:
A1: https://example.com/prices
B1: =IMPORTHTML(A1 & "?nocache=" & INT(NOW()*1440), "table", 1)INT(NOW()*1440) changes once a minute. Set the spreadsheet to recalculate on change and every minute under File, Settings, Calculation. Do not leave this running on a shared sheet — every recalculation is a real request to somebody's server.
The blunt version, for a one-off: cut the formula, press Enter, paste it back.
Import formulas are not free. A sheet with dozens of them gets slow, and past a point they start failing with a loading error that never resolves. If you are building something that needs many rows from many pages, import them once, then Paste special, Values only, and keep the data rather than the formula.
The result is live, which is not always what you want. If the page changes, your numbers change, including in a report you already sent. Freeze it with Paste special, values only, once you are happy.
Numbers may arrive as text. Currency symbols, percent signs and thousands separators come across literally. A right-aligned column is a number; a left-aligned one is text. Fix with:
=VALUE(REGEXREPLACE(A2, "[^0-9.\-]", ""))Locale decides the decimal separator. A sheet set to a European locale reads 1.5 as one and a half in some places and as 15 in others. Check File, Settings, Locale before you debug the data.
Merged cells in the source. A header with a rowspan shifts the columns under it. IMPORTHTML handles most of these correctly, but if the imported table looks one column out from the third row down, this is why.
If the table only exists after JavaScript runs, or it is behind a login, the data has to come out of your own browser session. Two ways:
Apps Script, if the page is public but awkward. UrlFetchApp.fetch(url) lets you set headers and follow redirects, and you can parse the response yourself. It still does not run the page's JavaScript, so it does not help with cause 1 above.
A browser extension, which reads the page you are looking at, after the scripts have run and while you are logged in. That is what GridPick does. Click the toolbar icon, pick the table, and either copy it — it goes to the clipboard as tab-separated text, so pasting into Sheets lands each value in its own cell — or save it as CSV and use File, Import, Upload. It reads div-based grids as well as real table markup, expands merged cells, and the free version has no row limit.
The trade is the obvious one: an extension gives you a snapshot, not a live formula. If the page is public and static, IMPORTHTML is the better tool and you should use it.
| Situation | Use |
|---|---|
| Public page, plain HTML table, want it to stay current | IMPORTHTML |
| Public page, need one specific table or non-table values | IMPORTXML |
| The site offers a CSV link | IMPORTDATA |
| Behind a login, or drawn by JavaScript | A browser extension |
| Needed on a schedule, unattended | Apps Script, or the site's API |
GridPick does this for you. It reads real tables and div-based grids, expands merged cells, warns you when a grid is only half loaded, and exports to Excel, CSV, JSON or Markdown. The free version has no row limit; Pro is a one-time payment.