6

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

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

Improve this question
2
  • 1
    Have you tried that? Does it work? Does it not work? Commented Mar 7, 2018 at 22:16
  • Have I tried what? ISERROR? It works inside the spreadsheet, but I need to call it in a script, so that I can then do script-y things with it. Or, if you mean have I tried doing if/else things, yeah I have. That technically works, but it seems needlessly complicated to make a whole string of those for each possible error value, if there's an actual way to do it in the code Commented Mar 7, 2018 at 22:31

4 Answers 4

Reset to default
6

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.");
      }
    }
  }
}

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);
}

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);
}

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

For my case, the helper function complicated it a little much. However, being pretty inexperienced with Javascript, it had never occurred to me to use an array for a variable. I set the variable errorValues = [ insert errors codes here], then after a little more Googling discovered indexOf. My final code looks like this: if (errorValues.indexOf(data) >=0) {return "this is an error"}. Thanks!
Yep, indexOf works nicely (better, really) than the for loop. The idea behind a helper function is that you can encapsulate some set of logic into a single place, and avoid needing to repeat that logic elsewhere. For example, if you needed to validate some input, rather than write a complex set of checks after every input collection, you would use a single function to check the input, and call that function with the user input whenever needed. This keeps the logic of what the input is needed for separate from the logic of checking for input errors.
3

Update: 25 March 2020

@tehhowch remarked "If/when Google Apps Script is upgraded with Array#includes, that would be a better option than Array#indexOf".

Array.includes does now run in Apps Script and, as anticipated provides a far more simple approach when compared to indexOf.

This example varies from the previous answers by using a specific range to show that looping through each cell is not required. In fact, this answer will apply to any range length.

The two key aspects of the answer are:

  • map: to create an array for each column
  • includes: used in an IF statement to test for a true or false value.
function findErrors() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); 
  const values = sheet
    .getRange(1, 1, sheet.getLastRow(),sheet.getLastColumn())
    .getValues();
  const errorValues = ['#ERROR!', '#NULL!', '#DIV/0!', '#VALUE!', '#REF!', '#NAME?', '#NUM!', '#N/A'];

  // Loop though the columns
  for (let c = 0; c < values[0].length; c++) {
    let errorCount = 0;

    // Create an array for the column
    const columns = values.map(row => row[c]);
  
    // Loop through errors
    for (error of errorValues)
      if (columns.includes(error)) {
        console.warn(`Column #${c} has error ${error} at least once`);
        errorCount++;
      }

    if (errorCount === 0)
      console.log(`Column ${c} has no error`);
  }  
}
Improve this answer

7 Comments

Hi @Tedinoz first of all thank you for the above function. it is very helpful. In my use case as well I am trying to find if a sheet includes any formula errors or not. With the above approach suggest by you, it is checking each column. I have values up to AH columns. I would like to check everything all in once (opposed to column by column) if any error exist, if NO, then trigger another function, if yes then do nothing. I would really appreciate if you could please help and show what changes I need to make in your code in order to accomplish this?
Hi @Tedinoz I have tested your code and it looks like it is always returning false condition where it shows NO ERROR
@kuml Please provide your data
Data screenshot here and getting this in the log Log screenshot . Basically I need help in scanning whole sheet at once instead of checking each column. Everything you have shown is great and I am looking for some guidance on how to modify the code so whole sheet is checked at once. If any error then do nothing and and if no error then trigger a function. i would appreciate your help.
@kuml I duplicated your data and used the script in the answer. I'm getting "column#:0, error#:6-#N/A- ERROR EXISTS" and "column#:1, error#:6-#N/A- ERROR EXISTS". The only thing about your data that I can't see is: is your data sheet actually called "Sheet6"?
|
1

Shorter yet, use a nested forEach() on the [vals] array, then check to see if the cell value matches a value of the [errorValues] array with indexOf. This was faster than a for loop...

function foo() {
  const errorValues = ["#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A","#ERROR!"];  
  const vals = SpreadsheetApp.getActive().getSheets()[0].getDataRange().getValues();
  
  vals.forEach((val,row) => { val.forEach((item, col) => {
    (errorValues.indexOf(item) !== -1) ? Logger.log("Array element (" + row + ", " + col + ") is an error value.") : false ;
    });
 });

}
Improve this answer

Comments

0

I had a similar question and resolved using getDisplayValue() instead of getValue()

Try something like:

function checkCells(inputRange) {
  var inputRangeCells = SpreadsheetApp.getActiveSheet().getRange(inputRange);
  var cellValue;
  for(var i=0;  i < inputRangeCells.length; i++) {
    cellValue = inputRangeCells[i].getDisplayValue();
    if (cellValue=error1.....) { ... }
  }
}

Display value should give you what's displayed to the user rather than #ERROR!

Improve this answer

1 Comment

That's not always true, getDisplayValue() returns #ERROR! as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.