How do I find out what is slowing down google sheets?

Question

I have a multitab google sheet that now and then slows to a crawl.

• The progress ribbon takes several passes to before it quits.
• At times the progress ribbon stops entirely for minutes at a time.
• This behaviour happens even with the file set to 'work offline'
• Reloading the page fixes it -- for a while.

Edit: I am NOT looking for a checklist of things that slow down sheets. I've found several of articles for that sort of thing. Rather I'm looking for tools and methods to determine where a sheet is spending time. In most compiled languages to do this you run the program in a profiler. It stops the program at intervals, pulls the current line number of the running code. Or you step through the code with a debugger.

In addition I'd like to know why the slowdown is episodic. Sometimes it's quite spritely, sometimes it takes forever.

Structure of Spreadsheet:

• Trees -- Static information about 300 lines. Used by vlookups
• StdPrices -- A collection of named ranges used to convert sizes to prices, apply appropriate rounding conditions, convert size code to descriptions.
• CustOrders -- Blocks of rows where each row is an order. Status shows each block as quote, order, shipped, cancelled
• RawInv -- sheet where I can enter current inventory at the start of the season. I enter product code, count, pot size, plant size, and percent salable, and it calculates product description, and pricing.
• A query below the inventory pulls the line items and their status out of the Order blocks in CustOrder.

A different spreadsheet imports the entire RawInv sheet, and does two pivot tables from it. Using a different sheet keeps the pivot tables from updating every time I make a change.

I've searched online and can find various tips on speeding up sheets, both google and excel, but none that address this occasional slowing, nor any information on diagnosing what is slowing down a sheet, or how to profile it's running.

How many formulas have your spreadsheet? How complex are those formulas (how many functions / operators have them? How large are the calculation chains? How many VLOOKUP are used?, Is your spreadsheet using volatile functions like NOW(), TODAY()? Is your spreadsheet shared? Do you use extensions? –

Short answer: Under 10,000 Formulas.

Trees has 355 copies of =F273&" ("&G273&")" obviously with differnt row numbers. The () are part of the result.

StdPrices has no formulas at all.

CustOrders can have 5000 copies of

=VLookup(Upper(G113),TreeC,4,True) & " -- " &VLookup(Upper(I113),PriceBase,4,False) 

=mround(S98,vlookup(S98,PriceRound,2))

=K98*H98

=iferror(VLookup(I98,PriceBase,2,False)*VLookup(Upper(G98),TreeC,5,True))

=row() {used in backtracing pivot table oddities}

At present the number of copies of each is about 50, though, as it's early in my season. The order book opens 1 November, but orders don't even start to go out until early May.

250 formulas total.

Each order block has

=sum(L83:L96) {subtotal}

=-abs(E91)*F90

=sum(F90:F92)

=F94-abs(F95)

4 formula per block currently 10 blocks.

40 formula total.

Raw Inventory has about 500 rows

=iferror(AVERAGE(I161:J161)/12)

=iferror(ROUND(L162*H162),0)

=mround(S162,vlookup(S162,PriceRound,2))

=(if(I162<24,I162&"-"&J162&" in.",mround(I162,6)/12&"-"&mround(J162,6)/12&" ft."))

=vlookup(F161,Trees!E$2:I$356,4,0) & " -- " & vlookup(G161,PriceBase,4,0)

=max(V161,AA161)

=iferror(vlookup(G161,PriceBase,2,0))

=if(isblank(F161),0,Vlookup(F161,TreeC,5,1))

=T161*U161

=iferror(if(C161="Conifer",vlookup(G161,PriceBase,9,0), vlookup(G161,PriceBase,10,0)))

=iferror(query(PriceBase,"select D where B = '"&G161&"'",0),"")

=MAX(Y161+Z161*U161,T161*U161)

=iferror(vlookup(G161,PriceBase,5,0))

13 formula per row, 500 rows = 6500 formula.

However each row only depends on 5 fields typed at the start of the row, so once a row is done, it doesn't have to be redone until one of those fields changes.

I'm currently editing inventory. Sometimes I can get 4 rows ahead while it's doing lookups.

For lookups, anything over 30 rows, I keep sorted, and use a 1 flag. For short tables I don't bother.

Complexity: Not sure how to score this. Be nice if there were auditing tools for sheets. Few formulas have parentheses more than 3 deep. But I have a lot of vlookups so that my data is in the 'enter it only once' category. Calculation chains may be 6-8 cells long at the extreme.

Volatile functions: None.

Sharing: Spreadsheet is shared, but is set to work offline, so that sharing shouldn't slow it down.

Extensions:

• Markdown Tablemaker
• Mailmerge
• More Fonts
• Freezer (disabled)

Answer 1

From my answer to Measurement of execution time of built-in functions for Spreadsheet

Google Sheets doesn't include a built-in tool to measure the recalculation time.

One alternative is to use the Chrome Developers Tools Timeline but bear in mind that functions like IMPORTHTML and IMPORTXML are not recalculated every time that the spreadsheet does (reference Set a spreadsheet’s location and calculation settings).

Related Q&A

SO

• Google spreadsheet constantly recalculates functions

Web Applications

• What's causing / how to get rid of long web calls in Google Spreadsheets
• Where does the calculation take place?

Another thing to bear in mind is that there is no way for end-users to know when the calculation of an specific cell / formula starts/ends.

By the way, on another answer to the same question on SO, Tanaike suggest a method to use Google Apps Script to measure the recalculation time of a simple formula.

Anyway, I don't think that there is a single and concrete simple tool / method to find what you are looking for. IMHO it's required an heuristic approach (= a lot of study and work)

Answer 2

The specific problem was this:

Table RawInventory use a query in each row:

=query(TreeT,"select C where E = '"&UPPER($F14)&"'",0)

Where TreeT is a named range. But TreeT had blank lines at the end for expansion. (If you insert a line, the range definition updates. If you append it, it doesn't.)

If F14 was blank, query would return two values, but there was room for only one This gave a #REF error.

My current working hypothesis is that Sheets attempts to resolve all #REFS with each iteration. This resulted in massive garbage collection.
One workaround I found: Reloading the tab (when it said all changes saved in Drive) was MUCH faster than allowing the calculation to finish. AFAICT all calcs were complete in the reloaded sheet.

Reducing #REF errors wasn't mentioned in any of the lists, so I leave it here for others, even though this answer does NOT answer my original question.

Reorganizing my table and replacing the queries with vlookups has resulted in a much faster response. If I'm entering inventory quickly (3 cells per item, with a couple dozen lookups to complete the row. I can get 4-5 rows ahead of the lookups. It catches up in about 15 seconds now.

I could also have put a constrain on the query, but the nature of queries makes me think it will have to read ALL the data every time. Also the entire formula has to be parsed every time, and queries appear to use two different syntaxes, and so I suspect that parsing a query takes longer than parsing a vlookup. This doesn't matter if the parse results are cached.