Apply IMPORTRANGE to filtered array

Question

I have a series of Sheet IDs that I want to import data from. The list is dynamic. I want to pull columns A:C from each sheet in that array.

I have tried using arrayformula and a map-lambda formula. Arrayformula pulls data from the first sheet, and map-lambda returns "Result should be a single row." error.

I have sheet1 with column A:A that holds the sheet IDs. On a different tab, I want to pull data from each sheet ID, tab sheet1, columns A:C.

Each array is variable - the number of entries in the sheet IDs can increase/decrease and the number of rows in A:C can vary (the number of columns is always the same).

I would love any input on this. Thanks !

Answer 1

Assuming:

Sheet 1→Column_A has the Sheet_IDs

you wish to import from tab Sheet 1→A:C columns from all target Sheet_IDs

=reduce(tocol(,1),tocol(Sheet1!A:A,1),lambda(a,c,vstack(a,let(Σ,importrange(c,"Sheet1!A:C"),filter(Σ,byrow(Σ,lambda(Λ,counta(Λ)>0)))))))

logic:

tocol(Sheet1!A:A,1) →. picking sheet IDs from here

vstack(a,let(Σ,importrange(c,"Sheet1!A:C" →. stacks vertically one sheet data (3 columns) at a time

filter(Σ,byrow(Σ,lambda(Λ,counta(Λ)>0)))) →. eliminating lines where all the cells(in a row) are blank