How to use importhtml function in Google Sheets

Importhtml is a function in Google Sheets that allows you to import tables and lists from any URL to Google sheets. It’s fairly simple to use but it’s application is restricted. Moreover, I don’t find the function very flexible. In this article I will explain how Imporhtml works and then list out the limitations.

Importhtml function in Google Sheets: How it works

Importhtml, just like Importfeed and Importxml, allows you to scrape and import content from web URLs to Google Sheets. The syntax of the function is as follows:

=importhtml(url, query, index)

URL: Mention the complete URL from where you want to fetch data. Ensure that the URL is wrapped in double quotes i.e. it’s passed as a string.
Query: Mention “table” if you want to import a table. Mention “list” if you want to import a list. No other value is accepted.
index: The index value (serial number) of the table or list. Could be a whole number.

Using importhtml to import a table:

Let’s understand using an example. Consider that we have to pull out the top-grossing hollywood movies in 2019 from the following URL : https://en.wikipedia.org/wiki/2019_in_film

When we look at the page we note that the data is present in the form of a table under the section titled “Highest Grossing Films” :

The table that we need to import using importhtml

We now know the URL to fetch the data from and we also know that the data is presented as a table. However, we don’t know yet the index number of the table.

To find the index number we could either use trial and error (try all numbers from 1 onwards till we get our data) OR we could use a smarter method.

The smarter method would be paste a JavaScript code in the browser console. To do that here’s what we need to do :
1. Open the URL
2. Right-click & select “Inspect Element”
3. Select the console tab
4. In the empty console paste the following code:

var i = 1; [].forEach.call(document.getElementsByTagName("table"),
   function(x) { console.log(i++, x); });

The code returns the list of tables ordered by index number. All we need to do is identify our target table:

Table list ordered by index number

In this case the table we are interested in has an index 4. We now have all the required values that we need to pass to the importhtml function. All we need to do next is write the formula in a google sheet (on cell A1):

importhtml function in action
importhtml function in action

That’s it! In one click our target table has been captured in our Google sheet.

Limitations of importhtml in Google Sheets:

The 2 key limitations of importhtml are as follows:

  1. Limited in capability: You may have noticed that importhtml’s capability is limited to importing tables and lists only. This limitation makes it’s usage kind of redundant considering that we have the option of using importxml function, which allows us to import almost any html element. If you want to learn more about importxml, do check out my article Web Scraping without coding – easiest way to build your own Web Scraper.
  2. Lack of flexibility: Identifying the index number of a list or a table is a tedious and convoluted task. This makes the function far less popular than imporxml, which provides you the flexibility to import an element by xpath.

FAQs : IMPORTHTML in Google Sheets

Which HTML elements can be scraped from an URL using IMPORTHTML ?

We can scrape lists & tables from an URL using IMPORTHTML.

How to scrape page title, H1 etc. from an URL using IMPORTHTML ?

Unfortunately you can’t scrape any element apart from lists and tables using IMPORTHTML. To scrape title , H1 etc. You need to use IMPORTXML function.

How do I find out the index number of a particular table or list ?

We can find out the index number either by trial & error or by using a JS code in the page console.

3 thoughts on “How to use importhtml function in Google Sheets”

  1. It’s actually a great and useful piece of information. I’m
    happy that you just shared this helpful information with us.

    Please stay us informed like this. Thank you
    for sharing.

  2. Thank you for every other informative blog. Where else may I am getting that kind
    of information written in such an ideal approach? I have a
    project that I’m just now running on, and I’ve been on the glance out for such information.

Leave a Reply

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