CODEWITHSUNDEEP
google-apps-script
Alex
Alex

Reputation: 65

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

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

Upvotes: 6

Views: 7391

Answers (4)

Tedinoz
Tedinoz

Reputation: 8044

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

Upvotes: 3

James VB
James VB

Reputation: 93

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

}

Upvotes: 1

Vanessa
Vanessa

Reputation: 1

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!

Upvotes: 0

tehhowch
tehhowch

Reputation: 9872

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.

Upvotes: 6

Related Questions