Which factor will give most speed up to every update to a large Google Sheets?

Question

I have a huge spreadsheet in Google Sheets and it takes about half a minute to complete every change I make to its contents: user interface stays responsive, but there's a slow progress bar showing the ongoing update (upload? re-calculation? download)?

Which factor will give the most improvement in the update time?

What I choose from is, with respective numbers:

1. Removing nearly-static sheets (I have 20 such sheets; 10x150 cells in each) which (a) never change and therefore don't need re-calculation; 80% of content is constant values, the rest is one-operation simple calculations (no RANDs or heavy functions).
2. Other sheets are heavily generated based on RAND/BETWEENs

Will it help to use a single source of RAND values (a dedicated sheet) for all of them, referred to 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. Reducing number of cells, through integrating preliminary results into more complex formulas.
4. Reducing number of non-RAND atomic operations (although it will likely hurt readability of the calculations).

Overall, I'm interested in the largest-to-smallest list of factors contributing to the large update time hence slow progress bar on every update.

Answer 1

As mentioned in the comments, I would probably recommend to rewrite your logic from using lots of formulas, into using one/a few 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.

As long as we are talking about just a few formulas, the difference in performance will be small, perhaps even opposite. But in my experience, a huge number of formulas will give bad performance, and moving the logic into a script function performs better.

Answer 2

As mentioned in a comment and later the answer by Vidar S. Ramdal,

I would probably recommend to rewrite your logic from using lots of formulas, into using one/a few Google Apps Scripts.

It's worth to note that

1. Custom formulas, in contrast of nondeterministic functions like RAND, are recalculated only when their parameters change.
2. Using a triggered script rather than a custom formula, the spreadsheet recalculation will be made only when the script be triggered instead of on every change of the custom function parameters.

The below Google Apps Script function will return a custom matrix of randoms numbers. It' could be slower than using a matrix of =RAND() formulas in the case that the matrix is small, let say 3 x 3, but it will be faster in cases where the resulting matrix should be of thousands of cells. By the other hand, it will only be recalculated when its parameters be changed in contrast that RAND() will change every time that 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).