Google Sheets Script Find Cell Errors In Range

Question

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:

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?

I'm not sure what you're trying to do with your script but this looks strange: var range = sheet.getRange('Portfolio_Sparklines');. You need to read the documentation — Dmitry Kostyuk
Hi Dimitry, Portfolio_Sparklines is a Named Range. I use them a lot, as they are referenced across multiple sheets. A big advantage of using them in scripts is they reflect any changes to sheet layout, eg adding a column, compared to entering a hard-coded range in script. — maxhugen
I not sure its the problem, but you never declare rv. What happends if you just log data[i][0]? — Elchanan shuky Shukrun
BTW, Logger is very buggy, use console.log() instead if you can — Elchanan shuky Shukrun
I fixed the missing declaration var, and tried your suggestions (console.log, data[i][0] ) but still no joy. — maxhugen

Rubén Rubén · Accepted Answer · 2021-08-27T15:31:58

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.

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.
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.

Google Sheets Script Find Cell Errors In Range

1 Answers