Union multiple ranges in google sheets with programmatic lookup

1
votes

I have a google sheet with a dynamically changing number of worksheets within it. I'd like to be able to automatically union all worksheets that included in a named range that has the worksheet names.

I have a manual formula that is working and will union all data into a single worksheet. I'd like to make this computed from the named range.

UNION FORMULA

={QUERY('Sheet1'!A2:L, "select * where A != ''");QUERY('Sheet2'!A2:L, "select * where A != ''")}

I also have a named range in another sheet which has all the worksheet names

NAMED RANGE

WORKSHEET_NAMES = {Sheet1,Sheet2,Sheet3)

I also have previously used this to pull data from the worksheets using

Col A - this duplicates the range of worksheet names in column A
=QUERY(WORKSHEET_NAMES, "select A where A != ''")

Col B-Z - this looks up the worksheet name and pulls in the range from a string
=QUERY(indirect($A2&"!$A$2:$N"), "select * order by B desc limit 1")

I don't know if it's possible to (I've been searching for 1+ hours now) to combine my worksheet name lookup with my union operation, or if I'll need to resort to writing something in google scripts to allow me to do the union.

EDIT

Example Sheet

The "Union" sheet shows the desired output, but is a manual formula and not generated from the named range WORKSHEET_NAMES

The "Latest" sheet show the named range WORKSHEET_NAMES being used using an INDIRECT to pick a range from a string.

2
google-apps-scriptgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query
Perhaps you could post a link to a test sheet with sample data and desired output so we can make sure we know exactly what output we want. – CodeCamper
Will do, I'll try and get to it tonight – Neil
@CodeCamper that's me added the example sheet – Neil
I provided a messy solution without Google App Scripts, please let me know if it works for you. – CodeCamper

2 Answers

2
votes

without scripts you can build a formula generator in C1 like:

=ARRAYFORMULA({""; "={"&TEXTJOIN("; ", 1, 
 IF(A1:A="",,"QUERY("&A1:A&"!A2:C, ""where B != ''"")"))&"}"})

0

and then copy-paste C2 wherever you need

to skip re-copy-pasting you can use script:

function onEdit() { 
var sheet = SpreadsheetApp.getActive().getSheetByName("Contants");  
var src = sheet.getRange("C2");   // The cell which holds the formula
var str = src.getValue();
var cell = sheet.getRange("C10");  // The cell where I want the results to be
cell.setFormula(str);
}

0

so it will copy generated "formulaa"-string from C2 and it will paste it in C10 as true formula so all you need to do is type in sheet names in A column and everything gets auto-updated

spreadsheet demo

2
votes

Solution without Google App Script.

Requirements:

  • 3 individual helper cells
  • 1 helper column you will need to manually drag down each time you add a new sheet name, or you can just drag it down 1,000 times and that will be good for the next 1,000 sheets...

Using your scrap sheet as an example, you have a list in column A.

In cell B1 put =arrayformula(if(len("'"&A1&"'!A2:C")<1,"",query(transpose(substitute(query(substitute(indirect("'"&A1&"'!A2:C")," ","_"),"Select * where Col1 is not null",counta(array_constrain(indirect("'"&A1&"'!A2:C"),99^99,1)))," ","\")),"select * where Col1 is not null",99^99))) DRAG THIS FORMULA DOWN AS FAR AS YOU NEED SHEET NAMES YOU CAN OVERSHOOT

In cell C1 put =arrayformula(if(len(B:B)<1,"",split(B:B," ")))

In cell F1 put =arrayformula(query(transpose(substitute(query({C:E},"select * where Col1 is not null",counta(array_constrain(A:A,99^99,1)))," ","\")),"select *", 99^99))

In cell F2 put =arrayformula(substitute(transpose(split(transpose(split(F1," ")),"\")),"_"," "))

Now in cell F2 you will have your joined report dynamically updating as you add sheet names.