Google Sheets Web Scraping With IMPORT Formulas and Apps Script

Getting data from a website into Google Sheets takes one formula for a lot of pages, and no programming at all. By using built-in functions and formulas, you can pull data from web pages directly into your spreadsheet. The essential formulas and methods to get started with web scraping in Google Sheets.

Formulas or a Script

The formulas below read what the server sends. They see the HTML as it arrives and nothing that JavaScript builds afterwards, so a page that fills its content in the browser comes back empty no matter which formula you point at it. That single limit decides which half of this article you need.

For a static page, a table or a feed, one formula in one cell is the whole job. For anything rendered in the browser, or anything that needs a header, a key or a POST, the work moves to Apps Script, which runs JavaScript on Google’s side and can call an API. Both are covered here, formulas first because they are shorter, then the script.

Main formulas for Google Sheets web scraping

Google Sheets supports a number of formulas that allow you to work with web data in various ways. You can find a complete list of them on the official documentation page.

The most common and useful formulas for scraping data from websites are these three:

• IMPORTXML. This formula allows you to extract data from specific XML elements on a web page. You can use XPath queries to target specific data points.
• IMPORTFEED. This formula is used to import data from a RSS or ATOM feed. The data is imported directly into your spreadsheet.
• IMPORTHTML. This formula is designed to extract data from HTML tables on web pages. It’s a good option for simple tables with well-defined structures.

For those with programming skills, there is another way to scrape data in Google Sheets, custom scripts written in GS, which resembles JavaScript coding language. This method provides more flexibility than simply relying on the existing formulas, as it allows users to customize their own code for particular needs.

Each formula below comes with its syntax, a working example and what it cannot do. The first time you use any of them, Sheets asks you to allow web formulas before it will run the query.

Once enabled, you’ll be able to freely execute queries using formulas within the scope of this document.

IMPORTHTML for Tables and Lists

The IMPORTHTML function is one of the simplest and most common ways to get data from a page. It allows you to extract data from tables and lists on a given web page.

To use it, you need to enter a formula in the cell of the following form:

=IMPORTHTML(url, query, index)

Instead of url, specify in quotes the link to the website from which you want to take the data. Then, specify what type of data you want to extract. This formula expects to receive the value “list” or “table” as the query parameter, depending on the type of content you want to collect. And the last parameter is the number of the table or list on the page, with the counting starting from 1. That is, if there are several lists or tables on the page, you can easily get exactly the data that you need.

Let’s consider an example and try to collect data about proxies from our page with a list of free proxies:

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

The result of executing this formula will look like this:

Its pros and cons:

Overall, IMPORTHTML is a useful tool for extracting data from simple, structured web pages. However, its limitations in handling dynamic content, authentication, and potential data integrity issues should be considered when choosing a data extraction method.

IMPORTXML for Anything With a Path to It

Unlike the previous formula, IMPORTXML allows you to retrieve data from any website using XPath. There is a separate piece on XPath if the syntax is new. The short version is that an XPath expression names a node by its position in the document tree, and IMPORTXML returns whatever that expression matches.

To extract any data from a page, you can use the following template:

=IMPORTXML(url, xpath)

In addition to the page URL and the XPath of the desired elements, you can also specify a third parameter for the language and region code for which you want to retrieve data from the website. However, since this parameter is not mandatory, it can be ignored. In this case, the same localization parameters will be used as those for the document.

Let’s use IMPORTXML to retrieve all the articles from our blog:

=IMPORTXML("https://en.wikipedia.org/wiki/Web_scraping", "//h2")

This will return the following data:

IMPORTXML reaches anything the server sends, which is more than the other formulas manage. It helps to scrape data from websites directly into a spreadsheet. However, it’s important to weigh up the pros and cons before deciding if this is the right option for you.

The IMPORTXML formula is perfect for web scraping projects where you need specific pieces of information to analyze trends or process other tasks. So, it is the best choice for those who don’t good at programming.

Author’s Tip: Sites that fill their content after the page first appears are the ones that catch people out. IMPORTXML never sees that content, and no amount of reworking the path changes it. Apps Script or a rendering API is the way through.

IMPORTFEED for RSS and Atom

The IMPORTFEED formula is a valuable tool for extracting data from RSS or Atom feeds and presenting it in a spreadsheet format. While its scope is narrower compared to other data collection formulas, it can still be a useful addition to your data gathering toolkit.

The general syntax of the formula is as follows:

= IMPORTFEED(url, [query], [headers], [num_items])

However, the only required parameter is the link to the RSS feed. It may also be useful to specify whether to use headers, in TRUE/FALSE format. As a query parameter, you can specify items to get a full table with all items or feed, which returns only one row. The last parameter allows you to specify the number of items to retrieve, if you do not specify it, all available items will be returned.

Here is IMPORTFEED against the New Heights RSS feed:

=IMPORTFEED("https://feeds.megaphone.fm/newheights")

This formula will import the feed data into a spreadsheet table, displaying elements like titles, summaries, and publication dates. The result will look like this:

Now let’s consider the pros and cons of this formula:

IMPORTFEED covers RSS and Atom and nothing else. Its limited scope makes it less popular than other data collection formulas.

How Fresh the Imported Data Is

The values these formulas put in a cell are cached. The formula runs, Sheets keeps the result, and what you look at afterwards is that stored copy rather than a live read of the page.

Google publishes no refresh interval for the IMPORT family. Its reference pages for IMPORTHTML, IMPORTXML, IMPORTDATA and IMPORTFEED describe the arguments and say nothing about how often a cached result is replaced, so any specific figure you find in a blog post, this one included, would be someone’s observation rather than a documented promise. Treat the cadence as something you don’t control.

You can only force a recalculation. Editing the formula and pressing enter reruns it. So does changing any cell it reads, which is the argument for putting the URL in a cell and referencing it rather than hard-coding the string. Deleting the formula and pasting it back has the same effect and is the crudest reliable option.

The practical consequence is about what you build on top. A dashboard that has to be right at the moment someone opens it should not read straight from an IMPORT cell, because opening a sheet does not force a refresh. Either drive the fetch from Apps Script on a trigger you set, where the schedule is yours, or accept that the number on screen is as old as the last recalculation.

When a Formula Returns an Error Instead of Data

Every formula here fails in one of a handful of ways, and the message tells you which half of the problem you have. These are the ones worth recognising on sight.

Two of these are worth a longer word.

Imported content is empty is the one that sends people in circles, because the URL opens fine in a browser and the formula still returns nothing. The page is rendering its content with JavaScript, and Sheets never runs that. No change of XPath fixes it. That is the point where the Apps Script route below, or an API that renders the page, becomes the answer rather than a preference.

#REF! has a second cause that is easy to miss. A sheet created or opened through an API rather than a browser refuses the IMPORT family outright, and says so in the cell, Please use a desktop web browser to allow access to fetch data from other sources. Anyone automating sheet creation meets this and assumes the formula is wrong. Opening the sheet once in a browser clears it.

Web scraping with Google Apps Script

Google Apps Script runs JavaScript on Google’s side. It can build custom menus and buttons, reach third-party services over HTTP, and read or write any cell in the sheet. It also reaches Drive and can send mail, though neither matters for scraping.

To open the Apps Script area, go to Extensions and then Apps Script.

As an example, we will use HasData’s web scraping API. After signing up, you’ll get 1000 free credits to help get you started.

To start, let’s create a simple script that will get all the code of the page. After launching App Script, we get to the scripting window.

We can name the function differently to make it more convenient, or we can leave the same name. This affects how we refer to our script on the sheet. The function name will be the name of the formula.

If you don’t want to learn how to create your own script and want to use the finished result, skip to the final code and an explanation of how it works.

To execute a query, we need to set its parameters. You can find the API key in your account in the dashboard section. First, let’s set the headers:

var apiKey = PropertiesService.getScriptProperties().getProperty('HASDATA_API_KEY');
  var headers = {'contentType': 'application/json', 'x-api-key': apiKey};

Next, let’s specify the body of the request. Since we need the code of the whole page, we just specify the link to the site from which we need to collect data:

var data ={'url': 'https://nalgene.com/water-bottles/wide-mouth/'};

Finally, let’s gather the query and execute it, and display the result:

var options = {
    'method': 'post',
    'headers': headers,
    'payload': data
  };
  var response = UrlFetchApp.fetch('https://api.hasdata.com/scrape', options).getContentText();
  Logger.log(response);

As a result, we get the following:

You can configure the data extraction settings in your account in the Web Scraping API section. There you can also visually configure the query using special functions.

For example, we can use the execution rules function to extract only the product names and prices:

 "extractRules": {
    "title": "h2.woocommerce-loop-product__title",
    "price": "div.price-container"
  },

Adding new rules is specified in the format “Name”: “CSS selector”. Writing those parameters hard into the script makes the formula static. Passing them from the sheet instead is what makes it reusable. So, let’s add these data to the input. Input parameters are specified in parentheses next to the function name.

function myFunction(...rules) {…}

Several parameters have to go into the rules variable. For convenience, let’s rename the function and add another parameter to the input, a link to the page the data comes from.

function scrape_it(url, ...rules) {…}

Now let’s declare the variables and select the headers for the future table from the extraction rules we got:

  var extractRules = {};
  var headers = true;

// Check if the last argument is a boolean
  if (typeof rules[rules.length - 1] === "boolean") {
    headers = rules.pop();
  }

Let’s write the rules in the extractRules variable in the form we need:

for (var i = 0; i < rules.length; i++) {
    var rule = rules[i].split(":");
    extractRules[rule[0]] = rule[1].trim();
  }

Let’s change the body of the query by putting in variables instead of values and then execute it.

  var data = JSON.stringify({
    "extractRules": extractRules,
    "wait": 0,
    "screenshot": false,
    "block_resources": true,
    "url": url
  });

    var options = {
    "method": "post",
    "headers": {
      "x-api-key": "YOUR-API-KEY",
      "Content-Type": "application/json"
    },
    "payload": data
  };

  var response = UrlFetchApp.fetch("https://api.hasdata.com/scrape", options);

This query returns data as JSON with attributes that contain the required data and whose name is identical to the one we set in the extraction rules. To get this data, let’s parse the JSON response:

  var json = JSON.parse(response.getContentText());

Let’s set the attribute variable that contains the result of the extraction rules:

  var result = json["extractedData"];

Get all the keys and the length of the largest array to know its dimensionality:

   // Get the keys from extractRules
  var keys = Object.keys(extractRules);

  // Get the maximum length of any array in extractedData
  var maxLength = 0;
  for (var i = 0; i < keys.length; i++) {
    var length = Array.isArray(result[keys[i]]) ? result[keys[i]].length : 1;
    if (length > maxLength) {
      maxLength = length;
    }
  }

Create a variable output, in which we put the first row of data, which will be the column names of the future table. To do this, check the headers and keys so that we can enter only those columns for which the data were found.

  // Create an empty output array with the first row being the keys (if headers is true)
  var output = headers ? [keys] : [];

Each element from the extraction rules goes into the output variable line by line.

  // Loop over each item in the extractedData arrays and push them to the output array
  for (var i = 0; i < maxLength; i++) {
    var row = [];
    for (var j = 0; j < keys.length; j++) {
      var value = "";
      if (Array.isArray(result[keys[j]]) && result[keys[j]][i]) {
        value = result[keys[j]][i];
      } else if (typeof result[keys[j]] === "string") {
        value = result[keys[j]];
      }
      row.push(value.trim());
    }
    output.push(row);
  }

Finally, let’s put the result back on the sheet:

  return output;

Now you can save the resulting script and call it directly from the sheet, specifying the necessary parameters.

Final code for web scraping with Google App Script

The pre-made script needs two things done for those who skipped the section on creating a script but want to use it.

Go to Google Sheets and create a new Spreadsheet.

Go to Extensions and open App Scripts. In the window that appears, select all the text and replace it with our script code:

Script code:

function scrape_it(url, ...rules) {
  var extractRules = {};
  var headers = true;

  // Check if the last argument is a boolean
  if (typeof rules[rules.length - 1] === "boolean") {
    headers = rules.pop();
  }

  for (var i = 0; i < rules.length; i++) {
    var rule = rules[i].split(":");
    extractRules[rule[0]] = rule[1].trim();
  }

  var data = JSON.stringify({
    "extractRules": extractRules,
    "wait": 0,
    "screenshot": false,
    "block_resources": true,
    "url": url
  });

  var options = {
    "method": "post",
    "headers": {
      "x-api-key": "YOUR-API-KEY",
      "Content-Type": "application/json"
    },
    "payload": data
  };

  var response = UrlFetchApp.fetch("https://api.hasdata.com/scrape", options);
  var json = JSON.parse(response.getContentText());
  var result = json["extractedData"];

    // Get the keys from extractRules
  var keys = Object.keys(extractRules);

    // Get the maximum length of any array in extractedData
  var maxLength = 0;
  for (var i = 0; i < keys.length; i++) {
    var length = Array.isArray(result[keys[i]]) ? result[keys[i]].length : 1;
    if (length > maxLength) {
      maxLength = length;
    }
  }
    // Create an empty output array with the first row being the keys (if headers is true)
  var output = headers ? [keys] : [];

    // Loop over each item in the extractedData arrays and push them to the output array
  for (var i = 0; i < maxLength; i++) {
    var row = [];
    for (var j = 0; j < keys.length; j++) {
      var value = "";
      if (Array.isArray(result[keys[j]]) && result[keys[j]][i]) {
        value = result[keys[j]][i];
      } else if (typeof result[keys[j]] === "string") {
        value = result[keys[j]];
      }
      row.push(value.trim());
    }
    output.push(row);
  }

  return output;
}

Store the key rather than typing it into the script. In the Apps Script editor open Project Settings, scroll to Script Properties, and add one named HASDATA_API_KEY with your key as the value. The code above reads it with PropertiesService.

This matters more in a spreadsheet than in most places. A script belongs to the sheet, so anyone you share it with as an editor can open the editor and read the source. A key pasted into the code goes out with every copy of that file, and copies are what spreadsheets are for.

Back on the sheet, put the link to the page in cell A1, and in the next cell specify the formula. In general, the formula looks like this:

=scrape_it(URL, element1, [element2], [false])

Where:

1. The URL is a link to the page to be scraped.
2. Elements (you can specify more than one). Elements are specified in the format “Title: CSS_Selector @attribute”. Instead of Title specify the name of the element, then via a colon specify the CSS selector of the element and, if necessary, via a space and @ specify the attribute, which value is necessary. If only element text is needed, no attribute is specified. For example:
3. “title: h2” – to get product name.
4. “link: a @href” – to get product link.
5. [false]. This is an optional parameter that defaults to true. It indicates whether or not the headers for the columns should be saved. If you don’t specify anything, the header will be specified in the first cell and will be taken from the Elements parameter. If you specify false, column headers will not be specified and saved.

Now, using all the skills we learned, let’s get a table identical to the one we got with IMPORTXML but using the scrape_it formula:

=scrape_it(A1,"title: h2.woocommerce-loop-product__title", "price: div.price-container","link:div.add-on-product>a @href", "Image:div.add-on-product__image-container>img @src")

As a result, we got the following table:

However, we have to get the description from every single product page, so we don’t need to specify headers. In this case, we need to specify the third additional parameter false, at the end of the formula:

Scraping with Google Sheets and App Scripts can be a good tool for data collection and analysis. With the help of Google’s scripting language, you can easily access external websites to retrieve information, use web scraping API for automation processes or even manipulate results in whatever way suits your purpose.

It covers what IMPORTXML cannot. IMPORTXML is no use on sites with dynamic rendering. However, using Google App Scripts together with the Web Scraping API can solve this problem. The considered script is suitable for scraping any site, no matter what platform it is built on, whether it has dynamic rendering or not.

Additional Useful Formulas

In addition to the formulas discussed earlier, there are also those that are useful for processing or importing information. For example, if you want not to scrape data from a website, but to import it from another Google Sheets file.

For example, it may happen that it is not enough for you to simply extract data from a page, but you need to collect specifically the data that corresponds to the pattern you need. In this case, you can use a special formula to work with regular expressions.

Three more formulas are worth knowing, and they combine with those already discussed earlier to achieve the best results in the scraping process.

REGEXEXTRACT for Pulling Patterns Out of Text

The REGEXEXTRACT function in Google Sheets allows you to extract text that matches a specified regular expression. On its own it does nothing for scraping, since it needs text to work on. Wrapped around an IMPORTXML call it turns a page of markup into the one field you wanted.

The pairing is worth the extra nesting when you need to first retrieve data from a webpage using IMPORTXML and then refine the extracted information using REGEXEXTRACT. For instance, you can extract email addresses from a webpage.

There is a longer piece on scraping email addresses. Here is the same job done in Google Sheets. Let’s break down the REGEXEXTRACT function syntax:

=REGEXEXTRACT(text, pattern)

Consider a random New York cafe website. We’ll extract the contact email address from the webpage:

=REGEXEXTRACT(IMPORTXML("https://blackcatles.com/", "//body"), "[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}")

In this formula, the text is the result returned by the IMPORTXML function, and we use a regular expression to extract email addresses. The extracted email address is shown in the sheet.

As with previous examples, let’s summarize the pros and cons of this formula in a table:

REGEXEXTRACT and IMPORTXML together read a page and then narrow the result to the one field you wanted, which covers most of what people ask a spreadsheet to do with a webpage.

IMPORTDATA for CSV and TSV Files

The IMPORTDATA function in Google Sheets allows to import the data from a CSV or TSV file hosted on the internet. While it’s not the most common task, IMPORTDATA is particularly useful for importing data from a file into a spreadsheet.

The IMPORTDATA function follows a simple syntax:

=IMPORTDATA(url)

Any CSV with a stable address works. This one is a list of ISO language codes, 184 rows and two columns, which is small enough to land in a sheet without argument:

=IMPORTDATA("https://raw.githubusercontent.com/datasets/language-codes/main/data/language-codes.csv")

This will promptly import all the necessary data into our spreadsheet.

Its pros and cons:

IMPORTDATA pulls a CSV or TSV straight off the web into Google Sheets. Its simplicity and effectiveness make it a valuable tool for tasks involving data from online sources.

IMPORTRANGE for Pulling In Another Sheet

The final formula on our list is IMPORTRANGE, which allows you to import data from other Google Sheets. This can be useful when you need to keep data from multiple sheets up-to-date in one file and refresh it periodically.

IMPORTRANGE consolidates data into one file and keeps it current. The syntax:

=IMPORTRANGE(spreadsheet_url, range_string)

This means that you need to specify the link to the document and the range of cells from which you want to import. As an example, let’s consider the previously created document for tracking positions and scraping Google SERP using Google Sheets and import the necessary data from it:

=IMPORTRANGE("https://docs.google.com/spreadsheets/d/1HLDrQFzN3cH-vHnBYHhqgiw3p4iUFCchqW0cuIoR2Y8/edit?gid=88338269#gid=88338269", "Sheet1!A1:E10")

The import lands as a live range. Editing the source sheet changes the destination on its next refresh, which is the behaviour that makes IMPORTRANGE worth the permission prompt.

Its pros and cons:

IMPORTRANGE is the one formula here that reads a Google Sheet rather than the web, and it is what turns several sheets into one view. It reads from as many spreadsheets as you point it at, and each one stays live, so the destination carries the most up-to-date information. However, it’s important to be aware of the limitations and potential for errors when using this function.