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

Question

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

Answer 1

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

• Custom Functions in Google Sheets
• getDataRange