How to Use IMPORTHTML in Google Sheets

Let’s say you’ve landed great content in the form of a table or list on a website. Although you might directly try to copy and paste this content to your spreadsheet for later analysis, it’s not only time-consuming but may lead to potential errors. The better alternative is to figure out a way to scrape data and import it into your preferred storage platform.

This scraping process doesn’t require advanced training, especially if you’re already familiarized with Google Sheets formulas. For example, IMPORTDATA and IMPORTXML are built-in functions in Google Sheets that allow you to import data from external sources. The IMPORTHTML function in Google Sheets allows you to import data from an actionable table or list as well as keep it updated by refreshing it at regular intervals.

In this article, you’ll learn how the IMPORTHTML function works, understand the syntax of the IMPORTHTML formula, and how to use it to import data from a website. You can find a workaround solution at the end in case the function is not working properly.

How does IMPORTHTML work in Google Sheets?

You can use the IMPORTHTML function in Google Sheets to search for and extract data from an HTML list or table. Whereas you define an HTML table with the <table> tag, the <ul> describes an unordered list and <ol> and ordered one.

How to use IMPORTHTML in Google Sheets?

The IMPORTHTML function aims to import tables or lists from external websites. This ensures that every time the website is updated, so will the data imported into your Google Sheets. To better understand how to use this function, see the formula syntax explained below.

IMPORTHTML Syntax

=IMPORTHTML(URL, query_type, index)

• URL refers to the URL of the website. Don’t forget that you need to include the protocol “http://” or “https://” and enclose the full URL between double quotation marks.
• query_type is the data format you want to import, “table” or “list”.
• index is the index of the table or list on the website. Here, you can include any value starting from “1”, which would mean that you want to import data from the first table or list on the website.

How to get indexes of tables/lists to pull data from a website to Google Sheets using IMPORTHTML?

As it can be tricky to find the index reference of the table or list, it’s useful that you understand how to do so before using the IMPORTHTML function to import web data into Google Sheets.

1. 1. Go to the website containing the data to import. Launch the developer console by clicking on the upper-right-hand corner menu of your browser window, then More tools > Developer tools.

NOTE: Use Option + ⌘ + J on macOS, or Shift + CTRL + J (on Windows/Linux).

1. 2. To get the indexes of all tables, copy and paste this code into the “Console” tool. Press “Enter” to get results.

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

1. 3. As you can see, the “Console” tool returns all table indexes.

1. 4. Hover over the results to highlight the table you want. This is the best way to make sure you then include the right index number in the IMPORTHTML formula.

1. 5. To get the indexes of all lists, copy and paste this code into the console. Press “Enter” to get results.

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

1. 6. Hover over the results to highlight the list you want. This way, you can ensure that you include the right index number for the list in the IMPORTHTML formula.

1. 7. To empty the “Console” space, click on the “prohibition” icon, as shown below.

How to use IMPORTHTML to import a table in Google Sheets?

Now that you know how to access the Console tool in your browser, follow these steps to import a table in Google Sheets from the web.

1. 1. Open your Google Sheets and start typing in any cell in the IMPORTHTML.

1. 2. Go to the website where you want to import the table, copy the full URL, and then include it as the first argument between quotation marks.

1. 3. Include the query_type, in this case being “table”.

1. 4. Then include the index corresponding to the table you wish to import. Press “Enter”.

1. 5. You should now be able to see the table imported into your Google Sheets.

How to use IMPORTHTML to import a list in Google Sheets?

To import a list, you’ll use a similar method to the previous, but this time “list” will represent the second argument. This is how to import a list from the web using the IMPORTHTML in Google Sheets.

1. 1. Repeat steps 1-2 from the previous section.
2. 2. Include the query_type, in this case, “list”.

1. 3. Then include the index corresponding to the table you wish to import. Press “Enter”. You should now be able to see the list imported into your Google Sheets.

Google Sheets IMPORTHTML is not working

If you’ve managed to follow all steps successfully, congratulations! However, if you’ve encountered an issue and your Google Sheets IMPORTHTML is not working, it could be due to one of the following reasons:

• The URL has changed: Although this is not as likely to happen, it’s worth double-checking the URL in case the data you’re trying to import has been moved to another URL.
• The protocol has changed: As mentioned previously in the article, the protocol can be “https” or “http”. However, in some cases, the website has been optimized to redirect from one to the other, so make sure it has not changed.
• The index has changed: The table or list with index = 9 could have index = 8 now.
• Blocks bots/crawlers: The web owner may have included blocks to stop bots, in this case, our console codes, from reading or scraping web content.

Conclusion

As you’ve seen, Google Sheets not only offers powerful formulas to perform mathematical calculations. It also offers built-in functions to import data from external sources, including websites or blogs.

You should now feel more familiarized with your browser developer tools and the console to import data from the web. You have learned how the IMPORTHTML function works in Google Sheets, what the syntax of the IMPORTHTML formula is, and how to use your Google Sheets to import tables and lists from any website. Additionally, if you encounter any issues, you should also feel comfortable solving the issue.

If you want to learn more about how to import data, read the following articles below:

• How to use IMPORTDATA in Google Sheets
• Linking Google Sheets: How to Reference Another Sheet?
• How To Use IMPORTRANGE Function In Google Sheets
• How to Import CSV to Google Sheets Automatically