How to Query ALL ever-expanding sheets without naming each one-CodePudding

I've got a large document with a built-in Lookup sheet, which allows me to query all sheets in order to find row information about a particular person

Example doc here: https://docs.google.com/spreadsheets/d/1cn-LnKCauoBMxLCaJyfTg8t0qS2bsMAAYqTE5FHA-4M/edit#gid=1006641306

This sheet has a query where I combine LOTS of sheets together, using a custom formula sheetname() to grab the name of the sheet and also to dynamically select all the rows of that sheet (it also adds the sheet name as a column). This worked fine for a few sheets, but now I'm over 30 sheets, and it's exhausting to add more to this formula

Is there any way to shorten this formula so that I don't need to add a new line for every single new sheet I want to query?

=QUERY( {{indirect(sheetnumber(3)&"!$A$3:$e$"&counta(indirect(sheetnumber(3)&"!A3:A"))), TRANSPOSE(SPLIT(REPT(sheetnumber(3)&"♦",
ROWS(indirect(sheetnumber(3)&"!$A$3:$e$"&counta(indirect(sheetnumber(3)&"!A3:A"))))), "♦"))}; {indirect(sheetnumber(4)&"!$A$3:$e$"&counta(indirect(sheetnumber(4)&"!A3:A"))), TRANSPOSE(SPLIT(REPT(sheetnumber(4)&"♦", ROWS(indirect(sheetnumber(4)&"!$A$3:$e$"&counta(indirect(sheetnumber(4)&"!A3:A"))))), "♦"))}; {indirect(sheetnumber(5)&"!$A$3:$e$"&counta(indirect(sheetnumber(5)&"!A3:A"))), TRANSPOSE(SPLIT(REPT(sheetnumber(5)&"♦", ROWS(indirect(sheetnumber(5)&"!$A$3:$e$"&counta(indirect(sheetnumber(5)&"!A3:A"))))), "♦"))} }, "select Col6,Col2,Col3,Col4,Col5 where lower(Col1) contains '"& lower($A$2) &"' ",0)

Thanks in advance for your ideas!

CodePudding user response：

This is a very common question.

The real answer is "Don't build sheets like this". That is - data entry should always happen on ONE tab, forever, for all time. Adding new sheets for new data entry is a method left over from leather bound ledgers in the 16th century.

But most people do not like that answer. So I wrote a custom function called "StackRange()". Especially used in conjunction with another simple custom function "SheetNames()" it will do what you want.

Here is a View Only version. Feel free to File>Make a Copy so you can access the script. You will see the function implemented on two tabs, one that uses "SheetNames()" one that uses a rangeRef to accomplish the same thing.

Here is the code for StackRange:

/**
 * Stacks a given range from a list of sheets.  Ranges can be closed (A2:G10), or open-ended (C2:T)
 * Example: =STACKRANGE("A2:C",Settings!B2:B10,1,TRUE)
 * @param {"A2:C"} range The range in each sheet that will be stacked ("string")
 * @param {Settings!B2:B10} tabs A range reference or array listing the tabs from which to stack
 * @param {1} headers The number of header rows
 * @param {TRUE} source TRUE to pre-pend the source tab name to each row
 * @customfunction
 */
function STACKRANGE(range,tabs,headers,source) {
  var ss = SpreadsheetApp.getActive();
  var sheetNames = ss.getSheets().map(e=>e.getName());
  var currentSheetName = ss.getActiveSheet().getName();
  tabs = tabs.filter(e=>e[0] && sheetNames.indexOf(e[0])!=-1 && e[0]!=currentSheetName);
  var values = tabs.flat().map((e,i)=>ss.getSheetByName(e).getRange(range).getValues().map(function (g,k){
    if(!source){return g}else if(k<headers && i==0 && headers){return ['Source',...g]}else{return [e,...g]}
  }).filter((f,j)=>j>=headers || i==0)).flat();
  if(source){values = values.filter(e=>e.slice(1).join(''))}else{values = values.filter(e=>e.join(''))}
  return values;
}