Guide to Using Google Sheets for Basic Web Scraping

Google Sheets web scraping can be an effective technique. While most ways of web scraping require you to write code, web scraping with Google Sheets requires no coding or add-ons. All you need to do is use a built-in function of Google Sheets. Thus, it acts as a basic web scraper.

This guide will show you how to scrape website data with Google Sheets with a practical example. 

Importing XML and HTML

IMPORTXML is a function that imports data from various structured data types. You can use it not only to scrape XML documents but also HTML documents. Other structured data types that the IMPORTXML formula supports are Comma Separated Values (CSV), Tab Separated Values (TSV), Really Simple Syndication (RSS), and Atom XML feeds.

How IMPORTXML works

The IMPORTXML function needs two parameters—the URL of the page to examine and the XPath query.

If you would like to see the official documentation, click here. However, this article will explain IMPORTXML and a few other related functions in detail. Let's look at some of the examples to help us understand the IMPORTXML function a little better.

To extract the title element from the https://quotes.toscrape.com/ web page, the formula would be as follows:

=IMPORTXML("https://quotes.toscrape.com/","//title")

As evident here, the first parameter is the website URL, and the second parameter is the XPath query. Also, note that you would need to enclose both parameters in quotation marks unless these are a reference to another cell.

You can create an XPath query directly from the browser. Open the webpage in your preferred browser, right-click the element you want to extract, and select Inspect. You’ll see the developer tools open with the HTML element highlighted. Right-click the HTML of the highlighted element, then select Copy and choose Copy XPath. This action copies the XPath and saves that element to your clipboard.

Let's look at one more example. If you want to extract the first quote from the webpage, the formula will be as follows:

=IMPORTXML("https://quotes.toscrape.com/","(//*[@class='text']/text())[1]")

If this XPath query seems like something you aren’t comfortable with, we recommend reading the XPath section on our blog to learn more about writing XPath queries. 

Alternatively, you can enter the URL in cells. For example, if you enter the URL in cell A1 and XPath in cell A2, your formula will refer to these cells as follows:

=IMPORTXML(A1,A2)

Quick XPath introduction

The IMPORTXML works only with XPath. This section gives you a quick overview of XPath, which is enough to start using the IMPORTXML formula. 

If you have an HTML where the body tag directly contains h1, you can use the following XPath to locate this h1 element:

/html/body/h1

The forward slash at the beginning of an XPath expression represents the document. We can read the above XPath as follows:

• Begin with the root of the document

• Find an html tag in the document

• Find a body tag in the html tag

• Find an h1 tag 

If you want to match all the h1 tags, you can make this Xpath much shorter as follows:

//h1

Two forward slashes mean to match all descendants. This XPath expression will match all the h1 elements found anywhere in the document. Next, to extract the text contained in the h1 element, you can use the text() function:

//h1/text()

It’s also possible to extract the value of any attribute. For example, if you want to extract the value of the class attribute of h1 tags, the XPath will be as follows:

//h1/@class

As you can see, to work with attributes, you have to prefix the @ character.

Let's take a look at filtering. For example, what if you wanted to get only the h1 elements with a class named pdp_orange applied? The following XPath will do just that:

//h1[@class="pdp_orange"]

We learn from this expression that we need to use square brackets. 

How to extract data from a website to Google Sheets

Follow the steps below to extract data from a website to Google Sheets without the use of add-ons:

1. First, let’s find the XPath to select elements. Load the site in Chrome, Right-click the element, and select Inspect. 

You’ll see the developer tools window opening with the Elements panel. As you might know, the Developer Tools is crucial to building any web scraper. Next, if you want to select only one element, right-click the HTML element, select Copy, and click Copy XPath.

In this example, we’ll work with https://books.toscrape.com/, aiming to get all the book titles. These requirements mean that we need to write a custom XPath that looks like this:

//h3/a/@title

Press Ctrl+F or Command+F when the Elements tab of the developer toolbar is open and paste this XPath. You’ll see a preview of the 20 items selected.

Looking at the HTML markup, you’ll see that the book name is incomplete for many books within the element text. That's why we’re extracting the title attribute values containing the complete book title. 

2. Navigate to Google Sheets and create a new sheet. This step requires you to log in to your Google account if you haven't done so already. Depending on how many sheets you have created in the past, you would see a New sheet tile on the top or a floating New Sheet button in the lower right corner.

Tip: Open sheets.new to directly create a new sheet.

3. Enter the URL of the webpage and the XPath in two cells. Entering these values in the cells enables us to create a formula that’s easy to maintain.

4. Finally, we can start extracting website data with Google Sheets. In a new cell, for example, A4, enter the following formula:

=IMPORTXML(B1,B2)

This formula effectively calls the function below:

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

Press ENTER to execute this function. It’ll take a moment and get all the twenty book titles.

You’ll see that one single formula extracts all twenty book titles efficiently. In order to extract the book prices, the first step is to create the XPath for prices. This XPath would be as follows:

//*[@class="price_color"]/text()

Enter this XPath in a cell, let's say, B3. After that, enter the following formula in cell B4:

=IMPORTXML(B1, B3)

You’ll see that now you have the book prices fetched.

It’s also possible to extract only a single column. For this, you can use another Google Sheets web scraping function—INDEX. The INDEX function can extract any row or column from a table. It requires one mandatory and two optional parameters:

• The reference of the table

• Row - Optional

• Column - Optional

For example, if we wanted only the movie titles, which are in column number 3, our formula would be as shown below:

=INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_highest-grossing_films","table",1),,3)

We have skipped the optional parameter “row” as we want to keep all the rows. Let’s move on to another helpful function—IMPORTFEED.

Import data from XML feeds to Google Sheets

RSS and Atom are two standard XML formats to generate website feeds. Even though scraping data from them using the IMPORTXML function is possible, that would still require writing the XPath queries. Thus, a specialized function is more suitable. There are a few third-party add-ons, but we don’t need any of them. 

This is where the IMPORTFEED function comes in. It only needs one argument line, which is the URL of the feed. If you send the feed URL, this function will fetch the data and present it to you in Google Sheets. Let's take the example of the New York Times Technology feeds to see this function in action. 

1. Create a new sheet and enter the URL of the feed in cell B1:

https://rss.nytimes.com/services/xml/rss/nyt/Technology.xml

2. Now, in cell A2, enter the following formula:

=IMPORTFEED(B1)

3. Press Enter, and you’ll see twenty feed items imported into your Google Sheets document.

Customizing data imported by IMPORTFEED

The IMPORTFEED function has the following optional parameters:

• Query - You can use this to specify which information you want to import.

• Headers - As you can see from the above image, there are no headers in the imported data. If you want to see column headers, then set this parameter to TRUE.

• num_items - You can also control how many items are fetched. If you want only five items to be imported, set this parameter to 5.

Let’s update the function call to the following:

=IMPORTFEED(B1,,TRUE,5)

Note that we have left a blank space for the query. This function call brings all the columns. However, now you’ll see only five items fetched along with column headers.

If you want specific information from the feed, there is a list of possible items that you can send to the second parameter, which we have not used so far. If you want only the information about the feed, enter the following formula:

=IMPORTFEED(B1,"feed")

This formula returns a single row with the feed information. In this particular example, the output is as follows:

NYT > Technology https://www.nytimes.com/section/technology

You can narrow down this information further by specifying which piece of information you want from the feed information. For example, if you only need the title, modify the formula as follows:

=IMPORTFEED(B1,"feed title")

Other options are feed description, feed author, and feed URL. You’ll see an error if any of the information is not found in the feed.

If you want to see a specific column, you can use the query as "items <type>". For example, if you want to get only the titles, enter the following formula:

=IMPORTFEED(B1,"items title")

Note that you can't specify more than one column in such a manner. If you want to get two columns, you need to make a separate function call.

Importing Data from CSV to Google Sheets

If you have a website URL that contains a CSV file, you can use the IMPORTDATA function to get the data. For example, create a new sheet and enter the following URL in the cell B1:

https://www2.census.gov/programs-surveys/decennial/2020/data/apportionment/apportionment.csv

In the cell A2, enter the following formula:

=IMPORTDATA(B1)