Google sheets recalculating cell when not needed

1
votes

Is there any reason for Google Sheets to recalculate a cell even though no dependencies have changed? I'm having this issue with huge IMPORTRANGES, creating huge delays in recalculating thousands of cells which didn't changed.

To make this issue visible, I've created a simple test spreadsheet you can visit here.

This spreadsheet contains a custom function returning an 2-dim array containing 170'000 random values, including the current datetime so we know when the custom function was called. The custom function takes 1 parameter which is a constant. Very often when I open the spreadsheet, the custom function gets called, refilling everything again. However, it shouldn't since the only argument to the custom function is a constant.

Any idea?

Of course this is just an example, I'm writing this because I have huge spreadsheets with takes around 10 minutes to recalc when I open them, even though nothing have changed.

1
google-apps-scriptgoogle-sheetsgoogle-appscustom-function
These problems are common with importrange. I found the easiest method to mitigate this is to move the import into a script that you execute scheduled or on demand. - Robin Gertenbach
It's exactly what I did, I wrote a custom function. But I have the same issue with the custom function, this is the purpose of my text and example. - Rafael
The script would not be called via spreadsheet but a schedule, it would write the values explicitly rather than being the result of a formula. That makes the data non volatile. - Robin Gertenbach
This workaround has too many drawbacks: 1. You have no visibility of the range which is being filled by a script. If you override some cells, you have no warning. With a formula, if you override a value Sheets inform you that you're writing within the space of an array formula 2. Maintenance: when you have 15 imports in a Spreadsheet, it's much harder to maintain The purpose of my post was more to understand the logic behind the recalculation which is not needed. - Rafael

1 Answers

0
votes

Recalculation

Google spreadsheets are recalculated when they are opened. Some limitations could apply, like:

  • Server built-in functions and custom functions could be "freeze" if they were recalculated either by opening a spreadsheet or by a time based threshold like the ones of IMPORTDATA, IMPORTHTML,IMPORTRANGE, IMPORTXML.

AFAIK the "freeze" of some calculations is not included on the Google Docs Help but this kind of limitations are usually applied by Google for free consumer accounts (usually those with with a gmail.com domain email address) in order to "prevent abuses". "Paid" (now they are called G Suite) accounts could have "higher quotas".

Solution

As was suggested on the comments to the question, if this is an issue, you should think about how are you are building your spreadsheets.

The basic is to reduce the number of sheets, rows, columns and formulas to reduce size an complexity.

If you are using formulas to do calculations that are required only one time, replaces those formulas with their results. Paste as values come handy on this cases.

Other calculations that should be made only on demand, should be moved to a script or to an external spreadsheet from where you could import the calculated values.