5

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?

Justin Yu
  • 101
  • 2
  • 5

1 Answers1

4

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:

NightEye
  • 10,634
  • 2
  • 5
  • 24
  • 1
    Wasn't aware that import range had a time delay, I thought it was instant, or atleast it was instant when I used it a few years back. – Umar.H May 04 '21 at 21:07
  • 1
    Hi, @NaziA thanks for your input. I was aware of the 30 min delay on ImportRange. The problem is not that it is not updating rather the data appears as it doesn't exist unless the sheet is opened. For example, I have a script that emails out the contents of the sheet every 2 hours. Before it would send out an excel sheet with the contents accurately. Now, the contents are empty in the attachment. When I open the sheet, then it finally loads (shows "Loading....") and populates data. I guess what I'm saying is the data doesn't exist until the sheet containing the data is actually opened... – Justin Yu May 04 '21 at 21:10
  • @Umar.H, if I didn't see this question and the linked answer, I would never know that it also had a time delay. – NightEye May 04 '21 at 21:12
  • @JustinYu, Hmmm. I believe the issue is that sheets only does this data calculation for the following function above whenever opened. If you want the value to be updated even if the sheet is unopened, you might need to do it via apps script. I'll link you to a similar [answer](https://stackoverflow.com/a/11144130/14606045) that you might need to check. – NightEye May 04 '21 at 21:17
  • 2
    @NaziA, yes it's strange. I've been using ImportRange and Query this way for over a year now without issues. It just started happening around a week or so ago. – Justin Yu May 04 '21 at 21:24
  • @JustinYu, check this [answer](https://stackoverflow.com/a/40071631/14606045). – NightEye May 04 '21 at 23:56