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?


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"))

  • Hi, thanks for replying. It still returns formula parse error even if i write exactly what you suggested. Mar 8 at 14:29
  • 1
    Without seeing the sheet, I'm afraid guessing is as far as things can go. If you'd like to share a link, being sure to set the link's Share permission to "Anyone with the link" and "Editor," I'll be happy to take a look. It's the only we can see the IMPORTRANGE in action; even making a copy of a "View only" sheet will disconnect the other sheets without the proper permissions to reconnect them.
    – Erik Tyler
    Mar 8 at 15:51
  • 1
    I will add two questions here: 1.) What EXACTLY is typed into L2 and L3? -and- 2.) What is your international locale and language setting?
    – Erik Tyler
    Mar 8 at 15:52
  • Oh my gosh it was something as simple as the language setting changing the actual names of the functions, since I had the document locales set to my own country. I had no idea the functions would actually have translated versions depending on the locale! Mar 9 at 13:08
  • 1
    Well, I'm glad the additional questions led to a solution!
    – Erik Tyler
    Mar 9 at 13:23

So it turns out I'm a dummy and didn't know that the locale setting changes what language the functions are in. I had the documents set to my own country but was using the english function names!


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.