How to Use IMPORTXML in Google Sheets

Want to import web data into Google Sheets? Here’s a comprehensive guide on how to use IMPORTXML in Google Sheets.

The traditional way to pull data from a website is to manually copy-and-paste it into your Google spreadsheet.

But why waste all that time when the IMPORTXML function in Google Sheets does it much faster?

IMPORTXML retrieves publicly available data from the web and imports it into Google Sheets.

Read the following guide to learn everything you need to know about IMPORTXML, based on step-by-step walkthroughs and real examples.

Video Walkthrough: How to Use IMPORTXML in Google Sheets

What is the IMPORTXML Function in Google Sheets?

The IMPORTXML function enables you to import publicly accessible data from websites, XML documents, and other spreadsheets into Google Sheets.

The function can import various structured data types into Sheets, including HTML, TSV, CSV, and XML. The function can also perform web scraping and data mining by pulling data from RSS and ATOM XML feeds.

IMPORTXML requires the URL of the XML file you want to import. The IMPORTXML syntax is:

=IMPORTXML(URL, xpath_query)

Here’s a definition of the parameters:

• URL is the website address that contains the data you want to extract. Enclose this value in double quotation marks.

• xpath_query refers to the data you want to retrieve. For instance, if you use “//h2/@title” as your xpath_query parameter, the formula returns all <h2> tags on the source webpage.

XPath is the path language used for XML. The language is adept at extracting various parts of XML files and documents.

How to Import Data into Google Sheets with IMPORTXML

Use Case #1: Import H3 Subheadings into Google Sheets

Let’s start by going over how you can pull data on websites into Google Sheets by using IMPORTXML.

First, open the spreadsheet you want to import data into. Then open the website containing the data you want to import.

For this example, let’s extract data from one of our own blog posts. How about Top 7 Apps for Salesforce Exchange?

Let’s suppose you want to import the H3 subheadings from the blog. Your formula will look like this:

=IMPORTXML(“https://coefficient.io/salesforce-appexchange”,”//h3″)

The IMPORTXML function will fetch all the elements with the H3 tag from the blog, as shown below.

And voila — all of the h3 tags now appear in separate cells within your spreadsheet. This is what the function is ideal for: pulling a limited amount of web data into your spreadsheet.

Use Case #2: Pull Data Table into Google Sheets

With IMPORTXML, you can retrieve data from a table on a website and import it into Google Sheets quickly. Let’s pull data from this table of new cryptocurrencies on the coingecko.com website.

Right-click on the table and select the Inspect option at the bottom. Note: We’ll be using the Google Chrome browser for this example.

You should see the page’s source code in a sidebar on the right side of your screen, showing the XML data.

Hover your cursor over the elements so the browser highlights the table, and you’ll see the specific tag.

Look at the code to find the HTML elements associated with your data. The data in this example is encoded in HTML via the table rows tag, or <tr>.

In your IMPORTXML formula, specify your xpath_query as //tr to extract all data associated with <tr> tags. Your formula will look like this:

=IMPORTXML(“https://www.coingecko.com/en/new-cryptocurrencies”,”//tr”)

The data from the table will populate in your spreadsheet.

Free AI-Powered Tools Right Within Your Spreadsheet

Supercharge your spreadsheets with GPT-powered AI tools for building formulas, charts, pivots, SQL and more. Simple prompts for automatic generation.

This is a good example of how to use IMPORTXML to import tables into Google Sheets using HTML elements.

Similar Functions in Google Sheets

Besides IMPORTXML, you can use other functions to retrieve web page data and import it into Google Sheets, depending on your use case.

IMPORTDATA Function

The IMPORTDATA function allows you to import data from publicly accessible CSV or TSV files on the web into Google Sheets.

The function lets you retrieve data quickly with a single formula, saving you from manual copy-paste jobs.

Read our guide on how to use IMPORTDATA in Google Sheets to learn more about the function.

IMPORTFEED

The IMPORTFEED function lets you pull data from publicly accessible ATOM or RSS feeds into Google Sheets.IMPORTFEED automatically imports data updates from the feeds you specify.

IMPORTFEED formulas must include the ATOM or RSS feed URL, the spreadsheet you want to pull data into, and the column to receive the data.

GOOGLEFINANCE Function

The GOOGLEFINANCE function in Google Sheets imports financial data from Google Finance into your spreadsheet.

The function pulls financial data in Google Sheets, such as currency conversion rates, historical data for securities, and current stock prices.

The function requires the ticker symbol of the company you want to pull data from.

Coefficient: Alternative Way to Pull Data into Sheets

The IMPORTXML function is fine for mining, scraping, and importing data from the web.

However, it’s not always the most practical solution, especially when you want to pull large datasets. A more efficient approach is to use data connector tools.

Coefficient enables you to import real-time data from Salesforce, Shopify, HubSpot, MySQL, and more, into Google Sheets easily.

Read our blog to learn how to import live data into Google Sheets with Coefficient in a single click.

Important Factors: Google Sheets IMPORTXML Function

Here are some important factors you need to keep in mind when using IMPORTXML in Google Sheets.

• Only HTML & XML work. IMPORTXML can only import data from HTML and XML documents. The function can’t pull data from other document types, such as JSON or CSV files.

• The data isn’t refreshed. IMPORTXML pulls data from your source URL only once. To refresh the data, you’ll need to manually re-enter the formula or use Google Apps Script to write a function.

• Websites can block the function. Some websites block any scraping, which can stop the IMPORTXML function from pulling data into Google Sheets.

• Can only import publicly accessible data. IMPORTXML can only fetch data from publicly accessible websites, which means it can’t retrieve data from sites that require logins or authentication.

For these reasons, IMPORTXML is only ideal for usage in certain cases that involve one-time imports of web data.

IMPORTXML: Pull Web Data into Google Sheets

IMPORTXML is a useful Google Sheets function that allows you to extract data from web pages and import it into your spreadsheet easily.

With a bit of XPath and web page structure knowledge, you can leverage the function to gather the web data you want.

But for many use cases, you may need to turn to an app such as Coefficient to get the job done. Coefficient can import data from a wide variety of business systems, pull larger datasets, and perform automatic data refreshes.

Try Coefficient for free to automate spreadsheet processes and pull the data you need into Google Sheets.