How to handle empty cells when combining multiple IMPORTRANGE functions in Google Sheets

Other Questions Google Sheets
1

I’m trying to pull data from several different Google Sheets using IMPORTRANGE but running into issues when some reference cells are empty.

Here’s my current working formula that combines data from multiple sheets:

=QUERY(
{
IMPORTRANGE(DataList!B5,"Tasks!B3:O");
IMPORTRANGE(DataList!B6,"Tasks!B3:O");
IMPORTRANGE(DataList!B7,"Tasks!B3:O");
IMPORTRANGE(DataList!B8,"Tasks!B3:O");
IMPORTRANGE(DataList!B9,"Tasks!B3:O")
},
"select * where Col1 is not null")

The problem is that I have other people putting their sheet URLs in cells B5 through B9, but sometimes they leave cells blank. When that happens, the whole formula breaks down. I need a way to skip the IMPORTRANGE when a cell is empty but still process the ones that have URLs.

I tried using IF statements but got a parse error:

=QUERY(
{
IF(DataList!B5="","",IMPORTRANGE(DataList!B5,"Tasks!B3:O"));
IF(DataList!B6="","",IMPORTRANGE(DataList!B6,"Tasks!B3:O"))
},
"select * where Col1 is not null")

Is there a better way to handle this situation where some reference cells might be empty?

2

This is super common with dynamic IMPORTRANGE setups. I’ve had good luck with a combination approach - check for empty cells first, but handle it differently than what you tried.

Wrap your IMPORTRANGE in an IF statement that returns an empty array when the reference cell is blank:

=QUERY(
{
IF(ISBLANK(DataList!B5),{},IMPORTRANGE(DataList!B5,"Tasks!B3:O"));
IF(ISBLANK(DataList!B6),{},IMPORTRANGE(DataList!B6,"Tasks!B3:O"));
IF(ISBLANK(DataList!B7),{},IMPORTRANGE(DataList!B7,"Tasks!B3:O"))
},
"select * where Col1 is not null")

The trick is using {} instead of an empty string. This keeps the array structure QUERY needs while completely skipping those problematic IMPORTRANGE calls. Works great in collaborative sheets where people forget to add their references.

3

I’ve had good luck combining IFERROR with array handling. Your original IF statement breaks because Google Sheets can’t stack arrays properly when some return empty strings.

Try this:

=QUERY(
IFERROR({
IF(DataList!B5<>"",IMPORTRANGE(DataList!B5,"Tasks!B3:O"),{""});
IF(DataList!B6<>"",IMPORTRANGE(DataList!B6,"Tasks!B3:O"),{""});
IF(DataList!B7<>"",IMPORTRANGE(DataList!B7,"Tasks!B3:O"),{""});
IF(DataList!B8<>"",IMPORTRANGE(DataList!B8,"Tasks!B3:O"),{""});
IF(DataList!B9<>"",IMPORTRANGE(DataList!B9,"Tasks!B3:O"),{""})
}),
"No data available"),
"select * where Col1 is not null")

The IFERROR wrapper catches any leftover issues, and the semicolons stack your arrays vertically. This fixes empty reference cells and connection errors from bad URLs.