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.

Answer 1

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