ArrayFormula + multiple IMPORTRANGE + QUERY

Question

I want to import a few spreadsheets into one with some criteria, unfortunately if one of the spreadsheets doesn't return any data, the ARRAYFORMULA is asking for ARRAY_LITERAL instead of empty result.

What do I need to use in the IFERROR function so that ARRAYFORMULA appends nothing and is happy to continue?

This is what I have now:

={
   QUERY(IMPORTRANGE("<spreadsheet>", "Range!B2:J"), "SELECT Col1, Col2, Col3, Col5, Col9 WHERE Col1 <> '' AND Col5 >= 18 ");
   IFERROR(QUERY(IMPORTRANGE("<spreadsheet2>", "Range!B2:K"), "SELECT Col1, Col3, Col2, Col4, Col6, Col10 WHERE Col10 <> '' AND Col6 >= 18"), "")
}

I wrapped the second one in IFERROR but the empty string is not a valid ARRAY_LITERAL, so what can I do instead of it?

Full error:

In ARRAY_LITERAL, an Array Literal was missing values for one or more rows.

I realised I could give it an empty row with the same number of columns like I1:N2 but you never know if you might need to use these columns and then the query would need to change.

Max Makhrov · Accepted Answer · 2018-02-09 20:53:28Z

2

I suggest using an empty array:

=iferror(query(..., ...), {"", "", "", ..., ""} )

Note:

you select 6 columns, thus repeat "" six times.

Edit

by @Moseleyi.

Makes sense, but of course, it still produces empty rows, and if I wrap my ARRAYFORMULA in SORT, the empty rows will be shown first, but when I use empty cells they won't, probably because they're actually NULL. But I replaced empty strings with IFERROR(1/0) and it worked.

edited Feb 9, 2018 at 20:53

answered Feb 9, 2018 at 15:30

Max Makhrov

18.9k6 gold badges65 silver badges88 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Moseleyi Over a year ago

Makes sense, but of course it still produces empty rows, and if I wrap my ARRAYFORMULA in SORT, the empty rows will be shown first, but when I use empty cells they won't, probably because they're actually NULL. But I replaced empty strings with IFERROR(1/0) and it worked, so thank you!

Max Makhrov Over a year ago

Hi, @Moseleyi, great idea, thank you! I've edited the answer.

Collectives™ on Stack Overflow

ArrayFormula + multiple IMPORTRANGE + QUERY

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related