Google-sheets – Appending the content of one cell to another cell

Indeed, the first invocation of importrange cannot be nested within another function: the request for authorization does not "bubble up" (at first I thought this was an error on Google's part, but then considered that a single command can combine several importrange within it, so providing an authorization dialog in such a case would be extra complicated).

But you don't need a separate sheet to handle authorization. Just make the first call to the spreadsheet a single cell request: importRange("key","A1"). Then this can be replaced with the countunique that you want. Or, you can have a column with keys, and another column with a single cell request for authorization purpose.

First, you don't need query in your formula, it should be simply

=sort({Sheet2!A3:F; Sheet3!A3:F; Sheet4!A3:F; Sheet5!A3:F}, 1, true)

Unfortunately, it is impossible for such a formula to take a string parameter {Sheet2!A3:F; Sheet3!A3:F; Sheet4!A3:F; Sheet5!A3:F} from another cell. Normally one would use indirect(A2) for this, but indirect does not support array notation {...}, it only works with individual ranges like indirect("Sheet2!A3:F"). Using {indirect("Sheet2!A3:F"); indirect("Sheet3!A3:F")} is possible but this doesn't help with your problem.

Suggestion: since you will need a script to generate the list of all sheets anyway, use it to create the sort formula directly. Here is a function that does this. It gets all sheets, gets their names, excludes certain sheets (including the "masterSheet" where the formula will be located, to avoid cyclical dependency), appends the range to each sheet name (A3:F here), single-quoting sheet names for safety, and then puts all this in the sort formula in cell A3 of masterSheet.

function combineSheetsFormula() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = "A3:F";
  var masterSheet = "Sheet1";
  var excluded = ["Sheet1", "Sheet2", "Sheet3"];
  var sheetNames = ss.getSheets().map(function(sheet) {
    return sheet.getSheetName();
  }).filter(function(name) {
    return excluded.indexOf(name) == -1;
  }).map(function(name) {
    return "'" + name + "'!" + range;
  });
  var formula = "=sort({" + sheetNames.join(";") + "}, 1, true)";
  ss.getRange("'" + masterSheet + "'!A3").setFormula(formula);
}

This function combineSheetsFormula should be launched as explained here, via a function sheetChange that is triggered "on change" and launches the formula function if the change was adding or deleting a sheet.

function sheetChange(e) {
  if (e.changeType == "INSERT_GRID" || e.changeType == "REMOVE_GRID") {
    combineSheetsFormula();
  }
}