Google Sheets Web Scraping: IMPORTXML and Beyond

Every Google Sheets web scraping walkthrough demos IMPORTXML on books.toscrape.com and stops there. Your real targets are React apps, Cloudflare walls, and tables that move. This morning I fetched the source HTML of both kinds of page, the exact bytes Sheets’ import functions see, and that difference decides which method you get to use.

Below: working formulas for single values, lists, and whole tables; the refresh and quota rules most articles skip; Apps Script for when formulas can’t; and a clean exit line for when Sheets is the wrong tool.

The four import functions, one job each

Function	What it grabs	What it can’t
IMPORTXML	Any XML/HTML node you can address with XPath	JavaScript, sites that block Google’s fetchers, heavy use (throttling)
IMPORTHTML	A <table> or <ul>/<ol> list, by its position index	JS-rendered tables; tables whose index shifts between loads
IMPORTDATA	A CSV or TSV file at a public URL	Anything not already comma- or tab-separated
IMPORTFEED	RSS or Atom feed entries	Feeds behind a login

All four share the same plumbing, and the plumbing is the story. Google’s help page says they check for updates once per hour while the document is open, throttle you across every document you own, and refuse to run if the formula references NOW(), RAND(), RANDARRAY(), or RANDBETWEEN(). Refreshing your browser tab does nothing.

Before you start

A Google account, 15 minutes, and one reflex to learn: check the page before writing any formula. The first time an import formula runs, Sheets also shows an “Allow access” banner, because the sheet can now send and receive data to external URLs. Editors of the sheet must click it once.

One boundary: all of this is for reading public pages politely. Respect robots.txt, keep request counts sane, and don’t collect personal data into spreadsheets without a lawful reason.

Step 1: Check the page actually contains your data (30 seconds)

Open the target page, press Ctrl+U (Cmd+Option+U on macOS), and search the source for a string you want to pull. If you find it, a formula route will work. If not, the content is built by JavaScript after the fetch, and no import function will ever see it.

That’s the whole test. IMPORTXML, IMPORTHTML, IMPORTDATA, and IMPORTFEED all fetch raw HTML. No browser, no rendering.

Failure case: you find the string but it sits inside a <script> tag as JSON. XPath can sometimes address it (//script[contains(., 'product')]), but parsing JSON inside a cell is miserable. That’s Apps Script territory (Step 5) or a dedicated scraper.

Step 2: Pull single values, lists, and blocks with IMPORTXML

Three patterns cover most jobs. I’ll use a Books to Scrape category page (verified 2026-10-16: the page serves 11 book cards as plain HTML).

Single value, the page heading:

=IMPORTXML("https://books.toscrape.com/catalogue/category/books/travel_2/index.html", "//h1")

One cell comes back: Travel.

A list, every price on the page:

=IMPORTXML("https://books.toscrape.com/catalogue/category/books/travel_2/index.html", "//p[@class='price_color']")

An 11-row column: £45.17, £49.43, £48.87, and so on. (Output shown is the first three rows as served on the fetch date; abridged.)

Whole blocks, one row per product card:

=IMPORTXML("https://books.toscrape.com/catalogue/category/books/travel_2/index.html", "//article")

Each card is an <article class="product_pod"> holding a title link, a price, and stock text. Sheets returns one row per card and spreads the inner text across columns, so titles and prices land side by side with some junk between them. Cleanup is a SUBSTITUTE/VALUE pass, not a rebuild.

XPath tip that saves hours: don’t iterate inside Sheets. Open DevTools on the target page and run $x("//article") in the console; Chrome evaluates the same XPath the formula will, in milliseconds. Paste the winner into the cell.

Failure case: #N/A with “Imported content is empty” means the XPath matched nothing in the served HTML. Nine times out of ten the path is fine and the node only exists after JavaScript runs. Back to Step 1.

Step 3: Pull whole tables with IMPORTHTML

=IMPORTHTML("https://www.w3schools.com/html/html_tables.asp", "table", 1)

This returns the Company / Contact / Country demo table. The 1 means “the first <table> in the document”; I verified that page has two tables and the demo is the first. Swap "table" for "list" to grab <ul>/<ol> elements by the same index logic.

The index is the weak point. A CMS update that inserts one new table above yours silently repoints the formula at the wrong table, or returns #N/A. For tables you care about past a one-off, a method that targets the table directly instead of by counting holds up better; the guide on pulling tables from pages covers those.

Step 4: Know how it breaks before it does

Three failure modes, ordered by how often they bite.

JavaScript-rendered content. Books to Scrape has a sister sandbox, Quotes to Scrape, with a static version and a /js/ twin that renders the same quotes with JavaScript. I fetched both today: the static page serves 10 quote blocks in its HTML, the /js/ twin serves zero, and every quote is injected after the fetch. IMPORTXML with //div[@class='quote'] on the /js/ page therefore finds nothing. There is no formula fix, and Apps Script doesn’t help either. You need a tool with a real browser; the guide to scraping dynamic sites covers that path.

Blocks and challenges. Sheets fetches from Google’s infrastructure with a client signature you can’t change: no custom User-Agent, no cookies, no proxy. Sites with bot protection see the fetch for what it is and answer with a 403 or a challenge page, which the formula reports as #N/A or renders as garbage rows. Quick test: if the page shows a challenge in an incognito window, skip Sheets entirely. Proxies are a separate discipline; see when you actually need proxies.

Silent staleness. The hourly refresh runs only while the document is open somewhere, and a collaborator’s open tab counts. Reopening a closed sheet does not force new fetches; Google’s help page is explicit about that. To force a refresh, delete the formula and re-add it, or overwrite the cell with the identical formula. Two ceilings sit above all of it: Google’s guidance has long been to keep to about 50 IMPORTXML functions per spreadsheet, and heavy importing across many documents earns the “Error Loading data may take a while because of the large number of requests” throttle, where an edit anywhere can queue every import you own.

Step 5: Beyond formulas — Apps Script scraping

Formulas can’t set headers, loop through pages, or run on a schedule you choose. Apps Script can, and it’s already inside Sheets under Extensions → Apps Script:

function pullPrices() {
  const url = 'https://books.toscrape.com/catalogue/category/books/travel_2/index.html';
  const res = UrlFetchApp.fetch(url, { muteHttpExceptions: true });
  if (res.getResponseCode() !== 200) {
    throw new Error('HTTP ' + res.getResponseCode());
  }
  const html = res.getContentText();
  const rows = [...html.matchAll(/<p class="price_color">([^<]+)<\/p>/g)]
    .map(m => [new Date(), m[1]]);
  const sheet = SpreadsheetApp.getActive().getSheetByName('prices');
  sheet.getRange(sheet.getLastRow() + 1, 1, rows.length, 2).setValues(rows);
}

function makeHourlyTrigger() {
  ScriptApp.newTrigger('pullPrices').timeBased().everyHours(1).create();
}

Create a prices sheet, run makeHourlyTrigger once, and pullPrices appends a timestamped price column every hour whether the sheet is open or not. That alone fixes the staleness problem formulas can’t.

The budget, straight from Google’s Apps Script quota page (checked 2026-10-16):

Limit	Free Gmail account	Google Workspace
URL Fetch calls	20,000/day	100,000/day
Script runtime	6 min per execution	6 min per execution
Trigger runtime total	90 min/day	6 hr/day
Max response size	50 MB per call	50 MB per call

20,000 fetches a day is roughly one page per minute around the clock. Most price-watch and API-import jobs fit comfortably.

Three walls remain, and vendor posts about “scraping with Apps Script” tend to skip them:

• No browser. UrlFetchApp fetches bytes; it doesn’t execute JavaScript. A React page hands you the same empty shell IMPORTXML gets.
• Google strips identity headers. You cannot set User-Agent, Cookie, or Referer, so you can’t impersonate a normal browser or replay a login session. Scraping pages behind a login from Apps Script is effectively closed; the guide to scraping sites with a login covers tools that can.
• Custom functions can’t fetch. You can’t wrap this in =PULL() and call it from a cell; custom functions may not use services that require authorization. Fetches run from a menu item, a trigger, or the editor.

And the honest cost note: Apps Script scraping is code. The 10 lines above stay manageable, but pagination, retries, and HTML parsing grow fast. Fine as glue between Sheets and an API. Not a scraper framework.

When Google Sheets web scraping stops being enough

Trade up when any of these is true: more than ~50 formula imports, any JavaScript-rendered target, login walls, sub-hourly freshness, or data volume heading toward Sheets’ 10-million-cell limit.

The deciding question is which side the difficulty sits on. If the data must live-link into a sheet, Sheets is the destination and the constraint: pick an add-on connector and accept its ceiling, because the connector inherits the same fetch limits under its own quota. If the hard part is getting the data at all, flip the order: scrape with a real tool first, then import.

That’s the honest place for Magpie in this article. It’s a local-first scraper: a single Go binary (AGPL-3.0-or-later or a commercial license; free to download, 5 free runs per install, then $49/month). Point it at a page and you get clean markdown, where tables come out as markdown tables, or schema-driven JSON/CSV through its AI extraction with your own LLM key.

What it does not do, stated plainly: no .xlsx export, and no Sheets connection. You export CSV or JSON and bring it in with Sheets’ File → Import. If the destination must be a live-linked spreadsheet, that’s a dealbreaker.

If the problem is the scraping itself (JS pages, sessions, selectors that break on redesign), it’s the shorter road, and the guide to getting website data as CSV or JSON shows the export route end to end. (Excel has its own routes; the Excel import guide covers that side.) All of this is indexed in the web scraping tutorial hub.

FAQ

Is pulling website data into Google Sheets legal? Reading public data isn’t illegal by itself; the real constraints are the site’s terms, robots.txt, and data-protection law for anything personal. Don’t republish what you collect, and don’t scrape Google’s own results; their terms forbid it.

Why does IMPORTXML return #N/A or “Imported content is empty”? The XPath matched nothing in the served HTML. Most likely order: the content is JavaScript-rendered, the site blocked Google’s fetch, or your IMPORTHTML table index shifted. Re-run the 30-second view-source test from Step 1.

How often do the import functions refresh? Once per hour, and only while the document is open. Reopening a closed sheet doesn’t force fresh fetches. Delete and re-add the formula to force one immediately.

Can IMPORTXML or Apps Script render JavaScript pages? No. Both fetch raw HTML and never run the page’s scripts. You need a headless-browser tool (a Playwright-class library) or a scraper with a built-in browser.

Is there a limit on how many import formulas I can use? Google recommends about 50 IMPORTXML functions per spreadsheet. Past that you hit throttling across your documents and “Result too large” errors when a single query returns too much.

Where to go next

Try them in order: IMPORTHTML for a static table, IMPORTXML for anything the view-source test can see, Apps Script when freshness or headers matter, a real scraper the moment JavaScript or a login appears. The 30-second check in Step 1 answers the only question that matters before any of it: is the data even in the HTML?