There is no way to get the list of sheet names with a built-in spreadsheet function, not even for the current spreadsheet. You can use a script to fetch sheet names and create a formula automatically. The script given below adds a menu item Import > all sheets to the menu, when the spreadsheet is opened. When invoked, it will get the sheet names from the spreadsheet with URL contained in the script, and put the complex importrange formula in the cell A2 of the current sheet.
Aside: you don't need arrayformula in front of {...;...}
, and the query with select *
does not do anything useful.
function importall() {
var url = 'https://docs.google.com/spreadsheets/d/...'; // put URL here
var ss = SpreadsheetApp.openByUrl(url);
var imports = ss.getSheets().map(function(s) {
return 'importrange("' + url + '", "\'' + s.getName() + '\'!A2:Z")';
});
var formula = '={' + imports.join('; ') + '}';
SpreadsheetApp.getActiveSheet().getRange("A2").setFormula(formula);
}
function onOpen() {
SpreadsheetApp.getActiveSpreadsheet().addMenu("Import", [{name: "all sheets of doc X", functionName: "importall"}]);
}