0

I am trying to write some code (as part of a larger bit of code) that opens a different Google Sheet , cycles through all tabs in that sheet, and removes duplicates. I have based this off Google's tutorial, and here is what I have so far:

function removeDuplicates() {
  var book = SpreadsheetApp.openById("[sheet id]").getSheets();
  for (var s = 0; s <= book.length; s++){
    var sheet = book[s];
    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.join() == newData[j].join()){
          duplicate = true;
         }
        }
        if(!duplicate){
          newData.push(row);
      }
    }
    sheet.clearContents();
    sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
  }
}

The problem I'm facing is that at the second loop (for(j in newData)), the first iteration returns j = "transpose."

Edit: This results in an error at if(row.join() == newData[j].join()):"Cannot find function join in object."

I have tested this with the code running in the sheet that is being edited (essentially copy-paste), and it suggests j = 0 at that point. I thought perhaps the newData array is limited to the sheet the code is running from?
I hope I have made myself clear, I'm very new at this, so I'm still finding my way about. Alternative ways of doing the job are welcome, too!

Update: I have reworked the code somewhat and got it to work:

function removeDuplicates() {
  var book = SpreadsheetApp.openById("[sheet id]").getSheets();
  for (var s = 0; s <= book.length-1; s++){
    var sheet = book[s];
    var data = sheet.getDataRange().getValues();
    var chkData = [];
    var newData = [];
    for(i in data){
      if(i != "transpose"){
        var row = data[i];
        var duplicate = false;
        if(chkData.indexOf(row.join())!=-1){
          duplicate = true;
        }
        if(!duplicate){
          chkData.push(row.join())
          newData.push(row);
        }
      }
    }
    sheet.getDataRange().clearContent();
    sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
  }
}

Rather than nested loops I went for the indexOf method. Still getting a "transpose" on the last iteration, so had to put in the if to skip over it.

SandA
  • 47
  • 6
  • You are victim to an enumerable property and extended built-in objects. You wanted indexed properties, and used the wrong iteration mechanic: https://stackoverflow.com/questions/500504/why-is-using-for-in-with-array-iteration-a-bad-idea https://stackoverflow.com/questions/14034180/why-is-extending-native-objects-a-bad-practice – tehhowch Mar 26 '18 at 12:13
  • Okay, I took that on board and I've made some adjustments, works now! – SandA Mar 27 '18 at 02:27

0 Answers0