Google Sheets脚本如何在指定Range范围内检测单元格#N/A错误

Google Sheets中公式返回的错误值（比如#N/A）通过getValues()读取时不会返回字符串形式的#N/A，而是JavaScript的Error类型对象，无法直接用字符串匹配方法检测；Sparkline返回的错误存在特殊渲染逻辑，部分场景下getDisplayValues()也无法读取到显示的错误文本。
另外原代码中两次写入操作之间没有强制刷新，可能会被服务端合并为一次操作，达不到触发公式刷新的效果。

推荐使用getFormulaErrors()方法检测错误，该方法会直接返回单元格的公式错误类型，检测结果最准确，不受显示格式影响。

function portfolioRefreshSparklines(){
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Portfolio');
  const tempText = 'Refreshing...';
  const targetRange = sheet.getRange('Portfolio_Sparklines');
  const rowStart = targetRange.getRow();
  const col = targetRange.getColumn();

  // 一次性读取所有错误状态、原公式，减少接口调用提升效率
  const formulaErrors = targetRange.getFormulaErrors();
  const originalFormulas = targetRange.getFormulas();

  for (let i = 0; i < formulaErrors.length; i++) {
    const cellError = formulaErrors[i][0];
    // 精准匹配#N/A错误类型
    if (cellError && cellError.getType() === SpreadsheetApp.ErrorType.NA) {
      const currentCell = sheet.getRange(rowStart + i, col);
      const originalFormula = originalFormulas[i][0];
      Logger.log('检测到错误单元格：' + currentCell.getA1Notation());
      // 设置临时文本后强制刷新，确保修改被服务端接收
      currentCell.setValue(tempText);
      SpreadsheetApp.flush();
      // 写回原公式触发Sparkline刷新
      currentCell.setFormula(originalFormula);
    }
  }
  SpreadsheetApp.flush();
}

如果运行环境不支持getFormulaErrors()，可以改用读取显示文本的方式做匹配，兼容性更强，但如果单元格自定义了错误显示格式可能会失效：

function portfolioRefreshSparklines(){
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Portfolio');
  const tempText = 'Refreshing...';
  const targetRange = sheet.getRange('Portfolio_Sparklines');
  const rowStart = targetRange.getRow();
  const col = targetRange.getColumn();

  const displayValues = targetRange.getDisplayValues();
  const originalFormulas = targetRange.getFormulas();

  for (let i = 0; i < displayValues.length; i++) {
    if (displayValues[i][0] === '#N/A') {
      const currentCell = sheet.getRange(rowStart + i, col);
      const originalFormula = originalFormulas[i][0];
      currentCell.setValue(tempText);
      SpreadsheetApp.flush();
      currentCell.setFormula(originalFormula);
    }
  }
  SpreadsheetApp.flush();
}

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