Hi All,
I’ve struggled for a while with timeout problems on transaction fills – interactive and unattended – with a large sheet that has a bunch of additional complex formulas applied.
My sheet has 28k rows (since Jan 2019) and 31 linked accounts. In addition, I have 4 columns that populate with =ARRAYFORMULA() using nested vlookup, xlookup and some string functions to provide additional context on my transactions. As the sheet got bigger (say, >20k rows) I had intermittent problems with failures to fill.
Converting all of the columns to ARRAYFORMULA() instead of copy-pasting the formulas like I did initially managed to tide me over from ~20k to ~25k rows, but wasn’t a permanent solution.
My next solution was to comment out my additional formula columns to avoid the lengthy recalcs – that worked, but was a small PITA because I had other sheets that did things like pivot on those columns.
I came up with a better solution today that seems to be working well – I make those ARRAYFORMULA() columns conditional on a flag – in my case, a cell on my Balance sheet.
Here’s an example column that I use to check for dupes (an artifact of these failures to fill…):
=if(Balances!$N$26=true,“”,arrayformula(if($B:$B=“Date”,“Dupe?”,if($B:$B<>“”,countif(S:S,$S:$S),“”))))
So Balance!N26 is simple a true/false, and if it is true then my complex arrayformula() columns don’t fill, which makes recalc much faster, which allows the transaction fill to process within the normal timeout window.
Hope this helps someone else