Google-sheets – Formula parse error on importrange query

google sheetsgoogle-sheets-queryimportrange

I'm trying to recreate what's happening in this video at 7:17
https://youtu.be/5S7laJS9meU?t=437

I want to merge two sheets and remove the empty rows.

=QUERY({IMPORTRANGE(L2 ,"sheet1!A2:F20");IMPORTRANGE(L3 ,"sheet2!A2:F20")},"select * where Col1 is not null")

I've tested with importing from each sheet individually and given access to the document for both of them, and they work fine. What am I doing wrong when trying to merge?

Best Answer

You've created a virtual array within the curly brackets but not "prepped" the formula for that. Try wrapping the entire thing in ArrayFormula(...):

=ArrayFormula(QUERY({IMPORTRANGE(L2 ,"sheet1!A2:F20");IMPORTRANGE(L3 ,"sheet2!A2:F20")},"select * where Col1 is not null"))