Get 'cell reference out of range ' in Google Apps Script

Question

So I've created a program that is supposed to mark one of multiple columns when the if conditions are met, so we can categorize our marketing sectors.

If you look at the last line of code you will see sheet (which encapsulates everything) getRange (which defines the range that the next part,getCell should be inside of ) and then you will see getCell.

I added +1 to the get cell Variable because it is not considered an array and therefore doesn't start with zero, which means it an sum of 4 would start in fourth row (as opposed to the 5th row for arrays).The problem is that I get a "Cell reference out of range and google script editor will highlight the aforementioned row.

If the get range is within the sheet, and the getCell is within the getRange I dont know how this could happen.

function myFunction() {
  var app = SpreadsheetApp;
  var sheet = app.getActiveSpreadsheet().getActiveSheet(); 
  var lr= sheet.getLastRow();

  var dataRangeContact = sheet.getRange("B4:B"+lr);
  var dataContact = dataRangeContact.getValues();
  var dataRangeComp= sheet.getRange("M4:M"+lr);
  var dataComp=dataRangeComp.getValues();
  var IndigoColumn= sheet.getRange("L4:L"+lr);

  var validIndigoColumn =12;
  var validLabColumn=11;
  var validDesColumn=10;
  var validPocColumn=9;
  var validArtColumn=8;
  for(var i=3; i<=lr; i++) {         

    var lr=sheet.getLastRow();
    var rowTwo=dataContact[i].toString();
    var rowThirteen=dataComp[i].toString();


    if (rowThirteen=="University Lab") { 
      if (rowTwo.match(/(process engineer|analytical   chemist|scientist|sr.scientist|research scientist|test Engineer|Medical Devices professional|scientific)/i)) {
        sheet.getRange(4,12,lr).getCell(i+1,validIndigoColumn).setValue("Y");
    }
  }
}}

Cooper Cooper · Accepted Answer · 2018-04-12T03:41:33

I think this is the same function rewritten a little differently. It will most likely run faster. It only has one range so it may be easier to avoid range errors. Keep in mind that rows and columns start at 1 and array indices start at 0. I sometimes do this sort of thing to keep them straight.

Anyway here's my shortened version of the function.

function myFunction(){
  var ss=SpreadsheetApp.getActive();
  var sh=ss.getActiveSheet(); 
  var rg=sh.getDataRange();
  var vA=rg.getValues();
  for(var i=3;i<vA.length;i++){
    if(vA[i][12]=='University Lab' && vA[i][1].toString().match(/(process engineer|analytical   chemist|scientist|sr.scientist|research scientist|test Engineer|Medical Devices professional|scientific)/i)){
        vA[i][11]='Y';
      }
  }
  rg.setValues(vA);//rewrites the data into the range including all changes all at one time
}

I have no way to test it. I hope this helps. If I misunderstood your question let me know. I can always delete my answer.

Get 'cell reference out of range ' in Google Apps Script

1 Answers