IMPORTHTML, IMPORTXML and IMPORTDATA functions in Google Sheets

IMPORTHTML brings a table or list from a web page into the sheet; IMPORTXML and IMPORTDATA fetch other kinds of web data.

These three functions pull information from the web straight into cells, and fetch it again from time to time so that the sheet stays current. They suit public reference tables: a league table, a list of holidays, exchange rates published by an institution, an open data file.

No results are printed here, because they depend on live pages.

Syntax

=IMPORTHTML(url, query, index)

=IMPORTXML(url, xpath_query)

=IMPORTDATA(url)

Argument	What it is
`url`	The address of the page or file, starting with http or https.
`query`	For IMPORTHTML: "table" or "list".
`index`	Which table or list on the page, counting from 1.
`xpath_query`	For IMPORTXML: an XPath expression that says which parts of the page to return.

Examples

=IMPORTHTML("https://www.example.com/page","table",1)
Result: depends on live data or changes each time
The first table on a web page. (The address is a placeholder.)
=IMPORTHTML(A1,"table",2)
Result: depends on live data or changes each time
The second table, with the address kept in a cell.
=IMPORTHTML(A1,"list",1)
Result: depends on live data or changes each time
The first bulleted or numbered list on the page.
=IMPORTXML(A1,"//h2")
Result: depends on live data or changes each time
Every second-level heading on a page.
=IMPORTXML(A1,"//a/@href")
Result: depends on live data or changes each time
The address of every link on a page.
=IMPORTDATA("https://www.example.com/data.csv")
Result: depends on live data or changes each time
A CSV file published on the web, spread into rows and columns.
=QUERY(IMPORTHTML(A1,"table",1),"select Col1, Col3 where Col3 > 100",1)
Result: depends on live data or changes each time
Imported data handed to QUERY, where the columns are called Col1, Col2 and so on.

Which one?

Function	Fetches
IMPORTHTML	a table or a list from a web page
IMPORTDATA	a .csv or .tsv file at a web address
IMPORTXML	any part of a page, chosen by an XPath expression
IMPORTFEED	the items of an RSS or Atom feed
IMPORTRANGE	a range from another Google Sheets file

Start with IMPORTHTML. Most information worth importing is already in a table.

Finding the right table

A page may contain many tables, including ones used only for layout. The third argument says which. There is no label to go by, so try 1, then 2, then 3, until the one you want appears.

IMPORTXML in brief

XPath describes a path to elements of a page.

XPath	Returns
`//h1`	every top-level heading
`//p`	every paragraph
`//a/@href`	the address in every link
`//table[1]//tr`	the rows of the first table
`//div[@class='price']`	elements with a given class
`//title`	the page title

It is powerful and takes practice. A browser's Inspect tool shows the structure of a page.

Why an import fails

The page builds its content with scripts. These functions read the page as first sent by the server. Data that appears only after scripts run in your browser is not there to be read. This is the most common reason for an empty result on modern websites.
The site refuses automated visitors, or needs a sign-in. Nothing can be imported from behind a login.
The page changed. If the site is redesigned, the table number or the XPath no longer points to the same thing.
Cells in the way. The result needs empty space below and to the right.

A cell may show "Loading..." for some seconds. That is normal.

Be fair to the websites you read

Import only from pages you are allowed to use this way. Many sites' terms forbid automated copying, and the content may be protected by copyright. Public bodies that publish open data are the safest sources.
Each formula makes a request to the site. A few are harmless. Hundreds of import formulas in one sheet put load on the site and may get the requests blocked, and Sheets itself limits how many a file may make.
Say where figures came from when you pass them on.

Refreshing

Imported data is fetched again periodically while the sheet is open, roughly hourly, though Google does not promise an exact schedule. It is not a live feed. To keep a snapshot, copy the result and paste values only.

Cleaning what arrives

Imported tables often include units, footnote marks and text numbers. Wrap the import in QUERY to choose columns and rows, and use VALUE and REGEXREPLACE to turn text such as "1,250 km" into numbers. Inside QUERY, imported columns are named Col1, Col2, counting from the left.

Wrap the whole thing in IFERROR so that a temporary failure shows a short note and not a block of errors.

Common mistakes

"table" or "list" without quotation marks.
Counting tables from 0. The first is 1.
Expecting data that the page loads by script.
Building a business process on a page you do not control. It will break when the page changes.
Too many imports in one file.

In Excel

Excel has no IMPORTHTML. Its counterpart is Data, Get Data, From Web, part of Power Query, which is set up through menus and refreshed on request. Import formulas become plain values when a Sheets file is saved as Excel.

Related functions

The results on this page were worked out by us from the sample sheet; they were not produced by a script running Google Sheets. If Sheets shows you something different, please tell us.

This is an independent help site. It is not connected with or approved by Google. Google Sheets is a trademark of Google LLC, named here only to say which program a formula is for. The people and figures in the sample sheets are made up.