Google Sheets Web Scraping: IMPORTXML and Beyond

Drop =IMPORTHTML("https://en.wikipedia.org/wiki/Demographics_of_India","table",4) into an empty cell. A second or two later a Wikipedia table is sitting in your sheet, spilled across the columns to the right. Nothing was installed, no API key was issued, and the request never touched your machine. A Google server made it, from a Google IP address, announcing itself in the target's access log as Mozilla/5.0 (compatible; GoogleDocs; apps-spreadsheets; +http://docs.google.com).

That single line is the appeal of Google Sheets as a scraper, and it is also the entire problem. You did not choose the address the request came from. You cannot change the user agent. No cookie is carried, no JavaScript runs, and the answer in the cell may be up to an hour old. Almost everything else in this article follows from those five facts.

If web scraping with Excel / VBA is the desktop route, where a macro on your own computer pulls data into the workbook, Sheets is the opposite bargain: hand over control of the client, get scheduling, sharing and zero setup back. The formulas cover more ground than most people expect. Google Apps Script, a JavaScript runtime on Google's servers, covers the rest and hits a different set of walls. Every number, quota and quotation below was read from Google's and Microsoft's own documentation on 13 August 2026.

What actually happens when a cell fetches a page

The IMPORT... family does not run in your browser. Your browser sends the formula to Google, a Google fetcher retrieves the URL, and the parsed result comes back to the cell. Four consequences follow, and they explain most of the confusion around these functions.

The request carries a fixed identity. Server logs from sites scraped this way show the user agent quoted above, unchanged for years, and Google does not document it. The company's page on user-triggered fetchers lists Feedfetcher, Google Read Aloud and Google Site Verifier, with the note that "Because the fetch was requested by a user, these fetchers generally ignore robots.txt rules." Sheets is not on that list, so whether the spreadsheet fetcher reads robots.txt is not knowable from outside.

You share a reputation with every other spreadsheet on the planet. A site that decided Google's Docs ranges are a nuisance blocks you before you type the formula. That is the most common reason an IMPORTXML which should obviously work returns #N/A while the same URL opens fine in your browser.

The result is cached, and the cache is not short. Google's help page on how spreadsheet data is calculated gives the intervals plainly: IMPORTRANGE refreshes every "30 minutes", and IMPORTHTML, IMPORTFEED, IMPORTDATA and IMPORTXML every "1 hour". GOOGLEFINANCE prices are "delayed up to 20 minutes". If your use case is a dashboard that has to be right to the minute, stop here and go build a pipeline.

No JavaScript executes. What arrives is the HTML the server sent, not the DOM a browser would build. That is a harder boundary than the others, and it deserves its own section below.

How Google Sheets differs from Excel / VBA

One correction against the older comparisons, this article's own earlier version included. Excel is not restricted to Power Query for code-free extraction. WEBSERVICE pulls a URL into a cell, FILTERXML runs an XPath expression over the result, and Microsoft has shipped both since Excel 2013. Chain them and you have a two-step IMPORTXML. The catch is platform, not capability: Microsoft's own page states that FILTERXML "is not available in Excel for the web and Excel for Mac". Windows Excel has formula scraping. The rest of the family does not.

For light and medium jobs Sheets is still faster, because half the work is one formula. For heavy or awkward ones the logic carries over from VBA and only the syntax moves.

A note on syntax. These examples separate arguments with commas, which is what Sheets expects in the US and UK locales. If your spreadsheet locale uses a comma as the decimal separator, Sheets switches the argument separator to a semicolon. Adjust accordingly.

Level 1. Scraping with formulas

IMPORTHTML - tables and lists

The simplest function. It pulls an entire table or list by ordinal position:

=IMPORTHTML("https://example.com/page", "table", 1)

Three arguments: the URL, the element type ("table" or "list"), and the index. One detail from Google's own documentation saves an afternoon: "The indices for lists and tables are maintained separately, so there may be both a list and a table with index 1 if both types of elements exist on the HTML page." Counting <table> tags in view-source and getting a list back is a normal Tuesday.

Finding the right index is a search, not a lookup. Put the index in its own cell, reference it, and step through the candidates one at a time. Nested tables inflate the count badly on older markup, where a layout table wraps the one you actually want.

IMPORTXML - targeted extraction with XPath

The workhorse. Google documents the signature as IMPORTXML(url, xpath_query, locale), where the optional third argument is "A language and region locale code to use when parsing the data" and defaults to the document locale. It matters more than it looks on a page that uses a comma decimal separator.

=IMPORTXML("https://example.com", "//h1")
=IMPORTXML("https://example.com", "//div[@class='price']")
=IMPORTXML("https://example.com", "//span[@id='total']/text()")

The query language is XPath 1.0, a W3C Recommendation since 16 November 1999. Patterns that earn their keep:

The exact-match trap accounts for more failed formulas than everything else combined. //div[@class='price'] compares the whole attribute string. Markup that reads class="price price--sale" does not match, and Sheets reports nothing rather than explaining why. Reach for contains(@class,'price') by default and tighten only when it over-matches.

The other trap arrives free with DevTools. Copy XPath hands you an absolute path such as /html/body/div[3]/div[2]/section/div[1]/span, correct for exactly as long as nobody touches the template. Shorten it to the nearest stable id or attribute. Our primer on XPath for scraping covers the syntax in more depth.

IMPORTDATA - CSV and TSV

If the source already serves a CSV or TSV file, skip the parsing entirely:

=IMPORTDATA("https://example.com/data.csv")

Values land split across columns. Government open-data portals, exports and any URL that answers with text/csv are the natural fit. It respects the same one-hour cache as the rest.

IMPORTFEED - RSS and Atom

=IMPORTFEED("https://example.com/rss")

Google documents it as IMPORTFEED(url, [query], [headers], [num_items]). The query argument takes "items" or "feed" and switches between the entries and the feed metadata, headers adds a header row, and num_items caps the result. Check for a feed before you scrape the HTML index page next to it. Always.

GOOGLEFINANCE - finance with no scraping at all

GOOGLEFINANCE is the case where the right answer is to scrape nothing.

=GOOGLEFINANCE("NASDAQ:AAPL", "price")
=GOOGLEFINANCE("CURRENCY:USDEUR")
=GOOGLEFINANCE("NASDAQ:GOOGL", "price", DATE(2024,1,1), DATE(2024,12,31), "DAILY")

Current price, a currency pair, then a historical daily series. Real-time attributes run from priceopen and volume through marketcap, pe, eps, high52 and beta; mutual funds get their own set, including returnytd, netassets, expenseratio and morningstarrating.

Four constraints Google states outright and almost nobody repeats. The price is "delayed by up to 20 minutes", and the datadelay attribute reports the delay for a specific ticker rather than leaving you to guess. Coverage is narrower than the marketing suggests: "GOOGLEFINANCE is only available in English and does not support most international exchanges." Reuters instrument codes stopped working, so use an exchange prefix such as TSE or ASX. And the licence line matters before you build a desk tool on it: the data "is not for financial industry professional use or use by other professionals at non-financial firms (including government entities)."

Where it fits, it beats any scrape. No blocks, no markup changes, no XPath to maintain.

Getting data out of a page that renders in the browser

Sheets does not execute JavaScript, which sounds like the end of the story for anything built on React, Vue or Next.js. Often it is not. A large share of those pages ship their data twice: once as JSON embedded in the HTML so the framework can hydrate, and once as the DOM that only exists after the script runs. The first copy is plain text in the response body, and IMPORTXML reaches plain text. Three places to look, in the order that pays off:

• JSON-LD. Blocks of <script type="application/ld+json"> carry JSON-LD 1.1, a W3C Recommendation since 16 July 2020, and they exist because search engines read them. On commerce and publishing sites they routinely hold product name, price, availability and review count. Grab the whole block with =IMPORTXML(A2, "//script[@type='application/ld+json']").
• Open Graph and Twitter card meta tags. Title, description, image, occasionally price. Always in the head, always static, reachable with //meta[@property='og:title']/@content.
• Framework state blobs. Next.js emits <script id="__NEXT_DATA__" type="application/json">, which //script[@id='__NEXT_DATA__'] picks up whole. Nuxt assigns to window.__NUXT__ inside an anonymous script, so you get the tag and cut into it by hand.

Sheets has no JSON parser, so step two is string surgery with REGEXEXTRACT. Quotes inside a formula string are doubled, which makes the pattern look worse than it is:

=REGEXEXTRACT(A5, """price"":\s*""?([\d.]+)")

That is "price":\s*"?([\d.]+) once Sheets has unescaped it. Sheets regex uses RE2 syntax, so there is no lookahead and no backreference. Anything needing those needs Apps Script.

If the number genuinely only exists after the script runs, nothing in Sheets will get it. That is where a browser becomes mandatory, and neither level of Sheets has one.

When the import comes back empty

Four symptoms, four different causes, and guessing between them wastes hours.

Loading... that never clears usually means too many import formulas competing in one document, or a slow origin. Cut the number of live imports.

#REF! is not a fetch problem at all. The result needs room to spill and something is already sitting in the target range.

#N/A means Google fetched something and found nothing at your XPath. Diagnose it with the cheapest possible query first:

=IMPORTXML(A2, "//title")

If //title comes back empty, the problem is the fetch and not the expression. If it returns the page title and your real query still fails, the expression is at fault, and the exact-match trap above is the first suspect.

A page that renders perfectly in your browser and returns nothing in the sheet means the site answered Google differently than it answered you. Check from a terminal with curl -s -A 'Mozilla/5.0 (compatible; GoogleDocs; apps-spreadsheets; +http://docs.google.com)' URL | head -40. If that comes back as a challenge page, no formula will fix it.

One more trick worth knowing and worth distrusting. The one-hour cache is keyed on the URL string, so a changing query parameter forces a fresh fetch:

=IMPORTXML(A2 & "?v=" & TEXT(NOW(), "hhmm"), "//h1")

Google documents nothing about this, which makes it undefined rather than supported. A site that treats unknown query parameters as significant hands you a 404 or a different page. You are also multiplying your request rate against a target that already sees you as a shared robot.

Level 2. Google Apps Script

Formulas hit a ceiling. They cannot authenticate, branch, walk a paginated API or parse nested JSON. Extensions then Apps Script opens a cloud editor that removes all four limits and adds a few of its own.

One prerequisite before you copy anything from an old tutorial. Google deprecated the legacy Rhino runtime on 20 February 2025, and the release notes are blunt about what came next: "Scripts running on Rhino will continue to function until January 31, 2026, after which they will no longer execute." Everything below is written for V8. Code containing for each (var item in list) is dead code now, everywhere.

The header that never arrives

Start with the request function, because the version in the earlier edition of this article carried a bug that a large fraction of Apps Script tutorials still repeat:

const options = {
  method: 'get',
  headers: {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)'   // silently ignored
  }
};

Apps Script accepts the header map and sends its own user agent regardless. What the target sees is Mozilla/5.0 (compatible; Google-Apps-Script; beanserver; +https://script.google.com; id: ...), where the trailing id identifies your script project. Google's UrlFetchApp reference describes headers only as "a JavaScript key/value map of HTTP headers for the request" and never mentions the exception, which is exactly why the belief survives. Check it against your own project in half a minute:

function whatAmI() {
  Logger.log(UrlFetchApp.fetch('https://httpbin.org/user-agent').getContentText());
}

Other headers do go through. Authorization is how every Apps Script API integration on earth works, and Accept, Accept-Language and Cookie behave normally. The user agent is the one exception.

HTTP requests: UrlFetchApp

function fetchText(url) {
  const response = UrlFetchApp.fetch(url, {
    method: 'get',
    followRedirects: true,
    muteHttpExceptions: true,       // 4xx and 5xx come back as data, not as a throw
    validateHttpsCertificates: true
  });

  const code = response.getResponseCode();
  if (code === 200) {
    return response.getContentText();
  }

  console.log('HTTP ' + code + ' for ' + url);
  return null;                      // null, never a string that looks like data
}

Two deliberate choices. muteHttpExceptions: true stops a 404 from ending the run, at the cost of making the response-code check your job. And the failure path returns null rather than a string like 'ERROR: 404'. A string error lands in a cell, sorts alphabetically, survives a LEN() check and quietly poisons whatever reads the column three steps later.

The full list of options is short: method, headers, payload, contentType, followRedirects, validateHttpsCertificates, muteHttpExceptions and the deprecated useIntranet. There is no proxy setting. That absence decides more architecture than anything else on this page.

Parsing JSON, natively

The clearest advantage over VBA. No custom parser, no third-party module:

function getPrice(symbol) {
  const body = fetchText('https://example-api.com/quote?symbol=' + encodeURIComponent(symbol));
  if (body === null) return '';

  try {
    return JSON.parse(body).price;
  } catch (e) {
    console.log('Not JSON for ' + symbol + ': ' + body.slice(0, 120));
    return '';
  }
}

The try block is not decoration. An API that answers a rate-limited request with an HTML error page returns HTTP 200 often enough, and JSON.parse throws on the first <. The truncated log tells you at a glance whether you were throttled, redirected to a login, or served a maintenance notice.

Writing the result to the sheet

function writeQuotes() {
  const tickers = ['AAPL', 'MSFT', 'GOOGL', 'TSLA'];
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  sheet.getRange(1, 1, 1, 3).setValues([['Ticker', 'Price', 'Time']]);

  const rows = [];
  const now = new Date();

  tickers.forEach(function (ticker) {
    rows.push([ticker, getPrice(ticker), now]);
    Utilities.sleep(1000);          // one second between requests
  });

  sheet.getRange(2, 1, rows.length, 3).setValues(rows);
}

Collect into an array, write once. Google's own best practices page puts a number on the difference: the same fill written cell by cell takes about 70 seconds, and written in a single setValues call takes about 1 second. Seventy to one, on an operation that looks identical in the editor.

Parsing HTML in Apps Script

This is where Apps Script is weaker than VBA, and the older write-ups on the subject, including ours, were vague about why.

Apps Script does have an XML parser. XmlService, in Google's words, "allows scripts to parse, navigate, and programmatically create XML documents". The operative word is XML. Feed it real HTML with an unclosed <br> or a bare   and it throws. Right tool for an RSS feed or a sitemap, wrong one for a product page.

For actual HTML there is a port of Cheerio, and it is a library rather than the "wrapper service" this article previously described. Add it in the editor by Script ID:

1ReeQ6WO8kKNxoaA_O0XEQ589cIrRvEBA9qcWpNqdOP17i47u6N9M5Xh0

function priceFromHtml(html) {
  const $ = Cheerio.load(html);
  return $('[itemprop="price"]').attr('content') || '';
}

Read the source before you depend on it. The project lives at github.com/tani/cheeriogs, carries around 494 stars, is not archived, and its README calls itself "an unofficial update" that brings "cheerio to 1.0.0". Upstream Cheerio on npm is at 1.2.0, so the port is a version behind. Nothing is broken and nothing is racing ahead.

When one field on one known page is all you need, a regular expression is honest and small:

function extractByRegex(text, pattern) {
  const match = text.match(new RegExp(pattern));
  return match ? match[1] : '';
}

// const price = extractByRegex(html, "class='price'>([\\d.]+)<");

It is brittle by construction and fails the moment the template reorders two attributes.

A custom function for a cell

/**
 * Returns the price for a ticker.
 * @customfunction
 */
function MYPRICE(symbol) {
  return getPrice(symbol);
}

After saving, =MYPRICE("AAPL") works in any cell for anyone with access to the sheet. Google's guide to custom functions sets out three restrictions that the demos never mention.

Thirty seconds, then failure. "A custom function call must return within 30 seconds. If it doesn't, the cell displays #ERROR!" One slow origin and a column of them all fail together.

No authorised services. Custom functions "can only call services that don't have access to personal data". URL Fetch is allowed, along with Utilities, Cache, Lock and read-only Properties. Gmail, Drive and Docs are not, so a function that logs to a Drive file will not run from a cell at all.

Arguments must be deterministic, which rules out NOW() and RAND() as inputs. The consequence catches everyone: =MYPRICE("AAPL") recalculates when its argument changes and effectively never otherwise. If you want a number that moves, write it from a time-driven trigger and leave custom functions for lookups that stay put.

Auto-updating on a schedule

VBA needs an external scheduler and a machine that stays awake. Sheets has triggers. In the editor, the clock icon opens Triggers, then Add Trigger, then pick a function, a time-driven event and an interval. Or set it in code:

function setupTrigger() {
  ScriptApp.newTrigger('writeQuotes')
    .timeBased()
    .everyHours(1)
    .create();
}

Read the fine print in the ClockTriggerBuilder reference before you promise anyone a precise schedule. everyMinutes(n) accepts only 1, 5, 10, 15 or 30. nearMinute(minute) runs "plus or minus 15 minutes" around the time you name, and a daily trigger fires "near midnight (+/- 15 minutes)" by default. Google spreads the load deliberately, so a report due at exactly 09:00 is not something a trigger can promise.

The script runs whether or not your laptop is open and whether or not the sheet is. That is the one capability a VBA scraper cannot match without a machine that never sleeps.

What breaks at scale

A single fetch and ten thousand fetches are different problems, and Apps Script fails the second one in a predictable order.

The six-minute wall arrives sooner than the arithmetic suggests. Script runtime is capped at 6 minutes per execution, and unlike most Apps Script limits it does not improve with a paid account: Google's quotas page lists 6 minutes for consumer and Workspace alike. Spend one second in Utilities.sleep between requests and 360 seconds buys you at most 360 requests. Add a 400 ms response time and you are near 250. Ten thousand pages is forty executions, which is a queue, not a script.

The daily trigger budget is the limit that actually kills jobs. Total trigger runtime is 90 minutes a day on a consumer account and 6 hours on Workspace. An hourly trigger that runs its full six minutes needs 144 minutes, so on a gmail.com account it exhausts the budget shortly after lunch. It does not announce this. The trigger stops firing and the sheet quietly stops updating. If your rows stop refreshing at roughly the same hour every day, this is why.

Everything else has a number. URL Fetch is capped at 20,000 calls a day for consumer accounts and 100,000 for Workspace, which is generous rather than "typically thousands" as this article previously claimed. Responses and POST bodies cap at 50 MB, and the URL itself at 2 KB. That last one bites when you build a query string out of spreadsheet values.

What survives all of it is checkpointing: process what fits, record where you stopped, let the next trigger continue.

function crawlChunk() {
  const props = PropertiesService.getScriptProperties();
  const start = Number(props.getProperty('cursor') || 0);
  const sheet = SpreadsheetApp.getActiveSheet();
  const urls  = sheet.getRange('A2:A').getValues().flat().filter(String);

  const deadline = Date.now() + 4 * 60 * 1000;   // stop well before the 6-minute cap
  const rows = [];
  let i = start;

  while (i < urls.length && Date.now() < deadline) {
    rows.push([extractOne(urls[i])]);
    i++;
  }

  if (rows.length) {
    sheet.getRange(2 + start, 2, rows.length, 1).setValues(rows);
  }
  props.setProperty('cursor', String(i));
}

Four minutes rather than six, because the write at the end needs headroom and a run killed mid-setValues leaves the cursor lying about what was saved.

The sheet itself runs out too. A document holds up to 10 million cells or 18,278 columns, so three columns over three million rows is already at the ceiling, and it becomes unpleasant to open long before that.

Where the spreadsheet stops

Every limit in this section is a property of the client, and in Sheets you do not own the client.

Rendering. The most common wall, and worth restating. If the value appears only after JavaScript executes, no formula and no UrlFetchApp call will see it.

Bot management. Cloudflare announced on 1 July 2025 that new domains signing up would block AI crawlers by default unless the crawler pays, and the direction of travel has been the same for years: more challenges, applied earlier, keyed on client behaviour. Sheets cannot answer a challenge. It cannot change its user agent, use a proxy, hold a session, or solve anything interactive.

Your own administrator. Since 31 July 2024 Workspace admins can restrict which URLs are reachable, and the setting covers Apps Script together with, in Google's phrasing, "their Sheets IMPORT functions". The admin help states the result plainly: "Sheets and Apps Script return an error for functions that access URLs that aren't on the allowlist." It ships in Business Plus, Enterprise Standard and Plus, Enterprise Essentials Plus, Frontline Plus and Education Standard and Plus. The same release added audit logging, so on a corporate account the list of sites your spreadsheet pulls from is visible to someone else.

Robots and terms. The Robots Exclusion Protocol has been a formal standard since RFC 9309 in September 2022, and it states what a site wants rather than enforcing it. Google publishes no robots.txt policy for the spreadsheet fetcher, so reading the file yourself is the only way to know what you are ignoring.

The legal question does not change because the tool is a spreadsheet. The case usually cited to argue that public data is fair game is also the one most often misreported. hiQ Labs v. LinkedIn produced Ninth Circuit rulings in 2019 and again in April 2022 holding that scraping public profiles was unlikely to violate the Computer Fraud and Abuse Act, and that is where most summaries stop. The case continued. hiQ was found to have breached LinkedIn's user agreement, and it ended in late 2022 with hiQ paying $500,000 and accepting a permanent injunction. A win on one statute, a loss on the case.

Where the walls are the client, the fix is owning the client. Rotating addresses, real sessions and an actual browser are what rotating residential proxies and a rendering layer buy, and none of the three fits in a cell. The honest options from there are the Excel / VBA route on hardware you control, a rendering API called from Apps Script (ScrapingBee's entry plan was $49 a month for 250,000 credits when we read its pricing page on 13 August 2026), a managed extraction service, or a delivered data feed that lands in the same sheet without the sheet doing the fetching.

The numbers, read on 13 August 2026

From Google's Apps Script quotas page, the Sheets help on recalculation, and the Drive size limits page.

Two of those rows decide project outcomes. The six-minute runtime is a per-run limit and everyone plans around it. The 90-minute daily trigger budget is a per-day limit and almost nobody does.

Which to choose: Sheets or Excel / VBA

• Stock quotes and exchange rates. GOOGLEFINANCE first, every time.
• A table on a public page, needed weekly, shared with three colleagues. One IMPORTHTML and you are finished. This case is why the functions exist, and reaching for anything heavier is a mistake.
• Automation that runs while nothing of yours is switched on. A time-driven trigger, inside the 90-minute daily budget.
• Volume, frequency, awkward HTML, or a source that has ever shown you a challenge page. Excel / VBA on your own machine, or a dedicated pipeline. The deciding factor is not the parsing. It is that you get to choose the IP address and the user agent.
• A regulated or air-gapped environment. Excel / VBA. A Sheets formula sends the URL, and often the query values built into it, through Google's infrastructure and, on a Workspace account, into an audit log.

Conclusion

Google Sheets covers two levels of extraction with one tool. IMPORTHTML, IMPORTXML, IMPORTDATA and GOOGLEFINANCE handle everyday work without a line of code, and Apps Script with UrlFetchApp and native JSON.parse handles branching, pagination and scheduling, on Google's servers rather than yours.

The logic matches the Excel / VBA approach: request, parse, write, handle errors. What you pay for the shorter syntax and the built-in scheduler is control of the client. You cannot set the user agent, route through a proxy, execute JavaScript or hold a session, and you inherit whatever reputation Google's ranges carry on the target site.

So the choice is easier than it looks. Ask what the target does when a robot knocks. If the answer is nothing much, a formula is the cheapest correct tool ever built for the job. If the answer is a challenge page, no amount of spreadsheet cleverness will help, and the sooner you stop trying the sooner you get your data.