Mastering Google Sheets’ IMPORTHTML Function (Versus Excel’s Web Scraping Techniques)

Mastering Google Sheets' IMPORTHTML Function (Versus Excel's Web Scraping Techniques)

Google Sheets offers multiple functions for web scraping. IMPORTHTML function is the most useful and straightforward function to import data. This function imports an entire table or list from web pages directly into the spreadsheet. Excel has multiple techniques for web scraping such as Power Query and VBA (Visual Basic Applications). This post will show you how to use the IMPORTHTML function for web scraping while comparing Excel’s web scraping techniques.

What is IMPORTHTML Function?

This function imports an entire table or list within an HTML page. To import data, you will need to specify the URL, the query type (either table or list), and the index of the table/list. If you want, you can use the locale code to specify the language and region importing the data. 

Syntax:

=IMPORTHTML(URL, query, index, locale)

  • URL: The webpage URL to import data, including protocol (e.g. ‘http://’).
  • query: depending on the webpage data structure specify whether to import “table” or “list”.
  • index: The index number (starts from 1) identifies which table, or list will be imported from the webpage HTML source.
  • Locale [optional]: A language and region locale code to use when parsing the data. If unspecified, the document locale will be used.

Using IMPORTHTML function for Web Scraping

Suppose you want to import the list of major world payment currencies from Wikipedia in the Google spreadsheet.  You can use the IMPORTHTML function to import that list from Wikipedia. To do so, select any cell (A1) and use the following formula.

Formula:

=IMPORTHTML(“https://en.wikipedia.org/wiki/Currency”, “table”, 7, “en_Us”)

Explanation: 

  • “https://en.wikipedia.org/wiki/Currency”: URL of the webpage to import the required data.
  • “table”: The type of query is a table so it will import the table from the webpage.
  • 7: It’s the index number of the table of the HTML page.
  • “en_Us”: locale is English for the United States.

Formula Output:

Rank Currency July 2024
  *World* 100.00%
1 United States dollar 47.81%
2 Euro 22.47%
3 Pound sterling 7.00%
4 Chinese renminbi 4.74%
5 Japanese yen 3.62%
6 Canadian dollar 2.42%
7 Australian dollar 1.80%
8 Hong Kong dollar 1.63%
9 Singapore dollar 1.34%
10 Swiss franc 0.92%
11 Swedish krona 0.80%
12 Polish złoty 0.70%
13 Norwegian krone 0.64%
14 Thai baht 0.40%
15 Danish krone 0.37%
16 New Zealand dollar 0.37%
17 Mexican peso 0.31%
18 South African rand 0.29%
19 Philippine peso 0.28%
20 Chilean peso 0.23%

Example: Import Specific Data from a Table or List while Web Scraping

Consider a scenario where you want to import 10 rows from a webpage but the table has a large number of rows. You can do it by combining the IMPORTHTML with the QUERY function in Google Sheets. The QUERY function will allow you to limit the data by using the Limit clause. Select cell A1 and insert the following formula.

Formula:

=QUERY(IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_countries_and_dependencies_by_population”, “table”, 1), “SELECT * LIMIT 10”, 1)

Explanation:

  • IMPORHTML: Imports the entire table from the list of countries and dependencies by population webpage of Wikipedia. This table has 242 rows.
  • QUERY: Queries the entire table and extracts only 10 rows.
  • “SELECT * LIMIT 5”: SELECT clause extracts all columns (*) and the LIMIT clause limits the output to the first 10 rows.
  • 1: It specifies the number of headers at the top of the table.

Formula Output:

  Location Population % of world Date Source (official or from the United Nations) Notes
  * World* 8,127,721,171 100% 30 Aug 2024 UN projection[3]  
1 China 1,409,670,000 17% 31 Dec 2023 Official estimate[5] [c]
2 India 1,404,910,000 17% 1 Jul 2024 Official projection[6] [d]
3 United States 335,893,238 4% 1 Jan 2024 Official estimate[7] [e]
4 Indonesia 281,603,800 4% 1 Jul 2024 National annual projection[8]  
5 Pakistan 241,499,431 3% 1 Mar 2023 2023 census result[9] [f]
6 Nigeria 223,800,000 3% 1 Jul 2023 Official projection[10]  
7 Brazil 203,080,756 3% 1 Aug 2022 2022 census result[11]  
8 Bangladesh 169,828,911 2% 14 Jun 2022 2022 census result[12]  
9 Russia 146,150,789 2% 1 Jan 2024 Official estimate[13] [g]

Excel’s Web Scraping Techniques

Excel offers several basic to advanced techniques for web scraping, primarily through Power Query and VBA. You can use the Power Query to import data from different web pages. It’s easy to use for both basic and advanced users.

Using Power Query for Web Scraping in Excel

It can connect to web pages and import data like IMPORTHTML. However, it has both basic and advanced options that will allow you to decide how you want to import data into the sheet. It offers robust data transformation.

To use Power Query,

  • Go to Data tab >> select From Web >> place the URL >> click OK.

Mastering Google Sheets' IMPORTHTML Function (Versus Excel's Web Scraping Techniques)

Here, an index number is not required. It shows all the tables of the web page you can preview it from both table view and web view.

  • Now, select the table from where you want to import the data then click on Load.

Mastering Google Sheets' IMPORTHTML Function (Versus Excel's Web Scraping Techniques)

Power Query Output:

Column1 Column2 Column3
Rank Currency July 2024
  World 100.00%
1 United States dollar 47.81%
2 Euro 22.47%
3 Pound sterling 7.00%
4 Chinese renminbi 4.74%
5 Japanese yen 3.62%
6 Canadian dollar 2.42%
7 Australian dollar 1.80%
8 Hong Kong dollar 1.63%
9 Singapore dollar 1.34%
10 Swiss franc 0.92%
11 Swedish krona 0.80%
12 Polish złoty 0.70%
13 Norwegian krone 0.64%
14 Thai baht 0.40%
15 Danish krone 0.37%
16 New Zealand dollar 0.37%
17 Mexican peso 0.31%
18 South African rand 0.29%
19 Philippine peso 0.28%
20 Chilean peso 0.23%

VBA (Visual Basic for Applications): 

VBA will allow you all sorts of customization in web scraping. However, you will need prior knowledge of programming to use VBA.

Conclusion

Google Sheets’ IMPORTHTML is much easier and quicker to use though Excel’s techniques offer more advanced features with the flexibility to import data. IMPORTHTML will be best to import straightforward tables or lists. In complex scenarios, you may use Excel’s techniques for web scraping. Overall, the selection between Google Sheets’ IMPORTHTML and Excel’s web scraping techniques will depend on your needs and the complexity of the webpage data.

One Reply to “Mastering Google Sheets’ IMPORTHTML Function (Versus Excel’s Web Scraping Techniques)”

Leave a Reply

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