Google Sheets have Import functions to help optimize spreadsheets, such as:
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.
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.
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 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.