Javascript / Googlescript using set.Formula with complex formulas

0
votes

I have researching this but cannot find a suitable solution. The following formula works fine at the formula level when placed in a sheet cell. The issue is I want the formula to run at the script level. The options I am aware of include running a script to: (1) set.Formula('=complex formula') or (2) rewriting the entire formula as a script

I am new to GAS, and have messed around with both methods. There seems to be a syntax error when using option (1), usually in the form of a missing ")" that I cannot debug. Employing option (2) is currently above my skill level. Any help on either option would be greatly appreciated.

Here is the formula in question:

=ARRAYFORMULA(QUERY({UI!A:G,YEAR(UI!A:A),MONTH(UI!A:A), TEXT(UI!A:A, "MMMM"), TEXT(UI!A:A, "MMM-YY"), REPLACE(UI!A:A,1,1000,"GRAND TOTAL")}, "SELECT * WHERE Col1 IS NOT NULL AND Col2 IS NOT NULL LABEL Col8 'Year',Col9 'MonthMO#',Col10 'MonthMO',Col11 'MonthMOYR',Col12 'GRAND TOTAL'"))

1
multidimensional-arraygoogle-apps-scriptgoogle-sheetsformulasarray-formulas
if it only needs to work in newer browsers, you can use set.Formula(`=complex formula`) syntax (note the grave accents instead of apos/quote) this lets you avoid the toothpick problem. en.wikipedia.org/wiki/Leaning_toothpick_syndrome - dandavis
tried set.Formula(=complex formula) using chrome version 46.0.2490.80 m. with no luck. The script editor returns "illegal character" error on testing. - robazefa
tried set.Formula using grave accents (for some reason i cannot get them to show up in my comment here) using chrome version 46.0.2490.80 m. with no luck. The script editor returns "illegal character" error on testing. - robazefa
you need to put that code at the bottom in the grave marks. you can then space it out with linebreaks and not worry as much about nested quotes. - dandavis
i used the code with grave marks as you mentioned....the code line will not even save because the source formula has/requires both ' and " within the code, and seems to want to be delimited by " although it is invalid to use " bc it is also part of the code inside. when i use the grave marks instead i still get the illegal character error - robazefa

1 Answers

1
votes

Answer

Formulas doesn't run "at script level" so you will have to rewrite your formula as a Google Apps Script / JavaScript function.

Escaping formula apostrophes in scripts

In case that you want a script to add your complex formula to a cell bear in mind that writing complex formulas in one line makes harder to debug them. Try dividing your formula by functions and parameters and use a tab to align them. IMHO this prevents that the use of \ makes the script unreadable (Leaning toothpick syndrome).

Below is a onEdit() function that inserts the complex formula in the question to the cell to the right of a cell where 'Yes' is wrote. Look to the use of \ to escape the apostrophes used in the second parameter of the query function (select statement).

function onEdit() {
  var ss = SpreadsheetApp.getActive();
  var rng = ss.getActiveRange();
  var trg = rng.offset(0, 1);
  var formula = 
    '=ARRAYFORMULA('
       + 'QUERY({'
            + 'UI!A:G,YEAR(UI!A:A),MONTH(UI!A:A),' 
            + 'TEXT(UI!A:A, "MMMM"), '
            + 'TEXT(UI!A:A, "MMM-YY"), '
            + 'REPLACE(UI!A:A,1,1000,"GRAND TOTAL")'
          + '},' 
          + '"SELECT * WHERE Col1 IS NOT NULL AND Col2 IS NOT NULL LABEL Col8 \'Year\', '
            + 'Col9 \'MonthMO#\', Col10 \'MonthMO\', Col11 \'MonthMOYR\', '
            + 'Col12 \'GRAND TOTAL\'"'
       + ')'
     + ')';
  if(rng.getValue() == 'Yes') trg.setFormula(formula);
}