0

I have a Google Spreadsheet document with 15 sheets from which one has like 40 columns with ARRAYFORMULAS and other XLOOKUP and VLOOKUP functions from other sheets and is running slow with a delay that affects the way I can work with it.

Now, that sheet has only 30 rows and I would expect it to have like 50.000 at the end , but if it's working like this I don't know how I could handle more inputs into that sheet as it would be very hard to access it.

I've tried clearing the cache, site data and cookies from Chrome but it didn't do anything ... any suggestions on how I can improve this ?

I need the formulas in that sheet as some columns need to be calculated based on come new input and changes on other columns.

Ovi
  • 1
  • 3
  • ARRAYFORMULA's are slow by nature. Based on your data structure you may use `BYROW()` function to process data for each row till last non empty cell. Without seeing your current formula and data structure we can't suggest better. – Harun24hr Sep 30 '22 at 04:33
  • 1
    @Harun24hr using `BYROW` will raise the `LAMBDA` limitations problem as he get closer to 50,000 rows see [What factors determine the memory used in lambda functions?](https://stackoverflow.com/questions/73815258/what-factors-determine-the-memory-used-in-lambda-functions) – Osm Sep 30 '22 at 04:42
  • Would it help if I share a sample of the Spreadsheet ? – Ovi Sep 30 '22 at 04:55
  • 1
    @Ovi Keep in mind that your information (name, email) will be visible to anybody with access, and be certain that what you are sharing is not senstive. – Osm Sep 30 '22 at 05:03
  • 1
    @Ovi Asking volunteers to do optimizing work on your spreadsheet is demeaning. Start with the biggest formula and ask a new question on how to optimize it. You need to provide a [mcve] of your data as a markdown table and should've attempted to optimize it yourself(show it in your question).See if you can reproduce the same problem( that the formula is trying to solve) within a 3x2 input and output table. General recommendations: Delete all the empty rows at the bottom of the sheet, don't use `IF(A:A="", "", ...arrayformula)` Use arrayformulas like [this](https://stackoverflow.com/a/46884012/) – TheMaster Sep 30 '22 at 06:08
  • See [these spreadsheet optimization tips](https://support.google.com/docs/thread/102271339?msgid=102278707). – doubleunary Sep 30 '22 at 08:00
  • @TheMaster, I was not implying such a thing ... as you can see in my question, it was an honest question to the first reply and even if I would have shared the spreadsheet, it would be just a duplicate with empty content, only with the formulas and would not be for any custom work, just for having a look at the structure in order to save time and not to waste anyone's time to recreate the example ... just to let me know what could be improved. As I believe this is the purpose of a support community like this, but I'm new. – Ovi Oct 03 '22 at 06:54
  • @doubleunary do you know if there is a breakdown somewhere of the usage of all the formulas ? – Ovi Oct 03 '22 at 06:56
  • There is a list of resources at the end of the [optimization tips](https://support.google.com/docs/thread/102271339?msgid=102278707) post. – doubleunary Oct 03 '22 at 09:29

0 Answers0