I'm using the IMPORTRANGE function in Google Sheets to reference data from spreadsheet1
and display it in spreadsheet2
. This works fine, but if I edit spreadsheet1
and then go to spreadsheet2
it's not automatically updated, nor does it update if I refresh the page. I've set my cache to be limited to 0MB so it shouldn't be saving anything locally.
Any ideas how I can get it to check back to the master spreadsheet more often and update its values accordingly?
Best Answer
Short answer
Instead of using
IMPORTRANGE
use Google Apps ScriptExplanation
According to Change a spreadsheet's locale, time zone, recalculation, and language the recalculation interval for
IMPORTRANGE
is 30 mins. If shorter recalculation interval is needed then somehow the formula parameters should be changed.Unfortunately, in the new Google Sheets is not possible to use
NOW
,RAND
orRANDBETWEEN
functions, so the alternative is to use a Google Apps Script.Explanation about the use of non-deterministic functions
In the thread provided in the comment by Jacob Jan Tuinstra it's mentioned the use of an extra URL parameter that it's updated at certain intervals. That thread is from 2011.
The core idea was to add to the URL something like
"&workaround="&NOW()
and set the recalculation setting toon change and every minute
but this could be too frequent as the import task could take more than one minute, so instead ofNOW()
you should round it to a good enough interval, let's say 10 minutes, then instead ofNOW()
useLet name this formulas
time_interval
.The cell formula containing
IMPORTRANGE
will look like thisUnfortunately the above formula returns the following error
The same happens with the other import functions (
IMPORTDATA
,IMPORTHTML
,IMPORTFEED
,IMPORTXML
)References