Learn more about Import functions

Google Sheets have Import functions to help optimize spreadsheets, such as:

Usage Limits

When Import functions create too much traffic, you get this error message: “Error Loading data may take a while because of the large number of requests. Try to reduce the amount of IMPORTHTML, IMPORTDATA, IMPORTFEED, or IMPORTXML functions across spreadsheets you’ve created.”

The creator of the document has strict limits. Users must consider usage across all open documents they create. If a collaborator makes an edit, it may also count against your quota.

To resolve error messages, reduce the amount of changes on the source range from where the Import function imported. For example, if the resolved value for source source range in =IMPORTDATA(source range) changes frequently, you must issue external calls, which may cause a throttle. 

Data Freshness

To ensure users get fresh data while they keep their usage reasonable, IMPORTDATA, IMPORTHTML, and IMPORTXML share some rules: 

  • All three functions automatically check for updates every hour while the document is open, even if the formula and sheet don’t change.
  • If you delete and re-add cells or overwrite the cells with the same formula, it triggers a refresh of the functions. 

Important: If you open and refresh the document, it won’t trigger a refresh on any of the functions.

Volatility

When you use an Import function, you may get an in-cell “#ERROR!” with the message: “Error: This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()”. 

Import functions cannot directly or indirectly reference a volatile function such as NOW, RAND, or RANDBETWEEN to prevent an overload in our users' spreadsheet because these volatile functions update frequently. 

If you get the above error message, but still want to read results of the volatile functions, copy the result of the volatile functions. Use Paste special and then Values only.

Important: If you take these steps, it makes all values static. For example, if you copy and Paste special NOW results as values, the values you paste no longer change based on your time. 

Tip: The only exception is made for the TODAY function, which is volatile but doesn't update more than one time per day.

Error message: “Result too large”

For IMPORTXML, if you get this error message, reduce the amount of data that your XPATH query returns.

Related resources

Was this helpful?
How can we improve it?
true
Visit the Learning Center

Using Google products, like Google Docs, at work or school? Try powerful tips, tutorials, and templates. Learn to work on Office files without installing Office, create dynamic project plans and team calendars, auto-organize your inbox, and more.

Search
Clear search
Close search
Google apps
Main menu
Search Help Center
true
35
false