How to use the IMPORTHTML Google Sheets formula

Written by Valentine Schelstraete

Mar 1, 2017

If you want to pull online data into Google Sheets from a website, IMPORTHTML Google Sheets formula is a great tool to have up your sleeve. It imports data from a table or list on any webpage without you having to copy-paste or adjust the data using other formulas.

Copy-pasting tabular data from the internet into a spreadsheet can generate formatting issues. For this reason, IMPORTHTML is an easier way to import data from sites like Wikipedia directly into your spreadsheet while retaining the correct layout. In addition, this formula ensures your data is always up-to-date. Google Sheets updates in real time, pulling live data directly into your spreadsheet. When the website is updated, your Google Sheet is updated automatically too. Here’s how to use the formula.

Syntax

IMPORTHTML(url, query, index)

  • url – this is the address (uniform resource locator) of the web page containing the table or list you want to import. This can take two forms.
    • A valid and fully qualified location in the form of text (enclosed in double quotes). For example: “https://en.wikipedia.org/wiki/Demographics_of_the_world”
    • Or it can be a reference to a cell (such as B1) within Google Sheets, where the url is stored.
  • query – this will be either “list” or “table” depending upon the structure of the data.
  • index – the index number identifies the list or table within the web page. Note that the indexes for lists and tables are treated separately. Both a table and list can exist with same index number.

How to import data from a webpage into Google Sheets

Option 1: IMPORTHTML(url, “table”, index)

Take a look at the screenshot below. I want to import data into cell B3, so this is where I enter my IMPORTHTML formula.

Importhtml Google Sheets: Case 1

Make sure that the url is valid, i.e. enclose the url with double quotes and hit Enter.

While Google Sheets fetches the table data, it displays a Loading notification. Wait a few moments. The bigger the table, the longer it might take to finish the data import.

Importhtml Google Sheets: Loading Data

While the data import is in progress, cell B3 looks like it has returned an error. See the little red speck on the top right hand corner of the cell. However, it isn’t an error, just a transient state. Hover the mouse over the cell to see the following description:

Importhtml Google Sheets: Snapshot of Error

As soon as Google Sheets has finished loading the data, the red speck disappears. You’ll notice the data extends from the formula cell (B3) to the right and further down the spreadsheet.

Importhtml Google Sheets: Loaded Data

Make sure you leave enough room in the spreadsheet for the entire table of data. If you don’t, IMPORTHTML formula might cough up a #REF! error. Take a look at the following image to see what happens when I have data in cell C9.

Importhtml Google Sheets: Error Message
Option 2: IMPORTHTML(url cell reference, “list”, index)

In this example, I have stored the url in cell B1. This is the url to the HTML page containing the list we want to import. I’ll refer to that cell in the IMPORTHTML Google Sheets formula, which I enter into cell B3. The result is the same except this time I want a list instead of a table.

Importhtml Google Sheets: Case 2

Looking for more timesaving formulas and tricks in Google Sheets? If you regularly need to import data from from .csv or .tsv files from the web, check out the following how to use the IMPORTDATA formula in Google Sheets.

You may also like…

Share This