0

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.

1 Answer 1

1

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.

2
  • this makes sense. The custom scripts I use call UrlFetchApp class and the Cache class - your answer could add this then it's a nice overview of when cells can 'change themselves'
    – Mr. Boy
    May 18, 2021 at 12:32
  • Edited the answer to include info about custom functions. May 18, 2021 at 12:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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