Google Sheets Web Scraping: IMPORTXML and Beyond

If web scraping with Excel / VBA is the "desktop" route, where a script pulls data straight into the workbook, then Google Sheets offers something Excel does not have out of the box: built-in scraping formulas. For a lot of jobs, Google Sheets web scraping needs no code at all - you drop a function into a cell and the sheet loads data from a site and keeps it updated.

And when formulas are not enough, Google Apps Script steps in - a cloud-based equivalent of VBA written in JavaScript. It can make HTTP requests, parse JSON and run on a schedule, all of it on Google's servers rather than your computer.

In this article we will cover both levels: first the formulas for quick, no-code scraping, then Apps Script for the harder jobs.

How Google Sheets differs from Excel / VBA

A quick comparison helps you pick the right tool for the job:

The main takeaway: for light and medium scraping, Google Sheets is often faster because half the job is solved with a single formula. For heavy or non-standard scenarios the logic is the same as in VBA - only the syntax changes.

A note on syntax. These examples use commas to separate arguments (=IMPORTHTML(url, "table", 1)), which is what Google Sheets expects in the US/UK locale. If your spreadsheet locale uses commas 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 index:

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

Arguments: the URL, the element type ("table" or "list") and its ordinal position on the page. If the page has several tables, step through the index (1, 2, 3...) until you hit the one you want. The result spills into the neighboring cells automatically.

IMPORTXML - targeted extraction with XPath

The most powerful of the formula tools. It takes a URL and an XPath query - an expression that addresses a specific element in the markup:

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

A few handy XPath patterns:

The easiest way to grab an XPath is in the browser: open DevTools (F12), find the element, right-click, then Copy, then Copy XPath. If you are new to it, our primer on XPath for scraping walks through the syntax.

IMPORTDATA - CSV and TSV

If the source serves a ready CSV or TSV file, pull it directly:

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

The function splits the values into columns for you. Ideal for open datasets and exports.

IMPORTFEED - RSS and Atom

For news and blog feeds:

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

GOOGLEFINANCE - finance with no scraping at all

GOOGLEFINANCE deserves a special mention - it is a built-in source of stock and currency data. This is the case where you scrape nothing: Google has already collected it for you.

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

The first example is the current share price, the second is a currency pair rate, and the third is historical quotes over a period. If your task fits what GOOGLEFINANCE covers, this is the most reliable route: no blocks, no broken markup. Scraping third-party sites is only needed where that data does not exist or you need a non-standard source.

Level 2. Google Apps Script

Formulas are great, but they hit a ceiling: they cannot authenticate, get past complex protection, parse nested JSON from an API, or run branching logic. This is where Apps Script begins.

Open your sheet, then Extensions -> Apps Script, and you land in a cloud code editor. It is the direct analog of the VBA editor, only in JavaScript.

HTTP requests: UrlFetchApp

A basic request to a page or API:

function getResponse(url) {
  const options = {
    method: 'get',
    headers: {
      'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64)'
    },
    muteHttpExceptions: true   // do not throw on 4xx/5xx codes
  };

  const response = UrlFetchApp.fetch(url, options);

  if (response.getResponseCode() === 200) {
    return response.getContentText();
  }
  return 'ERROR: ' + response.getResponseCode();
}

The logic is the same as a GetResponse routine in VBA: open the request, set a User-Agent, check the response code. Only the wrapper changes - UrlFetchApp instead of MSXML2.XMLHTTP.

Parsing JSON - natively

The main advantage of Apps Script over VBA: JSON is parsed with a single built-in command, no hand-rolled functions or third-party modules.

function getPrice(symbol) {
  const url = 'https://example-api.com/quote?symbol=' + symbol;
  const json = getResponse(url);

  const data = JSON.parse(json);   // one line instead of a custom parser
  return data.price;
}

In VBA you would have to parse the string by hand or pull in a JSON library. Here it is JSON.parse, and you work with the object immediately.

Writing the result to the sheet

Write data into cells through the sheet object:

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

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

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

  tickers.forEach(function (ticker) {
    const price = getPrice(ticker);
    rows.push([ticker, price, now]);
    Utilities.sleep(1000);   // 1-second pause between requests
  });

  // Write everything in one call - much faster
  sheet.getRange(2, 1, rows.length, 3).setValues(rows);
}

The "collect into an array and write in one call" principle matters for speed here just as it does in VBA: setValues on the whole range is far faster than writing cell by cell in a loop.

Parsing HTML in Apps Script

Built-in HTML parsing is trickier: Apps Script has no full DOM parser like VBA's HTMLDocument. In practice people use either regular expressions or extraction between markers:

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

// Example: pull the price out of "<span class='price'>152.34</span>"
// const price = extractByRegex(html, "class='price'>([\\d.]+)<");

For complex markup, people sometimes pull in third-party libraries (Cheerio via a wrapper service, for instance), but for most jobs regular expressions or an IMPORTXML formula do the trick.

A custom function for a cell

Apps Script lets you create your own formula that you can call from the sheet like a built-in one:

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

After saving, =MYPRICE("AAPL") works in any cell. VBA has a similar capability through UDFs, but here the function is instantly available to everyone with access to the sheet.

Auto-updating on a schedule

In VBA, recurring runs need an external Windows scheduler. In Google Sheets the schedule is built in - it is called triggers.

In the Apps Script editor: the clock icon (Triggers), then Add Trigger, then pick the function, a "Time-driven" event and an interval (hourly, daily, and so on).

Or programmatically:

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

The script runs on Google's servers even when your computer is off and the sheet is closed. A VBA scraper cannot do that without a machine that is always on.

Limits and gotchas

The cloud approach has its price - Google's quotas:

• IMPORT... formulas refresh periodically (roughly once an hour) and are cached. They are no good for second-by-second freshness.
• UrlFetchApp has a daily call limit (it depends on your account type - typically thousands of requests per day for a free account).
• Script execution time is capped (around 6 minutes per run for free accounts). Long scrapes have to be split into chunks.
• #N/A and Loading... in formulas often mean the source did not return data, changed its markup, or blocked the request coming from Google's servers.

These limits are the main reason heavy, frequent scraping sometimes goes back to the "desktop" route from the Excel / VBA guide, where request limits are essentially non-existent. For large or protected sources, a dedicated pipeline with rotating proxies will outrun any spreadsheet.

Which to choose: Sheets or Excel / VBA

A short cheat sheet:

• Need it fast and code-free, data lives in a spreadsheet, team collaboration -> Google Sheets and formulas.
• Stock quotes and exchange rates -> GOOGLEFINANCE first, and only scrape if it falls short.
• Auto-updates without a PC left running -> Google Sheets with triggers.
• Large volume, frequent requests, no limits, complex HTML parsing -> Excel / VBA.
• Corporate environment with no cloud, local data -> Excel / VBA.

Conclusion

Google Sheets covers two levels of scraping with one tool. The IMPORTHTML, IMPORTXML, IMPORTDATA and GOOGLEFINANCE formulas solve everyday tasks without a single line of code, while Apps Script with UrlFetchApp and native JSON.parse takes on everything complex - and does it in the cloud, on a schedule, without your computer.

Compared with the Excel / VBA approach the logic stays the same - request, parse, write, handle errors - but the syntax is simpler, JSON parses out of the box, and automation needs no external scheduler. The price of that convenience is Google's quotas: when you hit them, it is worth going back to the desktop VBA solution. The two tools are not rivals but complements - pick the one that fits the job.

When a spreadsheet stops being enough - the source blocks Google's IPs, the volume is too high, or the data has to feed a downstream system - scraping.pro can run it as a managed data extraction service and deliver clean data straight into your sheet, database or dashboard.