2

I noticed that my spreadsheet is constantly refreshing functions - I can see progress bar [right-top section] moving each 5-10 secs. On those peaks I've huge processor consumption.

Since there're 1100 lines in main sheet (1 of 20 sheets in that file) it's pretty hard to find which function keeps progress bar running.

Is there any way to list which function is currently processed?

Rubén
  • 34,714
  • 9
  • 70
  • 166
Paweł Synowiec
  • 31
  • 4

2 Answers2

1

Try the developer tools from your browser. If you use Chrome, checkout How to Use the Timeline Tool.

Try to work offline in order to discard that the problem is related to functions that require to be online to be updated like IMPORTRANGE and Google Apps Script.

Use the search function together with regular expressions to look for open-ended references (like A:A) and functions like MMULT, SUMPRODUCT and similar functions.

Related

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • Is an open ended range bad because it has a bunch of meaningless entries, or is there some specific reason. E.g. If I had data up through row 985 of a thousand row sheet would sum(A1:A985) be signficantly faster than sum (A1:A)? – Sherwood Botsford May 07 '21 at 02:57
  • @SherwoodBotsford Instead of "bad" I think that is better to say that they might be "smelly" (from "code smell"), IMHO there isn't significative differences between `sum(A1:A985)` and `sum (A1:A)` but there might be if the open references are used in in functions like MMULT – Rubén May 07 '21 at 03:10
-1

I don't know of a calculation profiler for Google Sheets.
Probably simplest solution is to import it into Excel and work from there - hopefully Excel calculation bottlenecks are the same as Google calculation bottlenecks.
Start by reading my MSDN articles on Calculation performance.
https://msdn.microsoft.com/en-us/library/office/ff700515(v=office.14).aspx
(Disclosure: I develop & sell FastExcel which includes an Excel calculation profiler http://www.decisionmodels.com/fastexcel.htm)

Charles Williams
  • 23,121
  • 5
  • 38
  • 38
  • There are several functions and features that are not compatible between Excel and Google Sheets like array handling features (arrayformula, array_constrain, filter, importrange, etc.) – Rubén May 27 '17 at 03:21
  • This is in the "Translate your C program into fortran so you can use your fortran debugger" – Sherwood Botsford May 07 '21 at 02:58