Appscript adding single quotes inside a query formula

36 Views Asked by At

Hi everyone first time posting:

I wish to set following formula into a cell:

=QUERY({'tab1'!A2:K; 'tab2/variation with spaces or slashes'!A2:K}; "SELECT Col1, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col1 = 'Nieuw' AND Col2= TRUE ORDER BY Col3, Col4, Col6"; 0)

The output continuously excludes the single quotations:

=QUERY({tab1!A2:K; tab2/variation with spaces or slashes!A2:K};

I have tried the following and other variations but all of them keep excluding the single quotes.

var nieuwebSheet = ss.getSheetByName("Nieuwe boeken");

  // Set the formula in cell A2 of the "Nieuwe boeken" tab
  var formula = '=QUERY({';
  for (var i = 0; i < TabNames.length; i++) {
    if (i > 0) {
      formula += '; ';
    }
    formula += "'" + TabNames[i] + "'!A2:K";
  }
  formula += '}; "SELECT Col1, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11 WHERE Col1 = \'Nieuw\' AND Col2= TRUE ORDER BY Col3, Col4, Col6"; 0)';
  nieuwebSheet.getRange("A2").setFormula(formula);

I have also tried formula +='\'' + quotedTabNames[i] + '\'!A2:K';

I appreciate anyone having a look at this. It is part of a larger project so sharing the actual sheet is difficult. Thank you very mucht!

0

There are 0 best solutions below