How to build a table of imported data from conditional data sources

Question

I have many spreadsheets where data will be added manually and which are all identical. Let's call these UserInputSpreadsheets.

Inside each user spreadsheet are tabs with data which are also identical.

Let's call these tabs Data Input Sheets.

ALl DataInputSheets are identical.

Seach UserSpreadsheet has 1 compilation tab, which compiles all the data in the DataInputSheets. Let's call them UserSpreadsheetCompilationTabs

Here's an example

User Input Spreadsheet 1

fig.1
DataInputSheet 1
date      | name | value
-----------------------
1/12/2022 | Foo  | bar
1/13/2022 | Baz  | Quox

fig.1
DataInputSheet 2
date      | name | value
-----------------------
1/12/2022 | Burr | Bazz
1/13/2022 | Butts| Bums

UserSpreadsheetCompilationTab1 

fig.3
date      | name | value
-----------------------
1/12/2022 | Foo  | bar
1/13/2022 | Baz  | Quox
1/12/2022 | Burr | Bazz
1/13/2022 | Butts| Bums

Now imagine there are 10 of these Spreadsheets that exist and all are going to be compiled into a MasterSpreadsheet.

Here is what the MasterSpreadsheetCompilationTab should look like if we have 2 spreadsheets, a 2nd that you cannot see, and the first one included using the sample data above.

fig.4
date      | name | value
-----------------------
1/12/2022 | Foo  | bar
1/13/2022 | Baz  | Quox
1/12/2022 | Burr | Bazz
1/13/2022 | Butts| Bums
1/15/2022 | Grr  | Arr

However, the number of UserInputSpreadsheets is a variable quantity and will definitely adjust over time. So I need the MasterSpreadsheetCompilationTab to be able to adjust its output based on this.

Therefore I created a manifest that lists the URLs of the UserInputSpreadsheets. Let's call it the Manifest

It looks like this:

fig.5
Slots | User name | UserInputSpreadsheetURL
--------------------------------------
1     | Nickypoo  | https://agooglesheeturl...
2     | Bennyboo  | https://agooglesheeturl...
3     | Hoopadoo  | https://agooglesheeturl...
4     | -         | -
5     | -         | - 

The MasterSpreadsheetCompilationTab needs to look at this Manifest and only display data that has a URL listed on this Manifest tab.

I think I'm pretty close with this function:

=reduce(
   importrange(Manifest!B3, "Compiled!A2:AE"), 
   filter(Manifest!B4:B12, isblank(Manifest!B4:B12)=false), 
   lambda(array, url, {
      array; query(importrange(url, "Compiled!A2:AE"), "select * where Col1 is not null")}))

However, it is only showing the first set of data in the first argument of the reduce function.

Essentially the MasterSpreadsheetCompilationTab looks like this if I use the sample data from above:

fig.6
date      | name | value
-----------------------
1/12/2022 | Foo  | bar
1/13/2022 | Baz  | Quox

I need it to look like fig 4.

Answer 1

Use reduce() to merge several multi-row arrays into one taller array, like this:

=reduce( 
  importrange(C3, "Compiled!A1:AE1"), filter(C3:C14, B3:B14), 
  lambda( 
    result, url, 
    { 
      result; 
      query( 
        importrange(url, "Compiled!A2:AE"), 
        "where Col1 is not null", 0 
      ) 
    } 
  ) 
)