You can Import Tables and Lists into Google Sheets using the IMPORTHTML function. Then how to use the IMPORTHTML Function in Google Sheets? The IMPORTHTML function in Google Sheets is handy for collecting data from external web pages like Wikipedia. You can use such data to create different charts as well as presentations.
Why is this function required to fetch data from web pages? Can’t we copy and paste the data directly into Google Sheets? When you copy-paste data from a table that you found on any web page into Google Sheets, it may lose its tabular form. You may need to use Google Sheets Split function to make the pasted data look like a table again. It may consume lots of time and energy. Here comes the importance of the Google Sheets IMPORTHTML function.
I am explaining below how to use the IMPORTHTML function in Google Sheets. What more! You can also learn how to tame the imported table with the help of the Google Sheets QUERY function. It’s easy to learn. So let’s begin.
How to Use IMPORTHTML Function in Google Sheets
Purpose of IMPORTHTML:
The only purpose of the Google Sheets IMPORTHTML function is to import tables or lists into Google Sheets from external websites or blogs. When the data on the external webpage updates at any time in the future, the same changes will also occur to the imported data in Google Sheets.
Syntax: IMPORTHTML(URL, query, index, locale)
- “url” is the URL of the webpage that containing the table or list.
- “query” means whether you want to import a list or table.
- “index” is the index number that starts at 1. Here 1 means the first table or the first list, 2 means the second table or the second list, etc.
- “locale” is to specify a language and region locale code such as “en_US” to use when parsing the data. If you leave it, the function will use the document locale.
Please remember that, on a web page, there may be a list as well as a table with index # 1.
Table and List:
In concise, with the help of the IMPORTHTML formula in Google Sheets, we can import the following.
- We can import
- We can import lists that are created using the
- or
- tags.
Don’t get confused by these technical terms. You only want to use table 1, table 2, list 1, list 2, etc., in the formula. I assure you, from my examples, you can learn the use of the function without any issue.
Google Sheets IMPORTHTML Formula Examples
First of all, let’s import a table into Google Sheets using the said function.
Import a Table Using IMPORTHTML
Let us see how to import a table using an IMPORTHTML formula in Google Sheets. Here is the formula.
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)","table",1)See the function syntax once again and the color pattern.
IMPORTHTML(URL, query, index)
Note:- The URL and Query should be within double-quotes. See “Screenshot 1” above and the formula.
The above IMPORTHTML formula would return table # 1 from the webpage. The result would be as follows.
Update:- The table in the source seems updated and moved. So I have updated the above formula but not the screenshot below.
Import a List Using IMPORTHTML
Similarly, you can use the IMPORTHTML function to import a list. The only change is in the query argument. It’s “list” now.
See one formula as an example of importing a list into Google Sheets.
=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)","list",1)Here again, please refer to “Screenshot 1”. Then the below result.
Update:- If the content on the website is different in the future, you might get a different result at that time.
How to Use Google Sheets IMPORTHTML Formula with Query Function – Purpose and Examples
We can use the Query function together with IMPORTHTML to control the imported table. For example, if the imported table has seven columns, you can limit the columns between 1 to 7. Also, you can control the total number of rows to import. Here are a few examples.
Please refer to “Screenshot 2” above. You can see that the Google Sheets IMPORTHTML function, which we’ve used there, imported seven columns. We can control the number of columns imported by wrapping the above formula with a QUERY formula. Below is that tip.
How to Limit the Number of Columns Imported by the IMPORTHTML Formula
Here I am importing only four columns from column 2 to column 5. I’ve highlighted the changes in the formula.
=query(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)","table",1),"Select Col2,Col3,Col4,Col5")How to Apply Filter in IMPORTHTML Function in Google Sheets
The Wikipedia URL directs to a page that contains a list of all the world countries by their population. If you visit that page or check “Screenshot 1” above, you can see the column labels as below.
We are now filtering the imported data based on column 2, i.e., the UN continental region. When you want to import data specific to the UN continental region “Asia,” you may use the formula in the below format.
Note:- When I check the webpage on 16-12-2021, the “UN continental region” is the second column in the table. I have updated the formulas accordingly.
Formula:
=query(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)","table",1),"Select * where Col2='Asia'")How to Limit the Number of Rows Imported by the IMPORTHTML formula
You can control the Google Sheets IMPORTHTML function to return a limited number of rows. For that purpose, you can use the formula as below.
Here I am using the above same formula to return only five rows. It would be as below.
=query(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)","table",1),"Select * where Col2='Asia' limit 5")That’s all for now. I hope you have enjoyed the above advanced IMPORTHTML function tutorial. Check out Crawlan.com for more exciting articles and tutorials.
