CODEWITHSUNDEEP
google-apps-scripterror-handling
maxhugen
maxhugen

Reputation: 1944

Google Sheets Script: Search For Any Cell With Error

In a spreadsheet I use almost 2M cells, which use a lot of functions, including ArrayFormulas etc spanning thousands of rows. Many columns do calcs based on previous columns that are also calc'd, so errors cascade.

Is there a way to do something like using createTextFinder(), but comparing a cell's contents to an array of ERROR.TYPEs?

Or is there no smarter way other than looping through all the cells? Although I can accomplish that, I'm sure there's a more efficient way to go about it.

Upvotes: 0

Views: 963

Answers (1)

maxhugen
maxhugen

Reputation: 1944

I found another post that I adapted, it finds errors in any column (not specific cell), but that's OK for my purposes, and doesn't take long to run:

function c_FindCellErrors() {
// Author: Max Hugen
// Date: 2021-01-09
// Purpose: Log all columns in spreadsheet with an error
// Source: Tedinoz
// Link: https://stackoverflow.com/a/60848623/190925

  const aErrors = ["#NULL!", "#DIV/0!", "#VALUE!", "#REF!", "#NAME?", "#NUM!", "#N/A", "#ERROR!"];
  const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  for ( i=0; i<sheets.length; i++) {
    var sheet = sheets[i];
    if ( !sheet.getLastRow() || !sheet.getLastColumn() ) {continue; } // in case it's a blank sheet
    var dSheet = sheet.getRange(1,1,sheet.getLastRow(),sheet.getLastColumn()).getValues();
    // loop though the columns
    for (var col=0; col<dSheet[0].length; col++){
      // create an array for the column
      var aCol = dSheet.map( function(getCol) { return getCol[col]; } );
      // loop through errors
      for (var errNum=0; errNum<aErrors.length; errNum++){
        // get the error
        var errVal = aErrors[errNum]
        // if the error exists in this column then response = true
        if (aCol.includes(errVal) == true){
          Logger.log('ERROR: Sheet: '+sheet.getName()+', Column: '+c_ColumnToLetter(col+1)+', Error: '+(errNum+1)+' - '+errVal);
  } } } }
  return;
}

Upvotes: 3

Related Questions