-4

I have a spreadsheet that I have been developing for about a year and a half - and it's expanded well beyond what I had initially envisaged.

Currently I have 45 tabs containing either charts, queries, numerous conditional formatting rules, and/or complex formulae that run calculations across multiple sheets. I also have 97 functions over 4029 lines of code in the .GS

Most of the functions are small and are designed to gather a few pieces of information, and then create new entries with that information. However functions are now running extremely slowly and the spreadsheet is taking up a massive Memory footprint:

Memory footprint

I've spent a lot of time doing research on optimising code etc and I've pretty much hit a wall with it. I'd like to learn whether what I've created is simply too resource intensive for Google Sheets, or whether some of the practices I've used are causing bottlenecks (and if so hopefully learn of I can implement solutions).

Any tips, feedback or advice would be greatly appreciated.

Thanks

Aaron Irvine
  • 343
  • 3
  • 13

1 Answers1

3

Conditional formatting is actually a much bigger memory hog than a lot of people realize. I would start by eliminating as much of that as possible. If you're trying to use CF to "see what's important" it's better to just have large "naked" datasets, and then query's or other types of tables that extract and display important information rather than try to higlight the important stuff "in place" if that makes any sense.

As for the formulas, Arrayformulas() can make functionality much faster. Instead of "drag down" formulas, for many applications you can have a single formula that lives at the top of a column and computes everything for an entire column. that can produce huge savings.

Another idea is that you split your sheet up a little bit, and use IMPORTRANGE() formulas in strategic ways to pull large chunks of "offsite" data info to your sheet at once.

It's honestly pretty hard to say. I'm sure I could find things to optimize, if you wanted to share some portions of the sheet and the formulas you're using, someone here might be able to help you slim it down.

MattKing
  • 7,373
  • 8
  • 13
  • 2
    I'd start with File>Make a Copy. Then close the original. Then delete every single conditional formatting rule on every single tab on that new copy. then restart your browser and see if that Copy runs any faster. At least that will help you start isolating variables. – MattKing Mar 07 '20 at 14:04
  • Thanks for actually being helpful Matt - it's rare around here in this eliteist company. I don't even know if you can still see this question, but if you can it would be great to be able to chat more. Hard to find help around here – Aaron Irvine Mar 08 '20 at 21:27
  • I'd head here. A little friendlier and I'm active there as well. If you ask about me in the body of the question, one of the other volunteers might flag me down if I miss it. If not, there are a lot of capable people helping there. https://support.google.com/docs/thread/new?hl=en – MattKing Mar 09 '20 at 18:06
  • Awesome Matt, thanks so much for your help - and being friendly! :D – Aaron Irvine Mar 09 '20 at 19:08