Don’t forget to share this post

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.
How to Use IMPORTHTML in Google Sheets Launch Developer Console
How to Use IMPORTHTML in Google Sheets - Launch Developer Console

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); });
How to Use IMPORTHTML in Google Sheets Get table index
How to Use IMPORTHTML in Google Sheets - Get table index
  1. 3. As you can see, the “Console” tool returns all table indexes.
How to Use IMPORTHTML in Google Sheets Table indexes result
How to Use IMPORTHTML in Google Sheets - Table indexes result
  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.
How to Use IMPORTHTML in Google Sheets Highlight table
How to Use IMPORTHTML in Google Sheets - Highlight table
  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); });
How to Use IMPORTHTML in Google Sheets Get list index
How to Use IMPORTHTML in Google Sheets - Get list index
  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.
How to Use IMPORTHTML in Google Sheets List indexes result
How to Use IMPORTHTML in Google Sheets - List indexes result
  1. 7. To empty the “Console” space, click on the “prohibition” icon, as shown below.
How to Use IMPORTHTML in Google Sheets Empty console
How to Use IMPORTHTML in Google Sheets - Empty console

How To Use IMPORTRANGE Function In Google Sheets?

Google Sheets allows you to import and link a specific range of cells from another spreadsheet. Here's how to use the IMPORTRANGE function in Google Sheets

READ MORE
How To Use IMPORTRANGE Function In Google Sheets

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.
How to Use IMPORTHTML in Google Sheets Type in IMPORTHTML formula
How to Use IMPORTHTML in Google Sheets - Type in IMPORTHTML formula
  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.
How to Use IMPORTHTML in Google Sheets Include URL
How to Use IMPORTHTML in Google Sheets - Include URL
  1. 3. Include the query_type, in this case being “table”.
How to Use IMPORTHTML in Google Sheets Include query type
How to Use IMPORTHTML in Google Sheets - Include query_type
  1. 4. Then include the index corresponding to the table you wish to import. Press “Enter”.
How to Use IMPORTHTML in Google Sheets Include index
How to Use IMPORTHTML in Google Sheets - Include index
  1. 5. You should now be able to see the table imported into your Google Sheets.
How to Use IMPORTHTML in Google Sheets Imported table from web
How to Use IMPORTHTML in Google Sheets - Imported table from web
Linking Google Sheets How to Reference Another Sheet
Linking Google Sheets: How to Reference Another Sheet?

Sometimes you have to reference or merge data from multiple sheets or spreadsheets. Here's how to easily link multiple Google Sheets

READ MORE

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”.
How to Use IMPORTHTML in Google Sheets Include query type 2
How to Use IMPORTHTML in Google Sheets - Include query_type
  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.
How to Use IMPORTHTML in Google Sheets Include index 2
How to Use IMPORTHTML in Google Sheets - Include index

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.

How to import data in Google Sheets with Layer?

Layer is an add-on that equips finance teams with the tools to increase efficiency and data quality in their FP&A processes on top of Google Sheets. Share parts of your Google Sheets, monitor, review and approve changes, and sync data from different sources – all within seconds.

Using Layer, you can:

  • Share & Collaborate: Automate your data collection and validation through user controls.
  • Automate & Schedule: Schedule recurring data collection and distribution tasks.
  • Integrate & Sync: Connect to your tech stack and sync all your data in one place.
  • Visualize & Report: Generate and share reports with real-time data and actionable decisions.

Limited Time Offer: Sign up today and get free access to Layer, including all the paid features, so you can start managing, automating, and scaling your FP&A processes on top of Google Sheets!

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:

Hady ElHady
Hady is Content Lead at Layer.

Hady has a passion for tech, marketing, and spreadsheets. Besides his Computer Science degree, he has vast experience in developing, launching, and scaling content marketing processes at SaaS startups.

Originally published Aug 10 2022, Updated Sep 23 2022