CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
maxhugen
maxhugen

Reputation: 1944

Google Sheets Script Find Cell Errors In Range

I'm trying to find cells that have an error, using a Range in script. The Range consists of a single column AB of cells using Sparkline() getting data from GoogleFinance(), which quite often return Error Google Finance internal error., and display #N/A. Errors are showing:

enter image description here

However, the function is not returning anything when I try to getValues:

function portfolioRefreshSparklines(){
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Portfolio');
  const msg = 'Refreshing...';
  const err = '#N/A';

  var range = sheet.getRange('Portfolio_Sparklines');
  var col = range.getColumn();
  var rowStart = range.getRow();
  Logger.log('col: ' + col + '; rowRange: ' + rowStart);

  var data = range.getValues();
  for ( i=0; i<data.length; i++ ) {


    // this is NOT returning the `#N/A` error (`Google Finance internal error.`)
    var rv = data[i][0];
    Logger.log('i: ' + i + '  rv: '+ rv)  

  
    // If an error is found, set the cell's formula to the msg, then back to the original formula.
    // Think I have to reference the cell directly to do the setFormula() switch, not within the data array?
    if ( rv.includes(err) ){
      var row = rowStart + i;
      var cell = sheet.getRange(row, col);
      Logger.log('cell: ' + cell.getA1Notation() );
      rv = cell.getFormula();
      cell.setFormula(msg);
      cell.setFormula(rv);
    }
  }
  SpreadsheetApp.flush();
}

I've searched through the Range Class, tried to use function getDisplayValues(), but haven't found anything that returns a cell error.

Any suggestions pls?

Upvotes: -1

Views: 269

Answers (1)

Wicket
Wicket

Reputation: 38357

From the question

However, the function is not returning anything when I try to getValues:

Google Finance is blocked in Google Apps Script. See Reading the values of cells that summarize Google Finance data results via Apps Script

P.S.

  1. It doesn't make sense to include SpreadsheetApp.flush() as the last function statement. It should be used when you need to force that the changes made are applied before the function ends because you will be reading something that was changed by the script to be used in later part of it.

  2. The Best Practices discourages the use of Google Apps Script classes (in this case var cell = sheet.getRange(row, col);) in loops because they are slow.

Upvotes: 1

Related Questions