How to Use IMPORTXML in Google Sheets [Complete Guide]

If you are a data scraper and uses Google Sheets spreadsheets to store huge amount of data from the web, then the following article on how to use IMPORTXML in Google Sheets is useful to you. IMPORTXML is a built-in function of Google Sheets used to Import or lift data from a website. There are different kinds of uses of the IMPORTXML function depending on the type of data you are scraping from the web. Let’s keep an eye on the basic usage of the IMPORTXML function in Google Sheets through the following article.

Advantages of using IMPORTXML in Google Sheets

Data scraping or importing data from a website is always challenging in the traditional way of data scraping for a data scraper, sometimes, you may face data disorder, or it can also be time-consuming. While the IMPORTXML function can lift huge amounts of data within a few seconds in Google Sheets. IMPORTXML can Import various structured data types including XML, HTML, CSV, TSV, RSS, and ATOM XML feeds. Let’s see how we can use the IMPORTXML function to scrap all these types of data.

Syntax

=IMPORTXML(url, xpath_query)

• URL: This parameter defines the link of the web page you want the formula to examine. The value for the URL parameter should either be enclosed inside quotation marks or should contain a reference to the cell containing the URL.
• xpath_query: This is the query you wish to run on the data. XPath is used as an expression language that specifies parts of XML documents. The value in this parameter must be enclosed inside quotation marks. This can also be a reference to cells containing the text. We will discuss more about this below.

How to Use IMPORTXML in Google Sheets

You may use the IMPORTXML function of Google Sheets in various scenarios depending on your query. What type of data do you want to import from a website into Google Sheets? In the following tutorial, we will see some basic queries that are commonly used for data scraping by the IMPORTXML function in Google Sheets.

1. Import title & headings and subheadings from a website by the IMPORTXML function
2. Import table from a website by IMPORTXML function
3. Import links from a website by the IMPORTXML function
4. Import specific text from a website with the IMPORTXML function

Import title & headings and subheadings from a website by the IMPORTXML function

In this usage of the IMPORTXML function, we will be able to fetch the titles, headings, and subheadings of a website. In this method, we will discuss basic Xpath for the titles and headings as well so that we may easily import data by the IMPORTXML function.

Step 1

The first thing that we will have to do that, select the cell where you want to place the Imported data and run the IMPORTXML function with an equal sign as highlighted below.

Step 2

The first argument of the IMPORTXML function is the “URL” of the website so, go to the web page from where you want to import data and copy the URL from the URL bar as directed below.

Step 3

After copying the URL, come back to Google Sheets and paste your URL in the IMPORTXML syntax. This URL must be in quotation marks as I have written below.

Step 4

As we are looking for a website title, we will write the Xpath in the pattern as “//title” as I have written below. The Xpath must be in quotation marks as well.

Step 5

Your syntax for the IMPORTXML function has been completed, as you press the Enter key after closing the bracket, you will instantly get your result as I have gotten in the following picture.

Step 6

Now, let’s try for any heading or subheadings of the website. The URL will be the same just we will change the Xpath. In the following example, I am going to write the Xpath “//h2” as highlighted in the following picture.

Step 7

Pressing on the Enter key, the resultant will give you all the headings including subheadings regarding “<h2>” from the given website as you can see in the following picture.

Import table from a website by IMPORTXML function

Mostly, tables are imported from a website into Google Sheets, If a website contains a table and you want to import it to your Google Sheets then use the IMPORTXML function as follows.

Step 1

Here is a table regarding Bollywood movies that I want to import into Google Sheets from this website. Let’s see how can we do this with the IMPORTXML function.

Step 2

First, place your cursor where you want to Import the table from the website and start the formula of the IMPORTXML function by writing IMPORTXML with an equal sign as written below.

Step 3

As we know the first argument is the site URL, so first we will copy the URL from the web page.

Step 4

Then again relocate to Google Sheets and paste the copied URL into the IMPORTXML function with quotation marks.

Step 5

Commonly, the Xpath of the table in a website is denoted by “//tr” so here we will write the same pattern in our syntax with quotation marks.

Step 6

The result is in front of you, as you press the Enter key, the entire table will Imported into your Google Sheets.

Import links from a website by the IMPORTXML function

Most of the data scrapper needs internal and external links from a website, with the help of the IMPORTXML function, you can import all the links into Google Sheets within a few seconds. Let me show you practically in the following guide.

Step 1

First, start the formula of the Impotxml function in the cell where you want to import links in Google Sheets.

Step 2

Then again to enter the URL in the syntax, first, we will copy the URL from a website from the URL bar on your browser.

Step 3

After adding the URL, we will write “//@href” in place of XPath in the syntax as written below to import all the links from a website.

Step 4

All links applied on the selected website will be laid down here as you can see below. In this way, you can fetch all the links from any website in a jiffy.

Import specific text from a website with the IMPORTXML function

Till now, we have been learning to import titles, headings, tables, links, etc. But if you want to import a specific part of text from a website then what will you do? Read the following guide to find a way.

Step 1

Let’s suppose, the following text on a website, we want to import into Google Sheets,

Step 2

According to the syntax, simply first we will write the URL of the website after starting the syntax.

Step 3

Now, to find the next part of the syntax we will find for Xpath of the selected text, press write click of mouse on the text, and click on the “Inspect” option.

Step 4

As you click on the “Inspect” option, you will find the code for the text in the following box, Press the right click of a mouse on it, click on the “Copy” option then click on “Copy full Xpath“.

Step 5

Now, come back to Google Sheets, and paste the xpath in the syntax with quotation marks.

Step 6

You will get your text imported into Google Sheets by pressing the Enter key as can be seen below.

Note: This method does not work for most websites due to the invalidity of Xpath, in such cases, you will need to use a third-party app to find the exact Xpath of the text.

Frequently Asked Questions

Can I Use the Google Translate Function in Google Sheets to Translate Imported XML Data?

Yes, you can utilize the Google Translate function in Google Sheets to seamlessly translate imported XML data. Google translate in sheets enables swift and efficient translation of imported XML data, ensuring you can overcome language barriers and work with multilingual content seamlessly.

Can I Use the IMPORTXML Function to Pull Data from Websites into Google Sheets?

Yes, the IMPORTXML function in Google Sheets enables easy data extraction from websites. With google sheets web scraping, users can gather specified data by inserting the appropriate XPath query. This function is valuable for automating data collection and keeping spreadsheets up to date with real-time information.

Can I Use the INDIRECT Function in Google Sheets with IMPORTXML?

Yes, you can utilize the google sheets indirect function in conjunction with IMPORTXML. By using the google sheets indirect function, you can refer to a cell containing the IMPORTXML formula and dynamically generate the desired XML data. This enables a flexible way to import specific data from the web into your Google Sheets.

Conclusion

That’s all from how to use IMPORTXML in Google Sheets. After learning the use of the IMPORTXML function through the above article on how to use IMPORTXML in Google Sheets, you should thoroughly understand how to scrape XML data from websites into Google Sheets. Hope it will find helpful to you.