0

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.

  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.

  • Re "too broad" vote(s): please ask questions that will allow to narrow down the problem. Don't just silently vote without giving any feedback on my question. – yurkennis Feb 2 '16 at 11:38
  • 1
    It is nearly impossible to pinpoint your problem without having a look at the actual spreadsheet. So, post a (read-only) link to it if possible. – Vidar S. Ramdal Feb 2 '16 at 12:41
  • 1
    As a side note, if you have many formulas in your spreadsheet, it is usually more effective to rewrite it as a custom formula that outputs the full array of results. – Vidar S. Ramdal Feb 2 '16 at 12:41
  • "to rewrite it as a custom formula that outputs the full array of results" -- could you explain what do you mean? – yurkennis Feb 2 '16 at 12:46
  • 1
    If you speak Spanish or know about a good online translation tool that works fine with Stack Exchange sites, the following question could help you regarding how to ask about spreadsheet performance ¿Cómo medir el tiempo de ejecución del recálculo de una hoja de cálculo de Google? posted by me. Note that the context is similar, there is a complex spreadsheet that takes a lot of time to update (almost 6 mins), but instead of listing alternatives, a specific question is made. The question presents the research done before posting the question. – Rubén Feb 2 '16 at 16:42
3

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.

  • 1
    Replacing some conditional formatting and calculated fields with an app-script made my spreadsheet faster. – Salman A Mar 9 '17 at 9:53
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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.