Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I have some sheets using finance functions (from Google and custom scripts). I've noticed that sometimes, these cells change value without me doing anything even though the sheet is set to recalculate on cell change (the default).
How do these cells/formulae know to update? Is there some sort of timer? I would be interested to see if I can show on my sheet the last time this happened to see how up-to-date it is. Or maybe if I can tweak this refresh rate.
The ImportRange, ImportHtml, ImportFeed, ImportData, ImportXml and GoogleFinance functions get refreshed from time to time as documented at the calculation settings page.
You can force these functions to refresh immediately by tweaking their parameters. For example, you can modify the letter case in the range parameter in an importrange(), or the letter case of the https prefix in an importhtml().
This is most often done to work around time-out issues, like this:
=iferror( importrange("...ssID...", lower(A1)), importrange("...ssID...", upper(A1)) )
Custom functions are supposed to only be re-evaluated when their arguments change. In practice, they will often be recalculated when the spreadsheet is opened by a user. A custom function can store a timestamp value in the Properties service to maintain a record of when they were last called.
To answer your question: if you just leave a formula be, there is no way to predict when it will update, nor an easy way to discover when the most recent refresh took place, short of spooling and comparing the results periodically. The exception is with custom functions as described above.
UrlFetchApp class and the Cache class - your answer could add this then it's a nice overview of when cells can 'change themselves'