I drastically improved spreadsheet performance by deleting all blank rows past row 100 (5,824 blank rows deleted), in the Spending Trends sheet.
Hot Tip - delete any blank rows in the Transactions sheet
I figured I’d share my experience with trouble shooting frustratingly slow performance. I wanted to see what I could do without doing the commonly recommended remove transactions (5,423) and Tiller solution tabs (9).
I discovered this problem spot by running the Google Sheet Size Audit Tool, which is one of the ideas listed in the Slow Google Sheets? Here are 27 Ideas to Try Today article I’ve seen referenced multiple times in this community.
Here are the results for my Tiller spreadsheet, which includes some commonly used Tiller solutions:
My spreadsheet is nowhere near Google sheets size limits (11% and 1%).
Spending Trends stood out in the audit results as easily the largest number of cells in a sheet, plus the stark difference vs number of cells with data (503,540 vs 611). It was easy at that point to know I needed to delete all the extra blanks rows (and I knew Spending Trends installs using 100 rows). Sheet performance became enjoyably snappy after that change - I was surprised this one change caused such a massive performance improvement.
I believe I introduced all the extra blank rows by playing with array type formulas, that combined with some Spending Trends helper formulas executing on all rows causing the slow down.
The other data that jumps out from the report is the high TODAY() count of 648 in the Net Worth solution tab. So, I followed another idea from the article to remove volatile functions by replacing multiple TODAY() function calls with cell references to one function call.
I actually found/replaced 1477 TODAY() function calls and I wasn’t able to determine why the audit tool only reported 648. I investigated the AppsScript and it just looks like a basic count loop.
Maybe @benlcollins could provide a possible reason for the difference?
Here is a snapshot of the one occurrence I added in cell
T5. I replaced all other TODAY() with
@heather I would think a similar change should be made to the official version of Net Worth.
Although, I didn’t notice a significant change in performance after this change, I imagine it can all add up.
This might be the next one I investigate, since it has the highest count of ArrayFormula (71). While I don’t currently have a performance reason to do that at the moment, I might check it out at some point to see what I can find.
For reference, these are the results after my two changes:
Lots of blank rows cause performance issues - delete them