0

Finding answers on Web Apps SE or Reddit is wonderful, but if you don't understand them you can't modify them.

I recently came across the following as part of an answer:

={"Corrected List 2";ArrayFormula(IF(LEN(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(REGEXMATCH(E2:E,"("&TEXTJOIN("|",1,FILTER("(?i)"&A2:A,LEN(A2:A)))&")"),REGEXREPLACE(E2:E,"("&TEXTJOIN("|",1,FILTER("(?i)"&A2:A,LEN(A2:A)))&")","♥$1♥"),)),,1E+100)))),SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IFERROR(VLOOKUP(IFERROR(SPLIT(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(REGEXMATCH(E2:E,"("&TEXTJOIN("|",1,FILTER("(?i)"&A2:A,LEN(A2:A)))&")"),SUBSTITUTE(REGEXREPLACE(E2:E,"("&TEXTJOIN("|",1,FILTER("(?i)"&A2:A,LEN(A2:A)))&")","♥$1♥")," ","♦"),)),,1E+100))),"♥",0,0)),{A2:A,B2:B},2,0),IFERROR(SPLIT(TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(REGEXMATCH(E2:E,"("&TEXTJOIN("|",1,FILTER("(?i)"&A2:A,LEN(A2:A)))&")"),SUBSTITUTE(REGEXREPLACE(E2:E,"("&TEXTJOIN("|",1,FILTER("(?i)"&A2:A,LEN(A2:A)))&")","♥$1♥")," ","♦"),)),,1E+100))),"♥",0,0)))),,1E+100)))," ",""),"♦"," "),E2:E))}

My mental parser is in overload. It doesn't help that is is in effect self writing code, with string versions of what would otherwise be reserved words. and that it uses Google query.

The first step to understanding this is to tease it apart, and that, for me needs some kind of pretty printer. Google wasn't helpful.
One similar here: How can I pretty-print a formula in Google Sheets? is rather simplistic, is in VBA code and I don't thing understands the syntax overload of query.

The sheet was posted on reddit. https://docs.google.com/spreadsheets/d/1iMsMs8mPHFZIcBPV1vGzWEHd9RnO18VbdGwQ5HkR1ak/edit#gid=0

The formula is a green box Corrected List 2.

I asked a question there, that I had asked here. This sheet was in response to this question:

More compact version of nested substitute function possible?

1

There isn't a "pretty print" built-in feature for Google Sheets formulas. IMHO opinion the simplest way to improve formula readability are those already described in How can I pretty-print a formula in Google Sheets? and How to build formula in Google Sheets using content of other cells:

Insert spaces and breaklines

If you edit the formula to introduce new breaklines / spaces, you should include and another innocuous change like

  • add a zero +0
  • multiply by one *1,
  • append an empty string (&"")
  • change a reference from uppercase to lowercase a1 to A1
  • change a relative reference to an absolute reference or viceversa A1 to $A$1 or to $A1 or to A$1

If you edit the formula again, you could revert the innocuous change

NOTE

For matching parenthesis, you could use the Google Sheets formula bar: put the insertion at the right of an opening parentheses, then the matching parentheses will be highlighted. Then use the breaklines/spaces to indent and align each parentheses pair.

Another alternative, if you feel confortable with Google Apps Script editor to write long strings, you could write your formulas using it, and then use Google Apps Script function to add the formula to the spreadsheet (setFormula(formula) / setFormulas(formula[][])).

One more alternative is to use a "pretty-print" tool for Excel formulas. The best-one will be one that is able to work with custom functions, so a Google Sheets functions that aren't supported by Excel could be treated as custom functions.

Some tools for Excel were suggested in SO: Pretty Print Excel Formulas?. Some time ago I tried at least one of them; I don't remember how many. At that time I didn't find one that fits my needs.


I "ported" the code in an answer from VBA to Google Apps Script. The prettify function returns a formula with a breakline after each curly bracket, parentheses and after operators like +, -, *, /, ^, &. The test function takes the formula of the current cell and add the prettified version to the first cell to the right.

function test(){
  var cell = SpreadsheetApp.getCurrentCell();
  var formula = cell.getFormula();
  cell.offset(0,1).setFormula(prettify(formula));
}

function prettify(formula){
  var pretty = '';
  var tabNum = 0;
  var tabOffset = 0;
  var tabs = [];
  formula.split('').forEach(function(c,i){
    if(/[\{\(]/.test(c)){
      tabNum++;
      tabs[tabNum] = (tabs[tabNum - 1] ? tabs[tabNum - 1] : 0) + tabOffset + 1;
      tabOffset = 0;
      pretty += c + '\n' + ' '.repeat(tabs[tabNum]);
    } else if(/[\}\)]/.test(c)){
      tabNum--;
      pretty += c + '\n' + ' '.repeat(tabs[tabNum]);
      tabOffset = 0;
    } else if (/[\+\-\*\/\^,;&]/.test(c)) { 
      pretty += c + '\n' + ' '.repeat(tabs[tabNum]);
      tabOffset = 0;
    } else {
      pretty += c;
      //tabOffset++;
    }
  });
  return pretty;
}

NOTE:

  1. tabOffset++ is commented out because it cause that prettified version of the formula in the question exceeds the 50000 character length cell's limit.
  2. String.prototype.repeat is a method introduced in ECMAScript 6 and it's not natively supported but you could add the polyfill in https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/String/repeat
  3. The prettified version could include breaklines in text arguments that include parentheses / curly brackets that should be removed and in numbers that use 1E+10 notation that should be manually fixed.

Anyway,

Using very large / convoluted formulas should be avoided whenever it be possible because:

  1. They are hard to read, maintain and troubleshot (this is a "code smell")
  2. Usually they make the spreadsheet recalculation slower (this is another "code smell")

So, if you are really needing a tool for pretty-printing formulas, you should ask yourself if you are using the best approach to do what your formula does.

| improve this answer | |
  • A: As a diagnostic tool it doesn't have to be within the app. I'm quite willing to cut and past a formula so that I can figure it out. But I'm still figuring out matching parentheses in the one quoted. Yes, it's bad code. But I have to understand it before I can fix it. – Sherwood Botsford Jan 17 at 23:22
  • 1
    @SherwoodBotsford For matching parenthesis, you could use the Google Sheets formula bar, put the insertion at the right of an opening parentheses, then the matching parentheses will be highlighted. Then use the breaklines/spaces to indent and align each parentheses pair. – Rubén Jan 17 at 23:32
  • Write that up as a partial answer. That's cool. – Sherwood Botsford Jan 18 at 19:50
  • @SherwoodBotsford I edited my answer. – Rubén Jan 19 at 6:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.