如何检查工作表单元格是否包含错误? [英] How to check if a sheet cells includes errors or not?

问题描述

我正在使用此脚本函数来检查工作表中的单元格函数是否有任何错误.

I am using this script function to check if my cell functions in sheet has any errors or not.

这是代码，但似乎没有用.当我在单元格中出现错误时，它会一直说没有错误

Here is the code but it does not seems to be working. It keeps on saying no error when i have an error in a cell

var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sourcename = "Sheet1";
  var source = ss.getSheetByName(sourcename);

  var cell = source.getRange("A1:AG30");

function isError2(cell) { 
  const errorValues = ["#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A","#ERROR!"];
  if (errorValues.includes(cell) != true) {
  Logger.log("no error");
  } else{
    Logger.log("some error");
  }
  
}

---

function isError2() {  
var ss = SpreadsheetApp.getActiveSpreadsheet();
      var sourcename = "Sheet1";
      var source = ss.getSheetByName(sourcename);

      var cell = source.getRange("A1:AG30");
      const errorValues = ["#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A","#ERROR!"];
      if (errorValues.includes(cell) != true) {
      Logger.log("no error");
      } else{
        Logger.log("some error");
      }

    }

---

更新了方法，但仍然无法获得所需的输出

---

Updated the approach but still having no luck with the desired output

var mysheet = SpreadsheetApp.getActive().getSheetByName('Sheet1');
 var sheet1 = SpreadsheetApp.setActiveSheet(mysheet);

function findErrors(sheet) {
   const errorValues = ["#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A","#ERROR!"];
    
  var singleSheetArray = [];

  var name = sheet1.getName();
   
  // how many cells in the sheet currently
  var maxRows = sheet1.getMaxRows();
  var maxCols = sheet1.getMaxColumns();
  
  var totalCells = maxRows * maxCols;
  
  // how many cells have data in them
  var r = sheet1.getLastRow();
  var c = sheet1.getLastColumn();
  var data_counter = r * c;
  
  if (data_counter !== 0) {
  
    var dataRange = sheet1.getRange(1,1,r,c);
    var dataValues = dataRange.getValues();

    dataValues.forEach(function(row) {
      row.forEach(function(cell) {
        if ((errorValues.indexOf(cell) === -1) )  {
           SpreadsheetApp.getUi().alert("no errors in "+cell);          
          data_counter --;
        } 
          
      });
    });  
  }
}

推荐答案

问题

无法检查单元格是否有错误

Unable to check whether the cell has an error

说明

您面临的问题是简单的类型不匹配. getRange()方法返回Range的实例，同时尝试将其与errorValues数组的成员进行比较，该数组由字符串组成.因此，errorValues.includes(cell)将始终为false，因此将执行条件语句的第一块.

The issue you are facing is a simple type mismatch. getRange() method returns an instance of Range, while you try to compare it to a member of a errorValues array, which consists of strings. Therefore, errorValues.includes(cell) will always be false, hence first block of the conditional statement executing.

解决方案

在范围上使用getValues()，它将为您返回一个二维值数组.如果您只对一行感兴趣(可能是)，请提取该行并使用some(或every)方法在单元格上循环，进行相同的比较.

Use getValues() on the range, it will return you a 2-dimensional array of values. If you are only interested in one row (which you probably are), extract it and loop over the cells with some (or every) method, doing the same comparison.

注释

1. 在自定义函数和GAS中通常使用全局变量.您可以使用它们，GAS环境是一个JavaScript运行时，带有一个方便层，可以简化Google API的使用，几乎所有在JS中有效的东西都在这里有效.话虽这么说，将全局变量当作不存在一样对待-除非您确切地知道自己在做什么.

1. On using global variables in custom functions and in GAS in general. You can use them, GAS environment is a JavaScript runtime with a convenience layer that simplifies working with Google APIs, nearly everything that's valid in JS is valid here. That being said, do treat global variables as if they don't exist - unless you know exactly what you are doing.

参考

1. getRange方法参考
2. getValues方法参考
3. 自定义功能指南
4. every方法引用在MDN上(请参见some)

1. getRange method reference
2. getValues method reference
3. Custom functions guide
4. every method reference on MDN (see some there)

这篇关于如何检查工作表单元格是否包含错误?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！