I am creating a table to display data from a combination of several Spreadsheet files by using Multiple Importrange.
= QUERY ({Importrange (G3, "Sheet1! A2: D"); Importrange (G4, "Sheet1! A2: D"); Importrange (G5, "Sheet1! A2: D")}, "Select Col1, Col2, Col3, Col4 Where Col3 Is Not Null And Col4 Is Not Null ")
The formula above has gone well.
My file:
https://docs.google.com/spreadsheets/d/1eL06DChMSkLuOOj9YNK6KUsVUIqbjcYlKS8pZHLmt_0/edit#gid=0
I insert some Spreadsheet files into the "List Link" table.
The link in the table has 3 links.
What I want to ask is how do I overcome the multiple importrange error when the link in the table does not yet exist?
The formula that I have changed is like this:
= IFERROR (QUERY ({Importrange (G3, "Sheet1! A2: D"); Importrange (G4, "Sheet1! A2: D"); Importrange (G5, "Sheet1! A2: D"); Importrange (G6, " Sheet1! A2: D "); Importrange (G7," Sheet1! A2: D ")}," Select Col1, Col2, Col3, Col4 Where Col3 Is Not Null And Col4 Is Not Null "))
Best Answer
Wrap each individual
IMPORTRANGE
call inIFERROR
with a null array as the default length of a filled row if there is an error, like this:=QUERY({ IFERROR(Importrange(G3,"Sheet1!A2:D"),{"","","",""}); IFERROR(Importrange(G4,"Sheet1!A2:D"),{"","","",""}); IFERROR(Importrange(G5,"Sheet1!A2:D"),{"","","",""}); IFERROR(Importrange(G6,"Sheet1!A2:D"),{"","","",""}); IFERROR(Importrange(G7,"Sheet1!A2:D"),{"","","",""}) },"Select Col1, Col2, Col3, Col4 Where Col3 Is Not Null And Col4 Is Not Null")