1

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

Improve this question

3 Answers 3

Reset to default
2
+50

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

};
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

2

dudes who copy-pasted INDIRECT function into Google Sheets completely failed to understand the potential of it and therefore they made zero effort to improve upon it and cover the obvious logic which is crucial in this age of arrays.

in other words, INDIRECT can't intake more than one array:

=INDIRECT("Sheet1!A:B"; "Sheet2!A:B")

nor convert an arrayed string into active reference, which means that any attempt of concatenation is also futile:

=INDIRECT(MasterSheet!A1:A10)
————————————————————————————————————————————————————————————————————————————————————
=INDIRECT("{Sheet1!A:B; Sheet2!A:B}")
————————————————————————————————————————————————————————————————————————————————————
={INDIRECT("Sheet1!A:B"; "Sheet2!A:B")}
————————————————————————————————————————————————————————————————————————————————————
=INDIRECT("{INDIRECT("Sheet1!A:B"); INDIRECT("Sheet2!A:B")}")

the only possible way is to use INDIRECT for each end every range like:

={INDIRECT("Sheet1!A:B"); INDIRECT("Sheet2!A:B")}

which means that the best you can do is to pre-program your array like this if only part of the sheets/tabs is existant (let's have a scenario where only 2 sheets are created from a total of 4):

=QUERY(
 {IFERROR(INDIRECT("Sheet1!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet2!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet3!A1:B5"), {"",""}); 
  IFERROR(INDIRECT("Sheet4!A1:B5"), {"",""})}, 
 "where Col1 is not null", 0)

so, even if sheet names are predictable (which not always are) to pre-program 100+ sheets like this would be painful (even if there are various sneaky ways how to write such formula under 30 seconds)

an alternative would be to use a script to convert string and inject it as the formula

A1 would be formula that creates a string that looks like real formula:

=ARRAYFORMULA("=QUERY({"&TEXTJOIN("; "; 1; 
 FILTER(SNAME(1); SNAME(1)<>SNAME(0))&"!A1:A20")&"}; ""where Col1 is not null""; 0)")

then this script will take the string from A1 cell and it will paste it as valid formula into A2 cell:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName('query');  // MASTER SHEET NAME
var src = sheet.getRange("A1");                                  // COPY STRING FROM
var str = src.getValue();
var cell = sheet.getRange("A2");                                 // PASTE AS FORMULA INTO 
cell.setFormula(str);
}
function SNAME(option) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet()
var thisSheet = sheet.getName(); 
if(option === 0){                  // ACTIVE SHEET NAME =SNAME(0)
return thisSheet;
}else if(option === 1){            // ALL SHEET NAMES   =SNAME(1)
var sheetList = [];
ss.getSheets().forEach(function(val){
   sheetList.push(val.getName())
});
return sheetList;
}else if(option === 2){            // SPREADSHEET NAME  =SNAME(2)
return ss.getName();

}else{
return "#N/A";                     // ERROR MESSAGE
};
}

0

of course, the script can be changed to onOpen trigger or with custom name triggered from the custom menu or via button (however it's not possible to use the custom function as formula directly)

this will cover all your needs to not edit the formula by adding references if new sheets are added. the only drawback is a recalculation of sheet name script... to do so you need to dismantle A1 formula for example by adding ' before the leading = pressing enter and then removing it to fix the formula

spreadsheet demo

Improve this answer

Comments

0

with new functions that were added, we can do this easily:

=REDUCE(HSTACK(,,), INDEX(A2:A3&TEXT(B2:B3, " mm/dd")), 
 LAMBDA(a, b, {a; INDIRECT(b&"!A1:C5")}))

enter image description here

to fix issue with sheets that do not exist yet:

enter image description here

we can add IFERROR like:

=REDUCE(HSTACK(,,), INDEX(A2:A5&TEXT(B2:B5, " mm/dd")), 
 LAMBDA(a, b, {a; IFERROR(INDIRECT(b&"!A1:C5"), HSTACK(,,))}))

enter image description here

to not repeat headers we can do:

=REDUCE({"Date", "Test Name", "Test Data"}, INDEX(A2:A5&TEXT(B2:B5, " mm/dd")), 
 LAMBDA(a, b, {a; IFERROR(INDIRECT(b&"!A2:C5"), HSTACK(,,))}))

enter image description here

instead of A2:B5 we can use:

=SEQUENCE(5, 1, "2019-01-06", 7)

enter image description here

directly it would be:

=REDUCE({"Date", "Test Name", "Test Data"}, 
 INDEX("week of "&TEXT(SEQUENCE(5, 1, "2019-01-06", 7), "mm/dd")), 
 LAMBDA(a, b, {a; IFERROR(INDIRECT(b&"!A2:C5"), HSTACK(,,))}))

enter image description here

and with QUERY for OP:

=QUERY(REDUCE({"Date", "Test Name", "Test Data"}, 
 INDEX("week of "&TEXT(SEQUENCE(5, 1, "2019-01-06", 7), "mm/dd")), 
 LAMBDA(a, b, {a; IFERROR(INDIRECT(b&"!A2:C"), HSTACK(,,))})), 
 "where Col3 = 'RP' order by Col2", 1)

enter image description here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.