Google Sheets IMPORTRANGE and QUERY not refreshing

user_13906926 0
Viewed 2226

I have been using IMPORTRANGE and QUERY extensively to connect all of my spreadsheets for a while now. But recently noticed that IMPORTRANGE and QUERY will not return proper data unless the source Sheet is open. Also, the data used to automatically update (every 30 min or so, whatever the default refresh rate is) in the background for IMPORTRANGE, but now it will only update if I manually open the Sheet and it will display "Loading...." before returning the data.

Is anybody else having issues with these two functions?

google-sheets
1 Answers

This answer explains the issue you encountered.

In summary:

  1. It doesn't update when the sheet isn't opened.
  2. Recalculation only happens when sheet is opened.

Functions that pull data from outside the spreadsheet recalculate at the following times:
ImportRange: 30 minutes
ImportHtml, ImportFeed, ImportData, ImportXml: 1 hour
GoogleFinance: may be delayed up to 20 minutes

Alternative solution:

  • You can use time driven triggers and update those values every N minutes/hours instead BUT you will have to create a script for that.
  • Everytime you trigger, you'd have to use setFormula on every cell you used your importrange and query.

References:

user_161186860
Related