Skip to content
Tutorials Center
My learning

Topics

Lesson 23 of 29 ยท Advanced Formulas

Importing Live Data

Pull live data into Google Sheets with IMPORTRANGE, IMPORTDATA, IMPORTHTML, IMPORTXML and GOOGLEFINANCE, and learn their limits and common errors.

A spreadsheet does not have to be a closed box. Google Sheets can pull data from other spreadsheets, from CSV files on the web, from HTML tables, from XML or HTML pages and from a financial data service, all with a single formula. The imported data refreshes by itself, so a report or dashboard stays current without copy and paste.

This lesson covers the five main import functions, how to combine them with QUERY, and the limits you will hit. These functions need Google's servers and a network, so they cannot run on this page. The formulas are read-only, and JavaScript stand-ins show how the data is parsed once it arrives.

Five ways to bring data in

IMPORTRANGE Another spreadsheet.
IMPORTDATA A CSV or TSV file.
IMPORTHTML A table or list on a web page.
IMPORTXML Any XML or HTML element.

GOOGLEFINANCE adds stock and currency data.

1 / 2

IMPORTRANGE: another spreadsheet

IMPORTRANGE(spreadsheet_url, range_string) copies a range from another Google Sheet. The first argument is the file's URL (or just its ID) and the second is the sheet name plus range, as text:

TEXT
=IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID/edit", "Sales!A1:E100")

The first time you use a new source, the cell shows #REF! and a button Allow access. Press it once, and the data appears. You need at least view access to the source file. If you leave out the sheet name, the range is read from the first sheet. An open range such as "Sales!A:E" imports every row that exists, which suits growing data.

IMPORTRANGE is often placed inside QUERY to filter or summarize remote data. The columns of an imported array are called Col1, Col2, and so on:

TEXT
=QUERY(IMPORTRANGE("FILE_ID", "Sales!A1:E100"), "select Col1, sum(Col5) where Col4 > 10 group by Col1", 1)

You can stack ranges from several files with curly braces: ={IMPORTRANGE("ID1","Jan!A2:C"); IMPORTRANGE("ID2","Feb!A2:C")}. The pieces need the same number of columns. A neat pattern is to keep one source spreadsheet owned by one person and let reports import from it, so there is a single place to fix a number.

IMPORTDATA: CSV and TSV files

IMPORTDATA(url, [delimiter], [locale]) loads a text file at a public URL, one row per line and one column per delimiter.

TEXT
=IMPORTDATA("https://example.com/prices.csv")

The URL must end with a CSV or TSV file that is served as plain text without requiring a login. If the delimiter is not a comma, pass it as the second argument, for example ";". Large files are slow and count toward limits, so import only what you need.

IMPORTHTML: tables and lists from a page

IMPORTHTML(url, query, index) reads a table or a list from an HTML page. The query is "table" or "list" and the index says which one on the page, starting at 1.

TEXT
=IMPORTHTML("https://example.com/rates", "table", 2)

This is a quick way to bring a published table into a sheet. It only sees the HTML delivered by the server: tables that a web page builds later with JavaScript are invisible, and Sheets returns #N/A with a message that the content cannot be imported. Finding the right index is trial and error. Try 1, 2, 3 until you see the table you want.

IMPORTXML: any element with XPath

IMPORTXML(url, xpath_query, [locale]) is the most flexible one. It fetches an XML, HTML, CSV, RSS or Atom document and returns the nodes selected by an XPath expression.

TEXT
=IMPORTXML("https://example.com/blog/feed.xml", "//item/title")
=IMPORTXML("https://example.com/page", "//h2")
=IMPORTXML("https://example.com/page", "//a/@href")

XPath is a path language: //h2 means "every h2 anywhere", //item/title means "every title directly inside an item", and /@href selects an attribute. Because it works on the fetched source, it has the same limit as IMPORTHTML: no JavaScript-generated content. Web pages change, and when the markup does, the XPath stops matching, so treat these formulas as fragile. Check the site's terms before scraping, and prefer an official feed or API when one exists.

JS Stand-in: parse CSV and an HTML table

Press Run (or Ctrl + Enter) to see the console output here.

No output.

GOOGLEFINANCE

GOOGLEFINANCE(ticker, [attribute], [start_date], [end_date|num_days], [interval]) returns market data from Google's finance service. The ticker can include an exchange: "NASDAQ:GOOG".

TEXT
=GOOGLEFINANCE("NASDAQ:GOOG", "price")
=GOOGLEFINANCE("NASDAQ:GOOG", "close", DATE(2025,1,1), DATE(2025,3,31), "DAILY")
=GOOGLEFINANCE("CURRENCY:USDEUR")

Current attributes include price, priceopen, high, low, volume, marketcap and change. Historical data is requested with a date range and returns a table with a date column and the values; with a single date you get a header row plus one row. Quotes can be delayed by up to about 20 minutes, and the service is meant for personal use, not for trading decisions. Historical data is not available for every attribute or market, and mutual funds and some tickers are only available in part.

Refresh, limits and errors

Imported data does not refresh on a strict clock. Sheets re-evaluates imports when the sheet opens and from time to time while it is open, and the exact timing is not guaranteed. Each function has caching, so a change on the source may take minutes to appear. A few practical limits:

  • A spreadsheet can hold only a limited number of cross-spreadsheet connections, so chaining many IMPORTRANGE calls from sheets that themselves import data can fail.
  • There is a quota for the number of IMPORTXML, IMPORTHTML and IMPORTDATA calls, and a call can fail with Loading... that never ends or a message about the resource exceeding limits.
  • Imports that point at pages needing a login fail. Private data must be shared with the viewer through Google's permissions instead.

Common errors:

Message Meaning
#REF! with "Allow access" Source not yet authorized for IMPORTRANGE
#REF! array result was not expanded Something is in the way of the imported area
#N/A "Could not fetch URL" The server did not respond or blocked the request
#N/A "imported content is empty" The XPath or table index matched nothing
#ERROR! The formula could not be parsed

Wrap uncertain imports with IFERROR(..., "unavailable") so a dashboard degrades gracefully (see IF, AND, OR and IFS). To keep a snapshot, copy the imported range and use Paste special โ†’ Values only.

JS Stand-in: an XPath-like selector and IFERROR

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What must you do the first time IMPORTRANGE connects to a new spreadsheet?

    The first import from a new source shows #REF! until you click Allow access, and you also need at least view access to the file.
  2. 2. Which function reads a table from a web page by its position on the page?

    IMPORTHTML takes the URL, the word table or list, and an index starting at 1.
  3. 3. Why does IMPORTXML return nothing for a page that shows data in the browser?

    Import functions see the HTML the server sends. Content created later by JavaScript is not there.
  4. 4. Columns of imported data are named Col1, Col2, ... in which situation?

    QUERY treats arrays, such as an IMPORTRANGE result, as having Col1, Col2, and so on instead of column letters.

Summary

  • IMPORTRANGE copies a range from another sheet; allow access once.
  • IMPORTDATA loads CSV/TSV, IMPORTHTML a table or list by index, IMPORTXML any node chosen by XPath.
  • GOOGLEFINANCE returns current and historical market data, possibly delayed.
  • Imports see only server-sent content, refresh on Google's schedule and break when sources change.
  • Combine with QUERY for filtering and IFERROR for fallbacks.

Next: Sharing, Comments and Version History