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 !
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