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...

CodePudding user response：

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);
}