嵌套SUBSTITUTE的QUERY公式拉取网页表格数据缺失如何解决

问题场景 #

使用两种Google Sheets公式拉取niftyinvest网站期权链表格数据：

• 方案1：仅移除表格内容中的*号，公式如下：

=QUERY(ARRAYFORMULA(IFERROR(SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",)*1,SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",))),"Select Col1, Col2, Col4, Col5, Col3, Col6, Col9, Col7, Col8, Col10, Col11 where "&TEXTJOIN(" and ", 1, "Col" &SEQUENCE(11) &" <> 0"))

• 方案2：同时移除*号并将-替换为0，公式如下：

=QUERY(ARRAYFORMULA(IFERROR(SUBSTITUTE(SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",""),"-","0")*1,SUBSTITUTE(SUBSTITUTE(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),"*",""),"-","0"))),"Select Col1, Col2, Col4, Col5, Col3, Col6, Col9, Col7, Col8, Col10, Col11 where "&TEXTJOIN(" and ", 1, "Col" &SEQUENCE(11) &" <> 0"))

实际运行时第二种方案返回数据量少于第一种，对比Col6列可验证数据缺失，测试参数为M5=BAJFINANCE、N5=30June2022，需调整第二种公式获取完整数据。

故障原因 #

第二种公式数据缺失核心原因：全局替换所有-为0的逻辑有误。期权链表格中，行权价、日期类文本列本身可能包含合法-字符，全局替换会将这类有效文本转为无意义的0，后续*1转数值时触发报错，整行数据会被QUERY的过滤条件直接剔除。此外原公式重复调用两次IMPORTHTML，既会拖慢加载速度，也会提升请求失败概率。

修复后公式 #

优化版（支持LET函数，加载更快） #

=LET(
  raw_data, IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1),
  clean_data, ARRAYFORMULA(IFERROR(
    SUBSTITUTE(REGEXREPLACE(TO_TEXT(raw_data), "\*", ""), "^-$", "0")*1,
    SUBSTITUTE(REGEXREPLACE(TO_TEXT(raw_data), "\*", ""), "^-$", "0")
  )),
  QUERY(clean_data, "Select Col1, Col2, Col4, Col5, Col3, Col6, Col9, Col7, Col8, Col10, Col11 where "&TEXTJOIN(" and ", 1, "Col" &SEQUENCE(11) &" <> 0"))
)

兼容版（不支持LET函数的旧版表格可用） #

=QUERY(ARRAYFORMULA(IFERROR(
  SUBSTITUTE(REGEXREPLACE(TO_TEXT(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1)),"\*",""),"^-$","0")*1,
  SUBSTITUTE(REGEXREPLACE(TO_TEXT(IMPORTHTML("https://niftyinvest.com/option-chain/"&M5&"?expiry="&$N$5,"table",1)),"\*",""),"^-$","0")
)),"Select Col1, Col2, Col4, Col5, Col3, Col6, Col9, Col7, Col8, Col10, Col11 where "&TEXTJOIN(" and ", 1, "Col" &SEQUENCE(11) &" <> 0"))

调整说明 #

• 用正则^-$精准匹配**单元格内容仅为单个-**的场景，仅替换代表空值的短横线为0，不会误改文本内容中的合法-字符
• 先通过TO_TEXT将爬取的原始内容统一转为文本格式，避免不同数据类型导致的匹配失效
• 优化版仅调用一次IMPORTHTML，减少重复请求，加载速度更快、稳定性更高

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