We all know how resourceful Google Sheets application has become in our daily life. While it is extremely good with all the basic and advanced spreadsheet needs, it also lets us connect with the outside information with ease. For instance, if we need to import a web HTML table to Google Sheets, it has a native function called as IMPORTHTML formula. Here’s the syntax:
IMPORTHTML(url, query, index)
- url – this is the address (link/URL) to the HTML page on the web, where the table or list that we are looking to import is located. This can either be a fully qualified URL string like “https://en.wikipedia.org/wiki/Demographics_of_the_world”. Or it can be a reference to a cell (like B1) where the URL is stored.
- query – this will be either “list” or “table” depending upon which HTML structure contains the data we are trying to import.
- index – the index number that uniquely identifies the list or table within the HTML page. Please note that the indexes for both lists and tables are treated separately. Both a table and list can exist with the same index number.
Example: import a web HTML table to Google Sheets
Please consider the image below. B3 is the cell where we are entering the IMPORTHTML formula. On the web page where the URL (mentioned above) takes us, we are rather interested in the second table. Hence we’re going to use “table” for query parameter and 2 for the index parameter.
We make sure that the url is within double quotes and hit the Enter key. As soon as we do that, Google Sheets tries to fetch the data. Bigger the table data, the longer it might take to finish the data import.
While the data import is in progress, the cell B3 looks like it is holding an error value. Please note a little red triangle on the top right corner of the cell. But, it is not an actual error, but a transient state. To confirm, we can hover the mouse on the cell B3, to see the description as shown below.
Once Google Sheets loads the data, the red triangle on the cell B3 disappears. We’ll notice the data extends from the cell B3 to right and also further down.
We may not know how far the data is going to extend. So, it is very important that we keep the expected real estate of the result, clear of any preoccupied values. Otherwise, the IMPORTHTML formula will return a #REF! error, as shown in the snapshot below. Please note, for the purposes of demonstration, we entered a dummy value in the cell C9.
Congratulations! We now learned how to import a web HTML table to Google Sheets, by making use of the native IMPORTHTML formula! Go through the following blog post to learn how to import RSS feed to Google Sheets.
