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” :
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:
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):
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:
- 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.
- 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
We can scrape lists & tables 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.
We can find out the index number either by trial & error or by using a JS code in the page console.
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.
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.
I truly appreciate this post. I’ve been looking everywhere for this! Thank goodness I found it on Bing. You’ve made my day! Thx again!