Is There a Script to Bypass 50000 Characters for In-Cell Formula

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

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.

New Google spreadsheet Concatenate limit 50000 characters

Looking back to here, you can probably nest concatenate:

=TRANSPOSE(SPLIT(ARRAYFORMULA(CONCAT(CONCATENATE('Monthly link'!A10:A&CHAR(13) , 
'Monthly link'!R10:R&CHAR(13)), CONCATENATE('Monthly link'!AG10:AG&CHAR(13) ,
'Monthly link'!AU10:AU&CHAR(13)) )), CHAR(13)))

I simply added in one more CONCATENATE to combine the strings with only one value, then use CONCAT to combine those.

EDIT

This isn't exactly a fix, but reading google documentation states that you can create an old spreadsheet by going here.

EDIT 2

Try this code:

=TRANSPOSE(SPLIT(CONCAT(ARRAYFORMULA(CONCATENATE('Monthly link'!A10:A&CHAR(13), 
'Monthly link'!R10:R&CHAR(13))), ARRAYFORMULA(CONCATENATE(
'Monthly link'!AG10:AG&CHAR(13), 'Monthly link'!AU10:AU&CHAR(13)))), CHAR(13)))

It seems the error is coming from ARRAYFORMULA, has it has a limit of 50000. Not CONCATENATE or CONCAT. So, I use CONCAT to combine two different ARRAYFORMULAs that both house half of the original data. You can continue to divide these until there are even 4 ARRAYFORMULAs that all only have one dataset if need be.

EDIT 3

Currently, I am working on implementing a function in javascript found here.

You can test it currently by Tools->Script editor->Paste, then to run it go Tools->Script Manager->organizeData->Run.

I'll continue to work on it... it is currently not working, but I am close;)

EDIT 4

I finished it! You can see it here. You need to create a new script using the above instructions (Tools->Script editor->Paste), save it, then you can run it from the Script Editor window or from the spreadsheet by doing Tools->Script Manager->organizeData->Run.

What the script does is gets the data from the forms, puts it in the data to be copied, then it has a strange restriction where it requires a letter in the column to be able to copy it, so it adds a letter in so the script will fill the rows with "undefined". From there, all of the rows have "undefined" in them, so data can be copied to all of them.

If you want to know how to implement the script directly into a cell, you can just put:

=organizeData()

it will call the custom function! See here for more details.

Is there any formula length limitation in Google Sheets?

Is there any formula length limitation

yes there is but it can be surpassed - https://stackoverflow.com/a/55070275/5632629
so far my longest formula had 422 379 characters

are you sure you need to do it like that (unclear from your question) instead of just:

=TEXTJOIN("|", 1, A3:A3000)

or maybe even like (it really depends on what you want to do next):

=QUERY(A3:A3000,,999^99)

or perhaps:

=ARRAYFORMULA(A3:A3000&"|")

Steve Sanderson's Partial Request

The PartialRequest Invoke method doesn't return anything, it writes the HTML that the request generates to the response. The idea is that you can invoke a controller action that renders an MVC View User Control rather than a View. You can make a partial request to this controller action and have it render the control directly into the response stream rather than using the Html.RenderPartial. Since the control writes directly to the response, you don't have a chance to interact with it's output.

How to shorten a formula with a OR comparing several RIGHT functions in Google Sheets?

| can be used only in regex formulas. what about this:

=N(REGEXMATCH(A1, "\^$|\*$|@@$|%%$"))

---

Related Topics