2

I recently worked on a project in google sheets. While everything is working, changes in the page take a while to process and load. There are a few parts of the project (described below). Is there a way to find out what is causing the biggest load on the project, so that I can work on that area?

Alternatively, if someone has experience with the following types of functions, what do you think is causing the biggest slowdown:

  • I have a query to lookup and match values. Would this be faster as a vlookup with sort in an arrayformula?

    =IFERROR(QUERY(Record!A:C,"Select A where B = '"&B7&"' order by A desc limit 1 label A ''"),"")

  • I have random number generation through an arrayformula:

    =ARRAYFORMULA(IF(ROW(B6:B)=6,"RANDOM",IF(ISBLANK(B6:B),"",RANDBETWEEN(0+0*ROW(B6:B),COUNTA(B6:B)))))

    This fills in a cell with a random number if the one next to it has a value. I use this for random sampling in another query later.

  • I have some conditional formatting based on whether the cell has something in it.

  • I have some data validation based on a range of cells.

Note: Since my main question is about performance, I didn't think having an example file would be beneficial. It would take me a little to make one so if Ii should, let me know. Also, since other questions deal with scripting performance (like this one and this one) I feel like my question is different.

Community
  • 1
  • 1
Andrew Jackson
  • 823
  • 1
  • 11
  • 23

1 Answers1

2

I suspect the RANDBETWEEN formula is your biggest culprit - basically every time the spreadsheet changes in any way whatsoever, even if you dont actually edit, the numbers all change, so inside an arrayformula, depending on how many rows you have, its always recalculating the rand for every single row

Aurielle Perlmann
  • 5,323
  • 1
  • 15
  • 26
  • I'm inclined to agree. I may replace this formula with a script function that is called when the document is opened, that way the random generation only occurs once. – Andrew Jackson Mar 07 '17 at 17:29