Web Applications Stack Exchange is a question and answer site for power users of web applications. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I'm using the importrange() function in Google Sheets to reference data from 'spreadsheet1' and display in it '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 the its master spreadsheet more often and update its values accordingly?

share|improve this question
2  
Read the following URL: productforums.google.com/forum/#!topic/docs/BsOdrVdZYdI – Jacob Jan Tuinstra Apr 16 '13 at 10:52

Short answer

Instead of using IMPORTRANGE use Google Apps Script

Explanation

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 or RANDBETWEEN 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 to on change and every minute but this could be too frequent as the import task could take more than one minute, so instead of NOW() you should round it to a good enough interval, let's say 10 minutes, then instead of NOW() use

INT(NOW()/TIME(0;10;0))

Let name this formulas time_interval.

The cell formula containing IMPORTRANGE will look like this

=IMPORTRANGE(
  "https://docs.google.com/spreadsheets/d/spredsheet_key&workaround="&time_interval,
  "Sheet1!A1:AB"
 )

Unfortunately the above formula returns the following error

Error
This function is not allowed to reference a cell with NOW(), RAND(), or RANDBETWEEN()

The same happens with the other import functions (IMPORTDATA, IMPORTHTML, IMPORTFEED, IMPORTXML)

References

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.