使用脚本检测Google表格中的公式错误 [英] Detect formula errors in Google Sheets using Script

问题描述

我的最终目标是此处，但是因为我已经没有得到答复，我开始从头开始学习(也许无论如何都是最好的).基本上，我想要一个脚本来识别错误并修复错误

My ultimate goal is here, but because I've gotten no replies, I'm starting to learn things from scratch (probably for the best anyway). Basically, I want a script that will identify errors and fix them

好吧，其中的第一部分是能够识别错误.是否可以使用Google脚本识别单元格中是否有错误，并返回特定消息作为结果?还是我只需要做一个if/else，说如果单元格值为'#N/A'，请执行此操作"，再加上如果单元格值为'#ERROR'，请执行此操作"，然后继续进行各种错误?基本上我想要ISERROR()，但是在脚本中

Well, the first part of that is being able to ID the errors. Is there a way using Google Script to identify if a cell has an error in it, and return a particular message as a result? Or do I just have to do an if/else that says "if the cell value is '#N/A', do this", plus "if the cell value is '#ERROR', do this", continuing for various errors?. Basically I want ISERROR(), but in the script

推荐答案

使用帮助器函数来消除烦恼:

Use a helper function to abstract away the nastiness:

function isError_(cell) {
  // Cell is a value, e.g. came from `range.getValue()` or is an element of an array from `range.getValues()`
  const errorValues = ["#N/A", "#REF", .... ];
  for (var i = 0; i < errorValues.length; ++i)
    if (cell == errorValues[i])
      return true;

  return false;
}

function foo() {
  const vals = SpreadsheetApp.getActive().getSheets()[0].getDataRange().getValues();
  for (var row = 0; row < vals.length; ++row) {
    for (var col = 0; col < vals[0].length; ++col) {
      if (isError_(vals[row][col])) {
        Logger.log("Array element (" + row + ", " + col + ") is an error value.");
      }
    }
  }
}

使用 Array#indexOf 在辅助功能中:

Using Array#indexOf in the helper function:

function isError_(cell) {
  // Cell is a value, e.g. came from `range.getValue()` or is an element of an array from `range.getValues()`
  // Note: indexOf uses strict equality when comparing cell to elements of errorValues, so make sure everything's a primitive...
  const errorValues = ["#N/A", "#REF", .... ];
  return (errorValues.indexOf(cell) !== -1);
}

如果/当Google Apps脚本使用

If/when Google Apps Script is upgraded with Array#includes, that would be a better option than Array#indexOf:

function isError_(cell) {
  // cell is a value, e.g. came from `range.getValue()` or is an element of an array from `range.getValues()`
  const errorValues = ["#N/A", "#REF", .... ];
  return errorValues.includes(cell);
}

现在可以使用v8运行时了，可以对上述代码段进行许多其他更改(箭头功能等)，但请注意，不需要以这种方式进行更改

Now that the v8 runtime is available, there are a number of other changes one could make to the above code snippets (arrow functions, etc) but note that changing things in this manner is not required.

这篇关于使用脚本检测Google表格中的公式错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！