For the following function, is there a way to iterate through the sheet IDs without having to literally write out each block again - the stack is growing; the only difference is the next sheet ID?
Error handling for (1) if there is no sheet ID, or (2) if the query returns N/A is by inserting a blank row with same number of columns in the query.
=ArrayFormula({
IF(SpreadSheetKeys!B3="",
{"","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""},
IFERROR(QUERY(IMPORTRANGE(SpreadSheetKeys!B3,"DataTable!A3:CL2002"),
"Select Col1,Col2,Col20,Col3,Col24,Col7,Col8,Col39,Col6,Col23,Col22,Col17,Col18,Col9,Col21,Col10,Col14,Col15,Col16,Col13,Col11,Col27,Col28,Col19,Col29,Col30,Col31,Col26,Col52,Col45,Col46,Col49,Col59,Col60,Col61,Col62,Col63,Col64,Col12,Col5 where Col2<>'' and Col3 is Not Null and Col41 is Null and Col43 is Null and Col6<>'Realtime'",0),
{"","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""}));
...
IF(SpreadSheetKeys!B50="",
"","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""},
IFERROR(QUERY(IMPORTRANGE(SpreadSheetKeys!B50,"DataTable!A3:CL2002"),
"Select Col1,Col2,Col20,Col3,Col24,Col7,Col8,Col39,Col6,Col23,Col22,Col17,Col18,Col9,Col21,Col10,Col14,Col15,Col16,Col13,Col11,Col27,Col28,Col19,Col29,Col30,Col31,Col26,Col52,Col45,Col46,Col49,Col59,Col60,Col61,Col62,Col63,Col64,Col12,Col5 where Col2<>'' and Col3 is Not Null and Col41 is Null and Col43 is Null and Col6<>'Realtime'",0),
{"","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""}))
})
Wanting to reduce number of characters required to perform this function: stacked queries in an array formula. Would really prefer not to employ a third helper sheet to capture the entire range which will exceed 100,000 rows.
This exercise is an attempt to get around import range limits: the data range dimension is 90 cols x 2000 rows, which is also the import range.
The function above is the least visually complex of the lot, so I picked that one.
You can try with REDUCE, which combined with VSTACK, allows you to create a loop like you're mentioning:
=REDUCE(TOCOL(,1),SpreadSheetKeys!B3:B50,LAMBDA(prev,id,
IF(id="",
VSTACK(prev,{"","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""}),
VSTACK(prev,IFERROR(QUERY(IMPORTRANGE(id,"DataTable!A3:CL2002"),
"Select Col1,Col2,Col20,Col3,Col24,Col7,Col8,Col39,Col6,Col23,Col22,Col17,Col18,Col9,Col21,Col10,Col14,Col15,Col16,Col13,Col11,Col27,Col28,Col19,Col29,Col30,Col31,Col26,Col52,Col45,Col46,Col49,Col59,Col60,Col61,Col62,Col63,Col64,Col12,Col5 where Col2<>'' and Col3 is Not Null and Col41 is Null and Col43 is Null and Col6<>'Realtime'",0),
{"","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""})))))