google-sheetsgoogle-sheets-formulaiterated-function

Reduce number of characters in stacked Google spreadsheet query functions


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.


Solution

  • 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),
    {"","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","","",""})))))