How to Use IMPORTHTML Function in Google Sheets [Advanced Use]

0
161
How to Use IMPORTHTML Function in Google Sheets [Advanced Use]

I think the IMPORTHTML function in Google Sheets is very useful for collecting data form external webpages like Wikipedia for creating different charts as well as presentations. Why this function is required to fetch data from webpages? Can’t we copy and paste data directly to Google Sheets?

When you try to copy and paste data from a table that you found on any webpages into Google Sheets, it may loss its tabular form. You may need to use Google Sheets Split function to make the pasted data again look like a table. It may consume your lots of time and energy.

Here comes the importance of Google Sheets IMPORTHTML function. I am going to explain here, how to use IMPORTHTML function in Google Sheets. What more! I am going to tell you how to tame the imported table with Google Sheets QUERY function. It’s very simple to learn. So let’s begin.

How to Use IMPORTHTML Function in Google Sheets

Purpose of IMPORTHTML:

The one and only purpose of Google Sheets IMPORTHTML Function is to import table or list in to 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 occur to the imported data in Google Sheets.

Syntax:

IMPORTHTML(url, query, index)

1. “url” is the URL of the webpage that containing the table or list.

2. “query” means whether you want to import a list or table.

3. “index” is the index number which starting at 1. Here 1 means first table or first list, 2 means second table or second list and so on. Remember! There may be both a list and a table with index 1.

Table and List:

In concise, with IMPORTHTML formula in Google Sheets;

1. We can import <tables>

2. We can import lists that are created using the <ol> or <ul> tags.

Screenshot 1

identify list and table in a webpage for importhtml

Google Sheets IMPORTHTML Formula Example

Import a Table Using IMPORTHTML in Google Sheets.

Let us see how to import a table using IMPORTHTML Formula in Google Sheets.

=IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)“,”table“,2)

Point to be noted: URL and query should be within quotes

See the Screenshot 1 above and the formula. The above IMPORTHTML formula would return the table # 2 from the webpage. The result would be as follows.

Screenshot 2

how to use Google Sheets IMPORTHTML formula

Import a List Using IMPORTHTML in Google Sheets

Similarly above you can use the IMPORTHTML function to import a list. Only change the query from table to list. See the formula.

=IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)“,”list“,1)

Here again see the Screenshot 1. Then the result below.

Screenshot 3

import a list in to google sheets from web pages

How to Use Google Sheets IMPORTHTML Formula with Query Function – Purpose and Examples.

By using Query function together with IMPORTHTML, we can control the imported table. Here is few examples.

See the above screenshot 2. You can see that Google Sheets IMPORTHTML function, which we’ve used there, imported 7 columns. By wrapping the above formula with a QUERY formula, we can control the number of columns imported.

How to limit the number of columns imported by IMPORTHTML formula

Here I am importing only 5 columns from Column 2 to Column 6. I’ve highlighted the changes in the formula.

=query(IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)”,”table”,2),”Select Col2,Col3,Col4,Col5″)

How to Apply Filter in IMPORTHTML function in Google Sheets

Actually the Wikipedia URL which we’ve used in all the above examples point to a page that containing List of all the world countries by its population. If you visit that page or check our screenshot 1 above, you can see the column labels as below.

Screenshot 4

use of query function with importhtml

We are now filtering the imported data based on column 3, i.e., UN continental region. When you want import data specific to UN continental region “Asia”, use the formula as below.

Screenshot 5

query function with filtering in importhtml

Formula:

=query(IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)”,”table”,2),”Select * where Col3=’Asia'”)

How to limit the number of rows imported by IMPORTHTML formula

When you want Google Sheets IMPORTHTML function to return only limited number of rows, you can use the formula as below.

Here I am using the above same formula to return only 5 rows. The formula would be as below.

=query(IMPORTHTML(“https://en.wikipedia.org/wiki/List_of_countries_by_population_(United_Nations)”,”table”,2),”Select * where Col3=’Asia’ limit 5“)

That’s all for now. Hope you have enjoyed the above advanced IMPORTHTML function tutorial.

LEAVE A REPLY

Please enter your comment!
Please enter your name here