Online Active Performance Monitor on sheets

Jeff Reisman

I recently ran into a situation where important automated row moves were getting stuck for sometimes hours at a time, instead of moving immediately based on their automations trigger.

The structure was:

Automated Move Row 2, from the Intermediate Sheet to the Final Landing Sheet, was the one getting stuck. I had experienced trouble before due to too many complex formulas in the Final Landing Sheet, but eventually had it working fine by eliminating most of those, so it didn't make sense for it to just stop now.

After hours of troubleshooting, I finally discovered that a simple two-criteria COUNTIFS on the Metric Sheet, used for counting rows in the Final Landing Sheet, was preventing the normal move of rows from the Intermediate Sheet!

This made me think about having some kind of performance and process monitor for a sheet, similar to the Processes and Performance tabs in Windows Task Manager. This monitor would show the sheets that have cell links or formula connections behind the scenes, and display the processing overhead between the sheets and which formulas or cell links are using the most processor and memory. A user would then have the ability to quickly find these blockers and figure out how to spread the load better or create more efficient formulas.

(In this case from yesterday, I copied the rows from the Final Landing Sheet to a new Final FINAL landing sheet with no formulas, and pointed the Metric Sheet COUNTIFS formula at the Final FINAL sheet instead. As soon as I updated the cross-sheet references in the COUNTIFS, the hundreds of stuck rows in the Intermediate Sheet moved to the Final Landing Sheet as designed. That's how I figured out what the culprit was. I added a copy row automation to the Final Landing Sheet to automate the population of Final FINAL, and then watched as everything moved as it should when it should.)