2024 Guide to the Powerful IMPORTHTML Google Sheets Function

The IMPORT functions are some of the best ones for saving time when you’re working with large amounts of data from outside sources. The IMPORTHTML Google Sheets function in among the most useful as it brings data over from websites quickly and easily.

This guide will cover how to use the IMPORTHTML function with simple step-by-step instructions. We’ll also briefly cover some of the other IMPORT functions in Google Sheets so they’re easier to tackle when you encounter them in the future.

Read on to get a thorough understanding of IMPORTHTML and an introduction to IMPORT functions in Google Sheets.

What Is HTML?

HTML or Hyper Text Markup Language is used to create web pages. The language describes the structure of web pages. Developers use the HTML language to design the interface that the browser displays web page elements like text, media, and hyperlinks in.

Users can use HTML to navigate and insert links, as HTML is commonly used to add hyperlinks. The language also makes it possible to format and organize documents in a similar fashion to Google Docs.

What Is the Google Sheets Import Data From Website Function?

The Google Sheet IMPORTHTML function can search and extract data from an HTML table or list. The function is aimed to be used in getting lists or tables from an external website. Before we take a look at how you can use the Google Sheets import web data formula, let’s take a look at its format. Here is the formula:

=IMPORTHTML(URL, query, index)

The formula requires you to input three parameters. These are:

1. URL: This parameter defines the web page’s URL or a reference to a cell containing the URL. This address should include the protocol, such as http://. If you want to enter the value for the URL directly in the formula, make sure it is enclosed in quotation marks.
2. query: this parameter defines whether the data is in a list or table format, depending on the type of data you want to import into your spreadsheet.
3. index: the index parameter defines which table or list you want to import into your spreadsheet. The tables and lists are maintained in separate indices, which means that a list and table can have the index as 1 if both exist on the page.

How to Use IMPORTHTML in Google Sheets

Google Sheets Import Table From Website

In this example, we want to get the table on the list of highest-paid film actors from the Wikipedia page. Doing this manually can take a lot of time and effort, which is why we will use the Google Sheets import HTML function.

Here is how to use IMPORTHTML in Google Sheets to get a table:

1. Click on the cell where you want to import the data. The top left element of the table will be imported into the cell where the formula is input. Make sure that there is empty space to import the table properly.
2. Enter the initial part of the Google Sheets IMPORTHTML function, which is =IMPORTHTML(.
3. Enter the first parameter, which defines the URL containing the table you want to import. In this case, we type it as “https://en.wikipedia.org/wiki/List_of_highest-paid_film_actors“. Include quotation marks if you add the URL directly in the formula.
4. Add a comma sign to separate the parameters.
5. Now, add the second parameter, which is the query. In this example, we want to import a table which is why we write the parameter as “table” including the quotation marks. Add another comma to separate the parameters.
6. Now, add the final index parameter, which will define the table number you want to import into your spreadsheet. In this case, we write it as 1 as it is the first table on the webpage.
7. Add a closing bracket to finish the formula and press the Enter key to execute it.

Import List From Website Into Google Sheets

If the webpage has a list, you can import it into Google Sheets using the same steps you use to import a table. Here is how to get a list from a website and import html to Google Sheets:

1. Click on the cell where you want to import the data and enter the initial part of the Google Sheets IMPORTHTML function, which is =IMPORTHTML(.
2. Enter the URL parameter, which is “https://www.w3schools.com/html/html_lists.asp” in this case. Make sure to add quotation marks. Add a comma to separate the parameters.
3. Now, add the query parameter. In this example, we want to import a list which is why we write the parameter as “list” including the quotation marks. Add another comma to separate the parameters.
4. Enter the index parameter now, which is 2 in this case.
5. Finally, add a closing bracket to finish the formula and press the Enter key to execute it.

Using Cell References with IMPORTHTML

You can also use cell references as the parameters for the IMPORTHTML function. In the example below, we’ve used the formula:

=IMPORTHTML(C1,C2,C3)

Instead of typing the URL, query, and index into the formula.

Import Specific Rows and Columns

To import only a specific row and column with IMPORTHTML, you simply have to next it inside the INDEX function. In the example below, we used the formula:

=INDEX(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_highest-paid_film_actors","table",2),3,2)

You’ll notice that there is a ,3,2) outside the first closing bracket, this indicates to the INDEX function that you want to pull data from Row 3 and Column 2.

Reasons Why the IMPORTHTML Function Isnt Working

The list or table should be displayed within a few seconds if the formula is executed properly. However, if no data is being displayed or you get an error prompt, it may be due to the following reasons:

• Changes in the URL: Double-check the URL in case the URL was changed of the table or list you want to import has been moved to another URL.
• Changes in the protocol: Redirects to the website can often cause issues with the IMPORTHTML formula. Make sure the protocol is http or https to ensure the formula works properly.
• The index was changed: You may note that Google Sheets has imported the wrong table or list. The reason for this can be that the index for the list or table was changed. Simply try going up or down until the table is loaded properly to fix this.
• Scrapping has been blocked: The website owner may have blocked using bots or scrapers to stop them from scraping any web content.

Related: Google Sheets Web Scraping: A Simple Guide for 2024

How to Get Indexes of Tables/Lists to Pull Data From Website to Google Sheets Using IMPORTHTML

1. In your browser, navigate to More tools > Developer tools in the settings.
2. Click the Console tab
3. Copy the following code into the text box:

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

4. Press Enter
5. Hover over the options until the table you want to import is highlighted.
   

   

If you want to import lists instead, you should use “ul,ol” as an argument instead of “table” like so:

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

How to Set a Custom Interval for Refreshing Your Imported Data

You can use a combination of adding a query and Google Apps Script to change how often the import is updated.

1. Add Refresh to cell A1 and the number 1 to cell B1
   

   

2. Use the same formula you normally would, but for the URL argument, concatenate the query parameter and the refresh cell. For example, for the site we used above, it would be:

=(IMPORTHTML("https://en.wikipedia.org/wiki/List_of_highest-paid_film_actors" & "?refresh=" & B1,"table",1)

3. Navigate to Extensions > Apps script
4. Copy and paste the following code into to text box:

function myFunction() {

 var sheet = SpreadsheetApp.getActiveSheet();

 var cell = sheet.getRange("B1");

 var refresh = parseInt(cell.getValue().toString());

 var increment = refresh + 1;

 cell.setValue(increment);

}

5. Click the floppy disk icon to save the script.
   

   

6. In the menu on the left, open Triggers and go to Add triggers
7. Under Select event source, select Time-driven
   

   

8. Choose the appropriate options from the following two drop down lists and click Save.
9. Go Back to the Editor window and click Run
   

   

10. The data should now refresh at the specified intervals.

Similar Options for Scraping Data Into Google Sheets

You can use several other functions to scrape content into Google Sheets. Let’s take a look at some of them.

IMPORTXML

XML is a markup language similar to HTML. However, there is one key difference: XML does not have predefined tags. Instead, you can define your own tags to fulfill your needs. The IMPORTXML function in Google Sheets can be used to XML into Sheets.

Here is the syntax for the formula:

=IMPORTXML(link, xpath_query)

The formula uses two parameters which are link and xpath_query. The link parameter defines the webpages link you want to examine. The xpath_query parameter is the query you want to run on the data. Enclose the value for this parameter in quotation marks.

You can learn more about the formula in our IMPORTXML Google Sheets function guide.

IMPORTRANGE

The IMPORTRANGE formula in Google Sheets allows you to access data from another worksheet, provided that you have access permission for that sheet. The function allows real-time data transfer, and you can import exact ranges from another sheet.

Here is the syntax for the formula:

=IMPORTRANGE(spreadsheet_url, range_string)

The formula uses two parameters which are spreadsheet_url and range_string. The spreadsheet_url defines the URL of the source spreadsheet. Enclose the URL in quotation marks. The range_string parameter contains the information about the range of the cells you want to import to the current spreadsheet.

IMPORTFEED

The IMPORTFEED formula in Sheets lets you get data from Atom and RSS feeds. This helps you keep track of any news or blog post items on a website.

Here is the syntax for the formula:

=IMPORTFEED(URL, query, headers, num_items)

The formula uses four parameters: URL, query, headers, and num_items. The URL parameter defines the link to the Atom or RSS feed from the website. The query parameter is an optional parameter that defines the elements you want to get from the feed. The headers parameter specifies whether you want to have headers. The num_items parameter can specify the number of items in the feed.

IMPORTDATA

The IMPORTDATA function in Sheets lets you quickly get the data from a URL containing a .tsv or a .csv file. It can be useful if you are working with data only available in a CSV or a TSV format. Google Sheets will import the data and format it appropriately.

Here is the syntax for the formula:

=IMPORTDATA(URL)

The formula only requires one formula to work. The URL formula defines the URL of the file’s location. Ensure the parameter is in quotation marks.

Related: The Easiest Google Sheets Import JSON Guide

Frequently Asked Questions

How Do I Refresh the IMPORTHTML Google Sheets Function?

You can refresh the IMPORTHTML function in Google Sheets in multiple ways. Either the function can be updated every hour automatically whether the user refreshes the formula or not. You can also use the NOW function to trigger a referred of the IMPORTHTML function every minute or thirty seconds.

How Often Does IMPORTHTML Refresh?

Google Sheets automatically check for updates every hour when the document is open to keep getting fresh data, even if the user doesn’t change the formula or the sheet. The formula is recalculated if the user changes the formula or if any cell containing a reference to the function is updated. However, if your close and reopen the document, it won’t cause a refresh on any of the IMPORT functions.

Wrapping Up

You should have everything you need to start working with the IMPORTHTML Google Sheets function. Luckily, knowing how to use IMPORTHTML means it will be much easier to use the other IMPORT functions in Google Sheets too, as they work very similarly. If you found this guide useful, please check out our related content below to keep learning.

Related:

• How to Use the IMPORTRANGE Google Sheets Function
• How to Convert Excel to Google Sheets
• How to Use the Google Sheets QUERY Function
• How to Make a Google Sheet Pivot Table
• How to Import CSV to Google Sheets
• How to Insert Google Sheets into Google Slides
• Can Google Sheets Reference Another Sheet