How to Use IMPORTHTML Function in Google Sheets

This guide will discuss how to use the IMPORTHTML function in Google Sheets.

When we need to import data from a table or list within an HTML page, we can easily do this using the IMPORTHTML function in Google Sheets.

The rules for using the IMPORTHTML function in Google Sheets are the following:

  • The IMPORTHTML function allows us to import data from an actionable list or table. Additionally, the imported data can be updated by refreshing it at regular intervals.
  • The value for the url argument must be enclosed in quotation marks or be a reference to a cell containing the appropriate text.
  • The imported data must be readily available on the website’s first load.
  • Furthermore, the imported data must be publicly available, meaning it does not require authorization and/or login credentials. 

The internet offers a vast amount of data readily available across different websites, allowing us to outsource data rather than create our own. Furthermore, there are certain data only accessible on specific websites.

Luckily, we can utilize different functions to avoid errors and save time manually inputting or directly copying and pasting data. 

For example, we can use the IMPORTDATA function and IMPORTXML function which allow us to import data from external sources. However, we will focus on the IMPORTHTML function in Google Sheets, which lets us import data from actionable lists or tables.  

In this guide, we will provide a step-by-step tutorial on how to use the IMPORTHTML function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.

Great! Let’s dive right in.

The Anatomy of the IMPORTHTML Function

The syntax or the way we write the IMPORTHTML function is as follows:

=IMPORTHTML(url,query,index)

  • = the equal sign is how we activate any function in Google Sheets.
  • IMPORTHTML() refers to our IMPORTHTML function. This function is used to import data from a table or list within an HTML page. 
  • url is a required argument. This refers to the URL of the page we want to examine, including protocols such as http://.
  • query is another required argument. This refers to either a list or table, depending on what type of structure contains the desired data.
  • index is also a required argument. It refers to the index, starting at 1, which identifies which table or list, as defined in the HTML source, should be returned. 

Note: The indices for lists and tables are maintained separately. There may be both a list and a table with index 1 if both types of elements exist on the HTML page.

How to Get the Index of Tables or Lists From a Website

It can be difficult to find the index reference of a table or list to input into our IMPORTHTML formula. Hence, we must learn how to get the correct index reference before using the IMPORTHTML function.

1. We must go to the website containing the data we want to import. 

IMPORTHTML Function in Google Sheets

2. Then, we will click on the upper-right-hand corner menu of the browser window to launch the developer console. Next, we will select More Tools and then Developer tools.

IMPORTHTML Function in Google Sheets

3. Let’s say we want to get the index of all tables. To do this, go to the Console tool and paste this code:

var index = 1; [].forEach.call(document.getElementsByTagName(“table”), function(elements) { console.log(“Index: ” + index++, elements); });

Index reference table code

4. Then, we will press the Enter key to get the result. Afterward, the Console tool will return all table indexes. 

IMPORTHTML Function in Google Sheets

5. Now, we can simply highlight the index of the table we want to include in our IMPORTHTML formula.

IMPORTHTML Function in Google Sheets

6. To get the indexes of all lists, copy and paste this code into the Console tool:

var index = 1; [].forEach.call(document.querySelectorAll(“ul,ol”), function(elements) { console.log(“Index: ” + index++, elements); });

Index reference list code

7. Now, we have all the list indexes. So we can simply highlight the index of the list we want to import data from.

IMPORTHTML Function in Google Sheets

A Real Example of Using IMPORTHTML Function in Google Sheets

Let’s say we found a table on a website containing data we want to use. Instead of manually copying the data, we want to import the table in order for the data to continuously update. 

We can easily perform this task using the IMPORTHTML formula below:

=IMPORTHTML("https://en.wikipedia.org/wiki/Google_Sheets","table",2)

IMPORTHTML formula

The first part of the formula is the URL of the website containing our desired table. In this case, we input the URL “https://en.wikipedia.org/wiki/Google_Sheets”.

Next, we specified the type of structure containing our desired data. Since the data is in a table, we inputted “table” as our query argument. 

Lastly, we have already explained how to get the index reference of tables in the previous section. Following the mentioned steps, let’s say we found the index of the table we want to pull. 

Now, we place the correct index reference of our desired table, which is 2, as our index argument.

Our final data set would look like this:

Final dataset

You can make your own copy of the spreadsheet above using the link below. 

Amazing! Now we can dive into the steps of using the IMPORTHTML function in Google Sheets.

How to Use IMPORTHTML Function in Google Sheets

1. First, we will go to the website containing the table we want to extract. Afterward, we will highlight the URL and right-click to select Copy. Otherwise, we can also highlight the URL and press Ctrl + C.

IMPORTHTML Function in Google Sheets

2. We will go to our spreadsheet and select an empty cell. To begin our formula, we will type in an equal sign and the function name. Our formula would be “=IMPORTHTML(”.

IMPORTHTML Function in Google Sheets

3. Next, we will paste the copied URL by right-clicking and selecting Paste. Otherwise, we can also press Ctrl + V to paste the URL. Remember to enclose the URL in quotation marks. Our formula would be “=IMPORTHTML(“https://en.wikipedia.org/wiki/Google_Sheets””.

IMPORTHTML Function in Google Sheets

3. Then, we will input our query argument. Since the desired data is in a table, we will input “table”. Our formula would be “=IMPORTHTML(“https://en.wikipedia.org/wiki/Google_Sheets”,”table””.

IMPORTHTML Function in Google Sheets

4. Lastly, we will indicate the index reference of the desired table. In this case, the desired table has an index reference of 2. Our final formula would be “=IMPORTHTML(“https://en.wikipedia.org/wiki/Google_Sheets”,”table”,2)”.

IMPORTHTML Function in Google Sheets

5. We will press the Enter key to return the result.

IMPORTHTML Function in Google Sheets

6. Alternatively, we can also extract data from a list. To do this, we will perform the first three steps. First, we will copy the URL of the website containing the list.

IMPORTHTML Function in Google Sheets

7. Then, we will go back to our spreadsheet and select an empty cell. We will type in an equal sign and the function name. Our formula would be “=IMPORTHTML(”.

IMPORTHTML Function in Google Sheets

8. We will paste the copied URL by right-clicking and selecting Paste. Otherwise, we can also press Ctrl + V. Our formula would be “=IMPORTHTML(“https://en.wikipedia.org/wiki/Google_Sheets””.

IMPORTHTML Function in Google Sheets

9. This time we will input “list” as our query argument. Our formula would be “=IMPORTHTML(“https://en.wikipedia.org/wiki/Google_Sheets”,”list”.

IMPORTHTML Function in Google Sheets

10. Now, we will input the corresponding index of the list we want to import. Our final formula would be “=IMPORTHTML(“https://en.wikipedia.org/wiki/Google_Sheets”,”list”,5)”.

IMPORTHTML Function in Google Sheets

11. We will press the Enter key to return the result.

IMPORTHTML Function in Google Sheets

And tada! We have successfully used the IMPORTHTML function in Google Sheets.

You can apply this guide whenever you need to import data from a list or table within an HTML page. You can now use the IMPORTHTML function and the various other Google Sheets formulas available to create great worksheets that work for you.

That’s pretty much it! Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Google Sheets users like you. If you liked this one, you'll love what we are working on! Readers receive early access to new content.

0 Shares:
Leave a Reply

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

You May Also Like