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.
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.
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:
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.
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.
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.
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.
