2

I have this (insanely) long formula I need to run in Google Sheets, and I came across the limit error:

There was a problem

Your input contains more than the maximum of 50000 characters in a single cell.

Is there a workaround for this?

my formula is:

=ARRAYFORMULA(SPLIT(QUERY({B!A1:A100; ........ ; CA!DZ1:DZ100}, 
 "select * where Col1 is not null order by Col1 asc", 0), " "))

full formula is: pastebin.com/raw/ZCkZahpw

apologies for Pastebin... I got a few errors here too:

note 1: due to fact that it's a long formula, the output from it should be of size ~100 rows × 3 columns note 2: so far I managed to bypass JOIN/TEXTJOIN for 50000+ characters even 500000 limits for total cells

Community
  • 1
  • 1
player0
  • 124,011
  • 12
  • 67
  • 124
  • 4
    If your formula is in excess of 50000 characters then you might need to go back to the drawing board and rethink your approach. Can you give us step-by-step break down of what you are trying to achieve? We can't provide any guidance without knowing what your goals are. – TheAddonDepot Feb 16 '19 at 21:56
  • 1
    @DimuDesigns answer updated. step-by-step break down: every range contains either empty cells (eg. nothing) or mix of empty cells and cells with 3 words. goal is to construct array with `{}` and filter out empty cells from that array with query. then split those 3-word cells into 3 columns of words – player0 Feb 16 '19 at 22:29
  • 1
    If the rest of the cells are empty in each range, create a open range: `A!A:A` instead of `A!A1:A100`. That's almost a 50% reduction in size. – TheMaster Feb 17 '19 at 07:52
  • 1
    Also remove `select *`, `asc`, etc. `"where Col1!=''order by Col1"` should be enough. I still don't think you'll reach <50k limit. But, still worth a shot. – TheMaster Feb 17 '19 at 08:02
  • I'm voting to close this question as off-topic because it's asking us to recommend a work around to a Google limit which seems more than reasonable. – Cooper Mar 08 '19 at 19:53
  • @Cooper "because it's asking us to recommend a workaround to a Google limit which seems more than reasonable." dude... don't twist my words to fit your justification – player0 Mar 08 '19 at 20:08

2 Answers2

4

Is there a script to bypass 50000 characters for in-cell formula?

If the length of {B!A1:A100; ........ ; CA!DZ1:DZ100} is greater than 50 thousands characters consider to build a custom function that build the array for you. You could "hard-code" the references or list them as text on a range to be read by your script.

Then, the resulting formula could look like this:

 =ARRAYFORMULA(SPLIT(QUERY(MYCUSTOMFUNCTION(), 
 "select * where Col1 is not null order by Col1 asc", 0), " "))

or like this

 =ARRAYFORMULA(SPLIT(QUERY(MYCUSTOMFUNCTION(A1:A1000), 
 "select * where Col1 is not null order by Col1 asc", 0), " "))

(assuming that you have 1000 references).

A custom function works because it on the Google Sheets side instead of having a formula that exceeds the cell content limit it will use just few characters and because by using good practices it's possible to make that it takes less than the 30 seconds time execution limit for them.

It's worth to note that if the MYCUSTOMFUNCTION() variant (without arguments) is used, it only will be recalculated when the spreadsheet is opened but the MYCUSTOMFUNCTION(A1:A1000) variant (with a range reference as argument) will be recalculated every time that a cell in the range reference changes.

References

S.S. Anne
  • 15,171
  • 8
  • 38
  • 76
Rubén
  • 34,714
  • 9
  • 70
  • 166
2

UPDATE:

I managed to enter up to 323461 characters as a formula! by using CTRL + H where I replaced simple =SUM(1) formula with my huge formula from this answer: https://webapps.stackexchange.com/a/131019/186471

___________________________________________________________

after some research, it looks like there isn't any workaround to pull this of.

recommended savings that were suggested ( shortening: A!A:A, dropping: select *, asc, shortening: "where Col1!=''order by Col1") reduced it a bit and rest was split into two formulas in VR {} array solution.

player0
  • 124,011
  • 12
  • 67
  • 124