Google Sheets中如何捕获IMPORTHTML错误并返回空行处理异常？

问题背景 #

我使用Google Sheets的IMPORTHTML函数抓取多个网页数据，用于列出FIFA世界杯各队的下一场比赛，示例公式如下：

=UNIQUE({
  IMPORTHTML("https://www.espn.com/soccer/team/fixtures/_/id/2869/league/FIFA.WORLD#","TABLE",1);
  IMPORTHTML("https://www.espn.com/soccer/team/fixtures/_/id/475/league/FIFA.WORLD#","TABLE",1)
})

实际需抓取5支球队的数据，但存在问题：部分球队被淘汰后，其赛程网页中的表格会被删除，导致单个IMPORTHTML调用返回错误，进而使整个UNIQUE函数失效。需要捕获每个IMPORTHTML的错误，返回空行而非让整体函数崩溃。

正常返回的数据样式：

DATE    MATCH           TIME    COMPETITION TV  Opponent
Thu, Dec 1  Canada  v   Morocco 10:00 AM    FIFA World Cup  FS1 Canada
Fri, Dec 2  Serbia  v   Switzerland 2:00 PM FIFA World Cup  FS1 Serbia

若第一个IMPORTHTML调用失败，期望返回样式：

DATE    MATCH           TIME    COMPETITION TV  Opponent

Fri, Dec 2  Serbia  v   Switzerland 2:00 PM FIFA World Cup  FS1 Serbia

解决方法 #

用IFERROR包裹每一个IMPORTHTML调用，当调用出错时返回与表格列数匹配的空值数组，确保单个抓取失败不会影响整体公式运行。

调整后的5支球队抓取公式示例（替换XXX为对应球队ID）：

=UNIQUE({
  IFERROR(IMPORTHTML("https://www.espn.com/soccer/team/fixtures/_/id/2869/league/FIFA.WORLD#","TABLE",1), {"","","","","",""});
  IFERROR(IMPORTHTML("https://www.espn.com/soccer/team/fixtures/_/id/475/league/FIFA.WORLD#","TABLE",1), {"","","","","",""});
  IFERROR(IMPORTHTML("https://www.espn.com/soccer/team/fixtures/_/id/XXX/league/FIFA.WORLD#","TABLE",1), {"","","","","",""});
  IFERROR(IMPORTHTML("https://www.espn.com/soccer/team/fixtures/_/id/XXX/league/FIFA.WORLD#","TABLE",1), {"","","","","",""});
  IFERROR(IMPORTHTML("https://www.espn.com/soccer/team/fixtures/_/id/XXX/league/FIFA.WORLD#","TABLE",1), {"","","","","",""});
})

关键说明 #

• IFERROR(公式, 错误返回值)：当IMPORTHTML执行出错时，返回指定的空值数组{"","","","","",""}，数组元素数量需与抓取表格的列数一致（示例中为6列，对应6个空字符串）。
• 空值数组会生成一行空数据，不会干扰UNIQUE函数对有效数据的合并去重。
• 若需要保留表头，确保每个IMPORTHTML抓取的表格包含表头，或手动在公式外添加固定表头。

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