CODEWITHSUNDEEP
google-sheets
Justin Yu
Justin Yu

Reputation: 151

Google Sheets IMPORTRANGE and QUERY not refreshing

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?

Upvotes: 5

Views: 5477

Answers (1)

NightEye
NightEye

Reputation: 11214

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:

Upvotes: 4

Related Questions