Google-sheets – Which factor will give most speed up to every update to a large Google Sheets

google sheetsgoogle-sheets-performance

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; 10×150 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 12×150 cells, about 6×150 RAND calls, about 20×150 non-RAND operations.

  1. Reducing number of cells, through integrating preliminary results into more complex formulas.
  2. 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.

Best Answer

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.

Related Topic