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

Solution 1:

Problem

Unable to check whether the cell has an error

Explanation

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.

Solution

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.

Notes

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.

References

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

Solution 2:

Try to move the 4 variables inside your function. Apps script does not support global variables. So the function does not recognize the var cell.

EDIT: Detect formula errors in Google Sheets using Script