Importrange from multiple google sheets, issue when any one sheet has no data to import

Question

I am importing data from two different Google sheets and merging them in another sheet. The formula which I used is as below .

=QUERY({
  IFERROR(QUERY(
    { IMPORTRANGE("" , "Sheet1!$A$1:$b") },
    "SELECT Col1,Col2 WHERE Col2='Total'",
    0
  )) ;
  IFERROR(QUERY(
    { IMPORTRANGE("" , "Sheet1!$a$1:$b") },
    "SELECT Col1,Col2 WHERE Col2='Yes'",
    0
  ))
})

In above formula, importrange is giving the result when both sheets have data to import, but if any one sheet has no data to import the data from another sheet is not getting imported. I had figured a solution at below link where we can handle this issue by using dummy column reference, this approach is fine when we have few sheets to import with few columns but when we are importing from many sheets and many column to import the dummy column reference gets too long. Is there any other way to solve this issue.

How big are the real databases getting merged? rows? columns? — MattKing
– MattKing, Commented Apr 17, 2021 at 14:07

MattKing · Accepted Answer · 2021-04-17 14:16:42Z

0

The easiest way to do this is to use an IFERROR(SEQUENCE(1,n)/0) as your output for when a query of an importrange fails instead of needing to list a bunch of blanks.

The solution for your simple example looks like this where n = 2:

=ARRAYFORMULA(QUERY({IFERROR(QUERY({IMPORTRANGE("1hea986JF3plR_tn7plNRgjWhiGqxOlR8s8yE-Ri6FfU" , "Sheet1!$A$1:$b")}, "SELECT Col1,Col2 WHERE Col2='Total'",0),IFERROR(SEQUENCE(1,2)/0));IFERROR(QUERY({IMPORTRANGE("1IbPpoLLfa-ukoz2WInlgNGSNMNBxBwSx4in52fZCEyE" , "Sheet1!$a$1:$b")},"SELECT Col1,Col2 WHERE Col2='Yes'",0),IFERROR(SEQUENCE(1,2)/0))},"where Col1 is not null"))

answered Apr 17, 2021 at 14:16

MattKing

7,80310 silver badges13 bronze badges

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

1 Comment

Edyphant Over a year ago

Great solution MattKing... it worked perfectly fine by replacing n with the number of columns required to import....Thanks

Collectives™ on Stack Overflow

Importrange from multiple google sheets, issue when any one sheet has no data to import

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related