Formula Generating Script Ignores Apostrophe Google Sheets GAS

February 16, 2022

I have a query where output is a condition met on all the tabs of a sheet.

QUERY({'01Dec2021'!A2:T},"select Col1 where Col1 !=''",1)

I wanted the sheet name to be included in the output, but only option seems to enter manually.

QUERY({'01Dec2021'!A2:T},"select Col1,'01Dec2021' where Col1 !=''",1)

I thought formula generating script can solve this problem which is this:

function Formula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var formula = "={";
  for (var i = 0; i < sheets.length; i++) {
    var name = sheets[i].getName();
    if (name != "Master List" && name != "Summary") {
      formula = formula +"QUERY({"+ name + "!A2:T},select Col1,'"+name+"' where Col1 !='',1);";
    }
  }
  formula = formula.slice(0, -1) + '}'
  ss.getSheetByName("Summary").getRange("A2").setFormula(formula);
}

and while this solved the problem, I just can’t figure out how to make the apostrophe as a text. I want to enter within apostrophe quotes like this "select Col1,’"+name+"’ where Col1 !=”" but I can’t do this without getting error.

formula = formula +"QUERY({"+ name + "!A2:T},select Col1,'"+name+"' where Col1 !='',1);"

I’d be thankful if someone can help me out to achieve this…

>Solution :

Use Template literals in your script with backticks

Template literals

in your script:

function Formula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheets = ss.getSheets();
  var formula = "={";
  for (var i = 0; i < sheets.length; i++) {
    var name = sheets[i].getName();
    if (name != "Master List" && name != "Summary") {
      formula = formula + `QUERY({'${name}'!A2:T},"select Col1,'${name}' where Col1 is not null label '${name}' '' ",0);`;
    }
  }
  formula = formula.slice(0, -1) + '}'
  ss.getSheetByName("Summary").getRange("A2").setFormula(formula);
}