How to Use Google Sheets’ IMPORTXML Function for Web Scraping (Versus Excel’s Power Query)

How to Use Google Sheets' IMPORTXML Function for Web Scraping (Versus Excel's Power Query)

Both Google Sheets’ IMPORTXML function and Excel’s Power Query are powerful tools for web scraping. It’s faster and easier to scrape data by using the IMPORTXML function. For complex web scraping Power Query offers robust functionality and features. In this post, we will show you the use and difference between Google Sheets’ IMPORTXML function and Excel’s Power Query with practical examples.

What is the IMPORTXML Function in Google Sheets?

The IMPORTXML function extracts structured data from web pages using XPath queries into Google Sheets. The XPath expressions are used to locate specific elements within the HTML of the page. Its structured data types include XML, HTML, CSV, TSV, RSS, and Atom XML feeds.

Syntax:

=IMPORTXML(URL, xpath_query, locale)

  • URL: The URL of the web page to import data.
  • xpath_query: The XPath expression is used to identify the data that you want to import. It runs on structured data.
  • locale: It’s a language and region locale code to use when parsing the data. If unspecified, the document locale will be used.

Commonly used XPath queries are:

  • //a/@href: Extracts all hyperlinks (anchor tags).
  • //h1, //h2, //h3: Selects heading tags (common for titles).
  • //img/@src: Extracts image sources (useful for getting image URLs).
  • //ul/li: Selects list items in unordered lists.
  • //ol/li: Selects list items in ordered lists.
  • //p: Select all paragraphs.
  • //span: Selects all span elements.
  • //div: Selects all div elements (useful for structured content).

What is a Power Query in Excel?

Excel’s Power Query is a data transformation tool that allows you to import, clean, and transform data from various sources, including the web. Power Query offers robust features and it’s useful for scraping dynamic web content.

Using Google Sheets’ IMPORTXML Function and Excel’s Power Query for Web Scraping

Consider a scenario where you want to import web page data without using the copy-paste method. Suppose you want to import stock prices from stock markets to see the current stock price. We will show you how you can easily do it by using the IMPORTXML function and Power Query tool.

Google Sheets’ IMPORTXML Function:

Let’s scrape the stock price of GOOGLE. To import the livestock price from Yahoo Finance you can use the following formula. Select cell A1 and insert the following formula.

=IMPORTXML(“https://finance.yahoo.com/quote/GOOGL/”, “/html/body/div[1]/main/section/section/section/article/section[1]/div[2]/div[1]/section/div/section[1]/div[1]/fin-streamer[1]/span” , “en_US”)

Explanation:

  • “https://finance.yahoo.com/quote/GOOGL/”:  This is the URL of the Yahoo Finance page for Google’s stock (GOOGL).
  • “/html/body/div[1]/main/section/section/section/article/section[1]/div[2]/div[1]/section/div/section[1]/div[1]/fin-streamer[1]/span”: The XPath query to import the stock price of GOOGLE. It navigates through the HTML structure from the root (/html/body/div[1]/…) to get the price of Google’s stock.
  • “en_US”: It’s the locale code of the USA.

Output: The IMPORTXML function will scrape only the stock price of Google stocks as I used the exact XPath of live price. 9th October 2024 the price is 150.92.

How to Get the XPath:

To get and copy the XPath you will need to use the browser’s developer tools, follow the steps given below:

  • Go to the web page >> Right-click on the mouse >> select Inspect.

How to Use Google Sheets' IMPORTXML Function for Web Scraping (Versus Excel's Power Query)

Now, hover over the data you want to export.

  • Select the Elements >> Right-click on the mouse >> click on Copy >> choose Copy full XPath.

How to Use Google Sheets' IMPORTXML Function for Web Scraping (Versus Excel's Power Query)

Now you can use the copied XPath in the IMPORTXML function to import any data from the web page. Here, we imported the stock price of Google.

Excel’s Power Query:

You can use the Power Query tool to import the stock price of GOOGLE from Yahoo Finance. Follow the steps:

  • Go to the Data tab >> select From Web >> insert the URL in the URL box >> click on OK. 

How to Use Google Sheets' IMPORTXML Function for Web Scraping (Versus Excel's Power Query)

  • Now, you’ll see a list of tables available on that web page. Select the relevant table that contains the stock price or other financial data. Here, select Table 2 >> click on Load.

How to Use Google Sheets' IMPORTXML Function for Web Scraping (Versus Excel's Power Query)

Final Output: Power Query will scrape the entire Table 2. You can’t directly scrape only the Google stock price. Later you can transfer the data as your choice.

How to Use Google Sheets' IMPORTXML Function for Web Scraping (Versus Excel's Power Query)

Conclusion

Google Sheets’ IMPORTXML function is useful while working with relatively simple web pages with static data. If you know the expression of XPATH queries, then it is easy to web scrape data by using IMPORTXML. Also, you can fetch any data you need rather than scraping the entire table or list. For advanced web scraping you can use Excel’s Power Query. Based on the complexity of the web page data and web scraping type you can choose the tools you’re more comfortable using.

Leave a Reply

Your email address will not be published. Required fields are marked *