Google Sheets Query - build referenced array source dynamically

Question

I have many sheets in my spreadsheet (sheet1,sheet2,sheet3...) and I want to add them all to array, maybe based on any call range? Now I add them manually as below:

=query( 
{ 
INDIRECT("sheet1!$A$3:$V"); 
INDIRECT("sheet2!$A$3:$V"); 
INDIRECT("sheet3!$A$3:$V") };
"SELECT Col2, Col3, Col4, ...[etc]")

I want to create any "Settings" sheet and put here all sheets that should be in array, like this:

=query( 
    { 
    get_all_sheets_names_from('settings!A1:A100'); // something like this
 };
    "SELECT Col2, Col3, Col4, ...[etc]")

Is it possible?

My attempts: https://docs.google.com/spreadsheets/d/1ZMzu6FuVyAJiWfNIHW87OW1Vpg_mM_QdtGs9nq9UXCU/edit#gid=0

I would like the array with data sources to be taken from the G2:G column. The example in column C shows how this can be done manually. However, I am looking for a solution so that in the query nothing has to be done so that the query can drag an array with the names of the data source from G2:G

Answer 1

1) What i Think

I think it is not possible to use "INDIRECT" in the query parameters, because "INDIRECT" returns a cell reference and the parameters {(); ()} in a query are fixed objects.
An "INDIRECT" on a complete query is not possible either, for the same reason: a query does not return a reference on a cell.

2) Limited soluce

the principle: case1: look in column G the 3rd line (3rd source), if empty then test case 2, otherwise apply the formula with 3 sources.

case 2: if 2nd source is empty then go to case 1, otherwise apply the formula with 2 sources

case 1: if empty then display "no sources" otherwise apply formula with 1 source

Formula

note 1 replace ESTVIDE (fr) by ISBLANK (eng) !! note 2 : you can test with (G2="source1" and G3="source2),
but it works with G2="source3" and G3="source1"

=SI(ESTVIDE($G$4); SI(ESTVIDE($G$3); SI(ESTVIDE($G$1); "no source(s)";query({((INDIRECT("'"&G2&"'!A1:A5")))};"SELECT Col1")) ;query({(INDIRECT("'"&G2&"'!A1:A5"));(INDIRECT("'"&G3&"'!A1:A5"))};"SELECT Col1")) ;query({(INDIRECT("'"&G2&"'!A1:A5"));(INDIRECT("'"&G3&"'!A1:A5"));(INDIRECT("'"&G4&"'!A1:A5"))};"SELECT Col1"))

Online sheet

https://docs.google.com/spreadsheets/d/1sCwwFjpYKKzzAvVwmbMUWcmHSc1wY52XnHlFdT00A3U/edit?usp=sharing

Limitations

Off course, this is a formula with only 3 sources max ! It will be verry big and uggly with more sources...

Script

macro is the only solution ?

soluce with Macro

append this script, it gets value sources values from G2:G30 (you need more...put G100..)
it create the formula and put it on H2
it read max 50 value in each source (see A1:A50 in source code) it's not so hard to understand,

note : managing macro with GSheet is a another problem, if you needs advices, please post a comment.

link to live sheet : https://docs.google.com/spreadsheets/d/14XaR-UsADUpCUCVWqeg0zCbfGy3CCvnwVxUhozjYocc/edit?usp=sharing

function formula6() {
  var spreadsheet = SpreadsheetApp.getActive();

  var values=spreadsheet.getRange('G2:G30').getValues();

  var acSources="{";
  for (var i = 0; (i < values.length) && (values[i]!=""); i++) {
    if (i>0) {  acSources+=";" }
    acSources=acSources+'INDIRECT("'+values[i]+'!A1:A50")';
  }
  acSources=acSources+"}";

  var formula='query('+acSources+';"SELECT Col1")';

  spreadsheet.getRange('H2').activate();
  spreadsheet.getCurrentCell().setFormula('='+formula);

};