Is there anyway to call an IMPORTRANGE() and have it include all sheets?

Question

Here is my current function that combines the current data with the first archive. I'd like to set it up as a failsafe for the future so when there's a new archive it will be included in the import:

QUERY({ARRAYFORMULA({IMPORTRANGE("****","'Current'!A2:Z");IMPORTRANGE("*****","'Archive1'!A2:Z")})},"select *") 

Is there a way to do that?

Answer 1

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