Which changes will have the most impact on recalculation times for a very large and slow spreadsheet?

Question

I have a huge spreadsheet in Google Sheets and it takes about a half-minute to recalculate after every change. During this delay, the user interface is responsive, but there's a slow progress bar showing the ongoing update.

What changes would provide the greatest improvement?

1. Should I remove nearly-static sheets?
   • I have 20 such sheets; 10x150 cells in each, which never change and don't need re-calculation;
   • 80% of their content is constant values,
   • 20% is one-operation simple calculations (no RANDs or heavy functions).
2. Other sheets are heavily dependent on formulas using RAND/RANDBETWEEN.
   • would it help to use a single source of RAND values (a dedicated sheet) and refer to it multiple times, rather than invoking RAND separately in each of them?
   • The logic of my spreadsheet allows such re-use of randoms. I have 17 such sheets; each is 12x150 cells, about 6x150 RAND calls, about 20x150 non-RAND operations.
3. Should I reduce the number of formulas by integrating the formulas used to return preliminary/helper results directly into the formulas that reference them?
4. Should I reduce the number of non-RAND atomic operations even though this will negatively affect the readability of those formulas?

Overall, I'm interested in the impact by largest-to-smallest of factors contributing to the long recalculation times following changes.

Answer 1

As in vidar-s-ramdal's answer, I think you should use Google Apps Script functions to offload calculations, and I have provide an example script below.

It's worth noting that:

1. Custom functions are recalculated only when their parameters change, or based on a trigger you define.
2. By contrast, RAND and RANDBETWEEN are volatile functions that are recalculated every time any change is made to the spreadsheet.

The Google Apps Script function below will return an array of random numbers. For a small array, 3x3 for example, the script may be slower than simply using 9 RAND functions in a 3x3 range of cells. Once the array becomes larger the script will be much faster, and can easily generate thousands of values.

Additionally, the script-generated array will only be recalculated if its parameters change, whereas the values returned by the volatile RAND functions will be recalculated on every time the spreadsheet changes.

function randomNumbersMatrix(rows,cols) {
  var matrix = new Array;
  for(i = 0; i < rows; i++){
    matrix[i] = [];
    for(j = 0; j < cols; j++){
      matrix[i].push(Math.random());
    }
  }
  return matrix;
}

In Stack Overflow en Español I posted a question asking about an specific approach to measure the recalculation time of a Google Sheets spreadsheet in order to objectively know if a custom function is really faster than the alternative based only on formulas.

Related

• ¿Cómo medir el tiempo de ejecución del recálculo de una hoja de cálculo de Google? (Stack Overflow en Español).

Answer 2

I would recommend you change your approach from using many formulas, to using one or more Google Apps Scripts.

See this example spreadsheet. It includes a Google Apps Script function that returns an array of 6 random numbers:

function giveMe6RandomNumbers() {
  return [Math.random(), Math.random(), Math.random(), Math.random(), Math.random(), Math.random()];
}

I can then call this function from any cell with =giveMe6RandomNumbers(), and it will fill that cell, and the 5 next cells, with random numbers.

If you only replace a few formulas, the performance benefit may be minimal, and there could even be a slight performance hit. But, in my experience, converting a large number of formula-based calculations into script-based calculations, can significantly improve performance.