How To Use IMPORTXML in Google Sheets

Reading Time: 3 minutes

What it does – Scrape publicly available structured data from webpages.

Syntax
=IMPORTXML(url, xpath_query)

url: The URL of the page to import data from. The URL can either be enclosed in quotation marks, or be a reference to a cell containing the URL.

xpath_query: The argument asks for the XPath query to be run on the data.

Sample Usage
=IMPORTXML("https://en.wikipedia.org/wiki/Game_theory", "//@href")

//Scapes all links and import them to Google Sheets from

Objective

In this Google Sheets guide, you will learn how to use IMPORTXML in Google Sheets

Why Use IMPORTXML in Google Sheets?

There are times when you want to scrape data tables and on-page elements from the internet. You can do this manually by copy-pasting the data, but it is a tedious task. IMPORTXML command helps you scrape data from websites. The function can import data from various data types such as XML, CSV, HTML and TSV. The function can pull data from anything enclosed in HTML tags. 

Syntax

=IMPORTXML(url, xpath_query)

  • URL: The argument asks for the URL of the website you want to import data from. The URL can either be enclosed in quotation marks, or be a reference to a cell containing the URL.
  • xpath_query: The argument asks for the XPath query to be run on the data.

How to Use IMPORTXML in Google Sheets?

Let’s review the example on how to import xml data in google sheets.

Example 1: Import all H2 headings

  • Open Google Sheets
  • Select a cell in which you want to import data.
  • Type the following command:

=IMPORTXML(“https://blog.tryamigo.com/”,”//h2″)

  • Here, the URL is the first parameter.
  • In the second parameter, write an XPath query, where you want to extract all the H2 headings.
  • As soon as you hit enter, the command will start loading, as shown below:
Loading the results of IMPORTXML command
Figure 1: Loading the results of IMPORTXML command

  • The output of the command is:
Output of all H2 tags
Figure 2: Output of all H2 tags

Example 2: Import Data from Header tag

  • Open Google Sheets
  • Select a cell in which you want to import data.
  • Type the following command:

=IMPORTXML(“https://blog.tryamigo.com/”,”//header”)

  • Here, the URL is the first parameter.
  • In the second parameter, write an XPath query, where you want to extract all the data from the header tag.
  • The output of the command is:
The output of data in header tags
Figure 3: The output of data in header tags

Example 3: Import Data from Class Tag

  • Open Google Sheets
  • Select a cell in which you want to import data.
  • Type the following command:

=IMPORTXML(“https://blog.tryamigo.com/”,”@class=’wp-container-6242aefb26621 wp-block-group’”)

  • Here, the URL is the first parameter.
  • In the second parameter, write an XPath query, where you want to extract all the data from the class tag.
  • The output of the command is:
The output of data in particular class tag
Figure 4: The output of data in particular class tag

See Also

Most Commonly Used Google Script Functions for 2022: Learn about widely used advanced script functions.

How to use Filter views in Google Sheets | Simple 2-min Guide 💻: Learn how to use Filter view in Google Sheets and its variations.

How to import JSON in Google Sheets : Learn How to import JSON in Google Sheets in 2 min.

X
Hire expert data analysts on-demand. Get 30 Days Free Trial