Google sheets scripts--how to refer to a cell given a row?

Question

I'm new to google scripts/Javascript, and I'm having a problem with what I'm guessing is the way I refer to specific cells. I'm going off of code I found on google here (https://developers.google.com/apps-script/articles/removing_duplicates) except that instead of removing duplicate rows (which is what their script does) I instead want to color the background of the first cell orange. It doesn't work correctly at the moment, instead some duplicate cells are colored and some are not, and some non-duplicate cells are colored, some aren't. My guess was that since the array is 0-indexed but the sheet is 1-indexed, I needed to do sheet.getRange(i+1,1) but this gives me an error. Can anyone help push me in the right direction?

function checkDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = new Array();
  for(i in data){
    var row = data[i];
    var duplicate = false;
    for(j in newData){
      if(row[0] == newData[j][0]){
        duplicate = true;
        Logger.log(i,j)
        var dup = sheet.getRange(i,1);
        dup.setBackground('orange')        
      }
    }
    if(!duplicate){
      newData.push(row);
    }
  }
 }

Cooper Cooper · Accepted Answer · 2017-07-19T00:47:29

I played around with this and I find that it works better for me this way.

  function checkDuplicates() 
  {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = new Array();
  for(var i=0;i<data.length;i++){//changed loop
    var row = data[i];
    var duplicate = false;
    for(var j=0;j<newData.length;j++){//changed loop
      if(row[0] == newData[j][0]){
        duplicate = true;
        Logger.log('%s,%s',String(i),String(j));//got and error saying that it required string parameters
        var dup = sheet.getRange(i+1,1);//changed to i+1
        dup.setBackground('orange')        
      }
    }
    if(!duplicate){
      newData.push(row);
    }
  }
 }

Google sheets scripts--how to refer to a cell given a row?

1 Answers