Google Sheets QUERY+IMPORTRANGE数组字面量缺失值报错解决方法

一、报错快速修复方法 #

报错核心原因是某一个IMPORTRANGE返回了空数组（对应目标表格无数据）或未完成授权，导致合并数组时行/列结构不匹配。你已确认列数对齐，只需给每个IMPORTRANGE套上IFERROR，让空/报错的请求返回对应列数的空数组即可：

修改后的公式：

=QUERY({
  IFERROR(IMPORTRANGE(Trackers!C27,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C28,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C29,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C30,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C31,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C32,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C33,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C34,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C35,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C37,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C38,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C39,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C40,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C41,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C42,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26));
  IFERROR(IMPORTRANGE(Trackers!C43,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26))
}, "select Col1, Col2, Col3, Col4, Col5, Col7, Col9, Col10, Col11, Col14 where Col9 = 'In Storage : MP3' AND (Col5 = 'Awaiting Samples')")

说明：ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26)生成1000行26列的空数组（D到AC共26列），确保每个IMPORTRANGE无论是否有数据，返回的数组列数一致，避免合并报错。

二、更简便的替代公式（批量处理IMPORTRANGE） #

不用手动重复写16个IMPORTRANGE，用REDUCE函数批量合并指定单元格的链接：

=QUERY(
  REDUCE({}, {Trackers!C27:C35, Trackers!C37:C43}, LAMBDA(acc, url, 
    IF(url="", acc, {acc; IFERROR(IMPORTRANGE(url,"Tracker!D4:AC"), ARRAY_CONSTRAIN(SPLIT(REPT(",",25),","),1000,26))}))
  ),
  "select Col1, Col2, Col3, Col4, Col5, Col7, Col9, Col10, Col11, Col14 where Col9 = 'In Storage : MP3' AND (Col5 = 'Awaiting Samples')"
)

说明：

• {Trackers!C27:C35, Trackers!C37:C43}将你需要的链接单元格合并为一个数组（自动跳过C36）
• REDUCE遍历每个链接，逐步合并到结果数组中，空链接直接跳过
• 同样用IFERROR处理空/报错情况，保证数组结构一致

额外注意事项 #

• 确保所有IMPORTRANGE的目标表格都已完成授权（首次运行公式时会弹出授权提示，逐个确认即可）
• 确认目标表格的D4:AC区域列数确实为26列（D到AC），避免因列数变化导致新的结构错误

内容的提问来源于stack exchange，提问作者Michael Mullins