Web Scraping Using Google Sheets (With Real Example)

Web scraping with Google Sheets does not require coding knowledge for basic use cases. Instead of writing code, you use formulas to automate the data extraction process, which are similar to Excel functions.

Learn how to use all five built-in Google Sheets import functions: IMPORTHTML, IMPORTXML, IMPORTDATA, and IMPORTFEED with real-world examples.

When to use Google Sheets for web scraping?

Google Sheets is best used for lightweight, public, and structured data scraping. It’s ideal for tasks like pulling in RSS feeds, monitoring publicly available news or blog content, and importing data from simple HTML tables or lists.

Many websites block automated tools, such as Google Sheets. You’ll often receive a #N/A (could not fetch) error when attempting to scrape from heavily protected sites like Amazon, eBay, LinkedIn, or Instagram. These platforms implement anti-scraping mechanisms that detect and reject automated requests.

Using Google Sheets’ built-in import functions for web scraping

1. IMPORTHTML

The IMPORTHTML function allows you to extract data from HTML tables or lists on any public webpage directly into Google Sheets. Let’s say we want to extract the first table from this Wikipedia article. Formula:

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_GDP_(nominal)", "table", 1)

This brings in the first table from that page:

To extract more than one table or list from the same page, keep “table” or “list” unchanged, but change the index number, depending on how many tables are on the page. If the page has <ul> or <ol> lists, you can use the same method. Enter a list instead of a table.

Important notes:

• If you want to import all tables, there’s no built-in way to extract all tables at once; each IMPORTHTML call targets one table or list.
• If the data is JavaScript-generated, you need to use IMPORTXML.

2. IMPORTXML

This function allows you to extract specific pieces of data (like titles, prices, images) from a publicly accessible web source using XPath queries.

The IMPORTXML function works on both HTML and XML content, supporting structured data types such as Tab-Separated Values (TSV), Comma-Separated Values (CSV), Really Simple Syndication (RSS), and Atom XML feeds.

While the IMPORTHTML formula targets entire tables or lists, the IMPORTXML formula targets individual elements. To use IMPORTXML, you need a correct XPath query.

This is the result of using IMPORTXML to extract content from a web page:

Here are the specific formulas used to retrieve each data point:

Get book titles:

=IMPORTXML("https://books.toscrape.com/", "//h3/a/@title")

Get book prices:

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

Get book links:

=IMPORTXML("https://books.toscrape.com/", "//h3/a/@href")

Limitations of IMPORTXML:

• IMPORTXML only reads static HTML; if you aim to gather data from a dynamically loaded website like Amazon, it won’t appear.
• Google Sheets doesn’t support all XPath features, such as “starts-with”.

How to get XPath using your browser

1. Right-click on the element you want to extract
2. Select Inspect to open Developer Tools
3. Right-click the highlighted HTML element
4. Copy → click Copy XPath
5. Paste the XPath into your IMPORTXML formula

3. IMPORTDATA

The IMPORTDATA function enables you to import raw data from a CSV or TSV file accessible via a public URL. Here is a sample output from a public CSV dataset provided by Florida State University:

We used the following formula to extract the data:

=IMPORTDATA("https://people.sc.fsu.edu/~jburkardt/data/csv/hw_200.csv")

Limitations of IMPORTDATA:

• IMPORTDATA doesn’t support xlsx and json files.
• It doesn’t support login-protected files. The target web source must be public and directly accessible.

4. IMPORTFEED

The IMPORTFEED function enables users to fetch and display feed content, such as blog posts, news headlines, or YouTube video updates.

The below code example is used for importing a blog feed. This will return a table with columns including all the titles, author, date, and summary for the latest blog posts:

=IMPORTFEED("https://www.theverge.com/rss/index.xml")

Limitations of IMPORTFEED:

• IMPORTFEED function non-feed HTML scraping.

Importing data from CSV to Google Sheets

Suppose you want to import a public dataset of global COVID-19 statistics hosted on GitHub. You can use the following code to import a CSV file:

=IMPORTDATA("https://raw.githubusercontent.com/owid/covid-19-data/master/public/data/latest/owid-covid-latest.csv")

Once you enter the formula, IMPORTDATA will fetch the HTML code and display it in a table format. However, make sure that the target page must have a direct link to a .csv or .tsv file.

Analyzing scraped data with Google Sheets’ =AI() function

You can use Google Sheets =AI() functions to generate text tailored to your data, summarize, and categorize information.

However, it is only available to Google Workspace Business/Enterprise accounts; standard Google Workspace accounts without Gemini licenses don’t support =AI() functions.

If you’re looking to demo this functionality, you can also install GPT for “Sheets and Docs” to connect to OpenAI.

For better results, always include the relevant cell/range in your prompt, for instance:

=AI("Summarize customer feedback", A2:A10)

Limitations of the =AI() functions

• The AI can’t reference your whole sheet unless you explicitly include data via a prompt.
• You can’t revert or nest =AI() result.
• Only the first 200 selected cells will generate outputs when using bulk AI.

Alternatives you can use without Gemini

1. Open your Google Sheet.
2. Go to the top menu:
   • Extensions > Add-ons > Get add-ons
3. Search for GPT for Sheets and Docs

Get your OpenAI API key: Go to https://platform.openai.com/account/api-keys and click “Create new secret key”. Go to your Google Sheet and paste the key.

What are the limits of using Google Sheets for scraping?

One of the biggest limitations of Google Sheets is that it cannot render JavaScript. Many popular websites use JavaScript frameworks like React, or Angular to load content dynamically. Since IMPORTXML or IMPORTHTML parameters can only access raw HTML, they will be ineffective for scraping sites that rely heavily on client-side rendering.

As your spreadsheet grows in size and complexity, performance can degrade rapidly. For projects involving dynamic content, authenticated data, large volumes, or automation, it’s better to use scraping tools or scripts.

Additionally, Google Sheets cannot scrape content hidden behind a login, paywall, or CAPTCHA. For these use cases, you’ll need to use more advanced tools such as Python with Selenium or Puppeteer.

Common errors in web scraping using Google Sheets

• #N/A – Could not fetch URL: The most common cause is an incorrect or malformed URL. However, it can also happen if the website is down or the RSS feed is no longer active. You can check whether the URL is valid and publicly accessible. Google Sheets is not optimized for heavy computation or scraping data at a large scale.
• #REF! – Cell reference error: If your sheet does not have enough empty rows or columns, Google Sheets cannot display all the data. You can clear cells below or to the right of your formula, or paste the formula in a new sheet or area with enough empty cells.
• #VALUE! – Invalid value: This error occurs when a formula receives a data type or format that it cannot interpret or process. Ensure that the target URL is correctly enclosed in quotes and that your XPath expression matches valid elements.
• #ERROR! – General function failure: If your spreadsheet is nearing its maximum size or memory usage, or something has gone wrong, but Sheets can’t specify what.

What is web scraping in Google Sheets?

Web scraping sheets refers to using built-in functions like IMPORTHTML, IMPORTXML, IMPORTDATA, and IMPORTFEED to automatically extract structured website data into a spreadsheet. It’s a code-free data scraping method that allows you to pull in live data, such as tables, news, or blog posts.