HomeGoogle DocsSpreadsheetHow to Use IMPORTHTML Function in Google Sheets

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

Published on

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)

  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 that starts at 1. Here 1 means the first table or the first list, 2 means the second table or the second list, etc.
  4. 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.

  1. We can import <tables>
  2. We can import lists that are created using the <ol> or <ul> 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.

Screenshot 1

identify list and table in a webpage for importhtml

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.

Screenshot 2

how to use Google Sheets IMPORTHTML formula

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.

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

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.

Screenshot 4

use of query function with importhtml

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.

Screenshot 5

query function with filtering in importhtml

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.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

23 COMMENTS

  1. How to Importhtml table data from date ranges?

    I want only dates between 25 to 31. Data tables have multiple months and multiple years.

    • Hi, Dhiru,

      The returned dates might be text formatted. So without seeing the data, I can’t suggest a formula.

      Please share a copy of your Sheet so that I can further assist.

  2. Hi Prashanth!

    Once again, thank you for your tutorial.

    I will need a minor variation in the above formula.

    I have a list of 10 hyperlinks in column B. Each link has a table with five rows and three columns.

    How do I manipulate the above formula so that I can extract all the 50 rows (i.e., five rows per entry, 5*10=50 rows)?

    Thanks in advance!

  3. Thank you. The date in this URL:

    =IMPORTHTML("URL;begindate=6/8/2020","table",2)

    is actually the “Ex-Date” listed on the table. As I mentioned earlier, I have to manually change the date in the URL to the date after tomorrow in order to bring the table on which the “Ex-Date” is the date after tomorrow.

    I used this new formula:

    =IMPORTHTML("URL;begindate="&today()+2,"table",2)

    but the new formula imports the table in which the Ex-Date is today date – not the date after tomorrow. Any clue?

  4. Hello Prashanth,

    I used this formula to import a table into my google sheets.

    =IMPORTHTML("URL_Here;begindate=6/8/2020","table",2)

    There is a date (6/4/2020) in the URL that I have to change every day manually to update the table to the date after tomorrow. Is there a way to change the date in the URL to the date after tomorrow automatically?

    • Hi, Roycey,

      In the below formula you can control the date by changing the formula part today()+2.

      =IMPORTHTML("URL_Here;begindate="&today()+2,"table",2)

      Best,

  5. Hello Prashanth,

    Thank you very much for the tutorial. I found it very informative.

    I am currently receiving this error message “Resource at URL contents exceeded maximum size”. I even receive this error message after trying to specify query and filter parameters. By chance do you know what the size limitations are? Or perhaps a workaround to this error message?

    Thank you in advance for any and all help.

  6. Hi Prashant,

    Thanks for the article.

    Need your support to address the below issue.

    I have created a script to download and update the option chain at every 5 min.

    The script runs every 5 mins but most of the time data is Old, how can I resolve this.

  7. Hey, thanks for the article!

    I’m trying to convert that page into a sheet with no success, any idea what I’m doing wrong?

    =IMPORTHTML("https://crates.co/crate/spotify-labels/4720/feed","table",1)

    Thanks!

  8. Hello Prashanth,

    I am trying to fetch the table and it is working fine but don’t know how to select the timeframe in the query.

    Please can you have a look on this:

    “https://www.investing.com/technical/pivot-points”

  9. Hey, maybe you can help, I’m trying to get the days, hours and minutes from this website.

    “https://www.cmegroup.com/trading/interest-rates/countdown-to-fomc.html”

    But I don’t think its a list or a table, any suggestions?

  10. I have problems using select rows.
    I have this

    =IMPORTHTML("https://tweakers.net/pricewatch/1129515/nzxt-n7-z370-wit.html";"table";3)

    That works, but now I want column 1 row 1 and column 4 row 1.

    =query(IMPORTHTML(“https://tweakers.net/pricewatch/1129515/nzxt-n7-z370-wit.html”,”table”,2),”Select Col1,Col4″)

    It does not work. Is it also possible to make it grab column 1 row 1 and column 4 row 1?

    • Hi Dennis,

      You can do that.

      I think there is a typo in your second formula with the table number. OK let’s leave that aside.

      You can use the Query limit clause in this case.

      =query(IMPORTHTML("https://tweakers.net/pricewatch/1129515/nzxt-n7-z370-wit.html","table",3),"Select Col1,Col4 limit 1")

      Hope this may help.

  11. Please tell me how to fetch full table having more than 50 rows. I can fetch only the first 25 rows displayed on url webpage and remaining rows of table on same url ….i m unable to fetch using IMPORTHTML.

    • Hi Ajay,

      I didn’t face any such issues so far.
      The problem may be associated with webpage you are using.
      I’ve sent a mail to you. If possible share me the link. So that I can test it.

      Thanks.

  12. HI,

    Thank you for the article above. It helped to create a google sheet to download the futures prices from NSE India website.
    Can you also advise as to how to fetch the data at regular interval from the nse web site in this google sheet?

    Thanks

LEAVE A REPLY

Please enter your comment!
Please enter your name here