Cell reference out of range on Google Spreedsheet

0
votes

I am trying to do a script to iterate over a column in a sheet, compare the cell value with another cells and changing the background color when they are equal.

I am new both to JS and google scripting, so I am having problems with a for loop, as I always get a "Cell reference out of range" error. Here is my code so far:

function onOpen()
{  
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Exibicao");

  var range = sheet.getDataRange();
  var rows = range.getNumRows();

  for(var i = 0; i <= rows; i++)
  {
    var cell = range.getCell(i, 13);

    var option1 = cell.offset(0, -3);
    var option2 = cell.offset(0, -2);
    var option3 = cell.offset(0, -1);

    if(option1.getValue() == cell.getValue()){
      option1.setBackground("#b6d7a8");
    }else if(option2.getValue() == cell.getValue()){
      option2.setBackground("#b6d7a8");
    }else if(option3.getValue() == cell.getValue()){
      option3.setBackground("#b6d7a8");
    }
  }
}
2
google-apps-scriptgoogle-sheets

2 Answers

1
votes

Which line is the error thrown on? Logging the cell location after each line may give you a better idea of where it's going out of bounds. 

var cell = range.getCell(i, 13);
Logger.log( cell.getA1Notation() );

var option1 = cell.offset(0, -3);
Logger.log( option1.getA1Notation() );

On second look, think you'll need some thing like this...

function onOpen()
{  

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Exibicao");

  var range = sheet.getDataRange();
  var rows = range.getNumRows();

  // Get values all at once
  var values = range.getValues();
  var row, len, bgs = [], rowBg = [];

  for(row = 0, len = values.length; row < len; row++) {
    var cell = values[row][12];
    var option1 = values[row][9];
    var option2 = values[row][10];
    var option3 = values[row][11];

    rowBg = ['white', 'white', 'white'];
    if( option1 == cell ){
      rowBg[0] = "#b6d7a8";
    }else if( option2 == cell ){
      rowBg[1] = "#b6d7a8";
    }else if( option3 == cell ){
      rowBg[2] = "#b6d7a8";
    }

    bgs.push(rowBg);
  }
  //Logger.log(bgs)
  // Set background colors all at once, start at the top left
  sheet.getRange(1, 10).offset(0, 0, bgs.length, bgs[0].length).setBackgrounds(bgs);
}
1
votes

I could be wrong, but I would bet that the issue is in the declaration of your loop:

for(var i = 0; i <= rows; i++)

JavaScript arrays are 0-based, which means that an array with a length of 2 has its first element at arrayName[0] and its last element at arrayName[length - 1].

Your loop starts with i = 0, and ends with i = range.getNumRows(). This means that the array starts at the first element and ends with trying to get one more row than is in the array. It ends with arrayName[length] instead of arrayName[length - 1].

Try this:

for(var i = 0; i < rows; i++)

And if that doesn't work, try this:

for(var i = 1; i <= rows; i++)

Both of these access getNumRows() elements, not getNumRows() + 1 elements.

Good luck!