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](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getrangerow,-column) — Dmitry Kostyuk, Aug 27 '21 at 11:49
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, Aug 27 '21 at 12:23
I not sure its the problem, but you never declare `rv`. What happends if you just log `data[i][0]`? — Elchanan shuky Shukrun, Aug 27 '21 at 12:51
BTW, Logger is very buggy, use `console.log()` instead if you can — Elchanan shuky Shukrun, Aug 27 '21 at 12:54
I fixed the missing declaration `var`, and tried your suggestions (console.log, data[i][0] ) but still no joy. — maxhugen, Aug 27 '21 at 13:17
How does your `data` array look like? Can you confirm you are not receiving any errors along with your script? — ale13, Aug 27 '21 at 13:54
Shouldn't you be using [getNamedRanges()](https://developers.google.com/apps-script/reference/spreadsheet/sheet#getnamedranges) for this purpose? — Dmitry Kostyuk, Aug 27 '21 at 14:50
Can you please share what `var data = range.getValues();` returns — Dmitry Kostyuk, Aug 27 '21 at 14:52
Does this answer your question? [Detect formula errors in Google Sheets using Script](https://stackoverflow.com/questions/49161874/detect-formula-errors-in-google-sheets-using-script) — Dan Dascalescu, Jun 10 '23 at 12:49

Rubén · Accepted Answer · 2021-08-27T15:37:10.303

1

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.

edited Aug 27 '21 at 15:37

answered Aug 27 '21 at 15:31

Rubén

34,714
9
70
166

Thanks. (1) I think I misunderstood the docs in GoogleFinance() - thought the function couldn't be used in script, didn't understand that the results - including errors - couldn't be returned either. (2) Point taken re flush(). (3) Haven't been using GAS for long, still trying to get to grips with using arrays. I need to study this further. – maxhugen Aug 27 '21 at 18:02
@maxhugen did you ask [pretty much the same question back in Jan 2021](https://stackoverflow.com/questions/65632638/google-sheets-script-search-for-any-cell-with-error)? – Dan Dascalescu Jun 10 '23 at 12:13

Google Sheets Script Find Cell Errors In Range

1 Answers1