How do formulae know to re-run?

[jdx-john]

I'm using GECKOPRICE and I have set a cell to let me trigger an update on-change.

However, I notice that sometimes cells update value even when I don't modify this cell - it appears GOOGLEFINANCE does similar. Do you know where and how this is controlled? The problem changing a cell is it leads to a very long edit history on my sheet.

[Eloise1988]

Check in In File Spreadsheet settings. I've set calculations on Change only, for it to refresh only when I work on the sheet.

[jdx-john]

@Eloise1988 I already have that - it's the default setting. But Google finance cells still periodically update anyway, as do cells using your functions. Typically when I switch tabs but I'm not certain that's the cause. I looked at your code and can't see anything that would explicitly do this so I figure it's a function of google-sheets but no idea how!

[jdx-john]

Aha, it's down to your use of Cache.put : https://developers.google.com/apps-script/reference/cache/cache?hl=en#put(String,String) This causes it to time-out using the default value of 10min, if I understand correctly. I wonder if you could expose the cache timeout in the API? I can hack my own version of course.

[Eloise1988]

From Cache.put : https://developers.google.com/apps-script/reference/cache/cache?hl=en#put(String,String) "The maximum length of a key is 250 characters." -> I used the getBase64EncodedMD5(() function to make sure it doesn't exceed the 250 characters.

"The maximum amount of data that can be stored per key is 100KB."
->100KB corresponds to 100'000 characters which should be enough for cache.

Yes the cache disappears after 10min, the time can be updated with put(key, value, expirationInSeconds). I might change the code to let users change the expirationInSeconds parameter. I'm surprised that functions get automatically updated when the cache resets.