2

the below code is awesome and remove the duplicates but there is one thing i want to change for example if i have column A and it contain duplicate values and column B contains unique values in this case the function don't remove the duplicates from Column A because some how it Join Column A & B together then it removes the duplicate.

what i need is to remove the duplicates based on Column A only whatever Column B is Unique Value or not

here is a sample sheet with dummy data https://docs.google.com/spreadsheets/d/13ViFiwoA_29oo-nz2LUK3CD7DiRqDRTW1blJRE6XHm4/edit?usp=sharing

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = [];
  for (var i in data) {
    var row = data[i];
    var duplicate = false;
    for (var 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);
}
Improve this question

2 Answers 2

Reset to default
7

  • You want to remove the duplicated rows.

    • As a sample, you want to achieve as follows.

    • From

      aa  123
bb  12345
cc  1235
dd  167
aa  1234  <--- You want to delete this row
bb  12  <--- You want to delete this row
ff  89

    • To

      aa  123
bb  12345
cc  1235
dd  167
ff  89

If my understanding is correct, how about using removeDuplicates()? This method was added at July 26, 2019. I think that using this method, your script will become more simple. Please think of this as just one of several answers.

Modified script 1:

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(1, 1, sheet.getLastRow(), 2); // Column A and B
  range.removeDuplicates([1]);
}

Modified script 2:

If your script is modified, how about the following modification?

From: 
if (row.join() == newData[j].join()) {
To: 
if (row[0] == newData[j][0]) {

Reference:

If I misunderstood your question and this was not the result you want, I apologize.

Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

should i delete my old script and paste this " Modified script 1 " it's small and simple
@Mohamed Montaser Thank you for replying. The method of removeDuplicates() was added last month. I thought that this might be useful for your situation. So I used this for your situation. If that was useful for your situation, I'm glad.
@Mohamed Montaser Did my answer show you the result what you want? Would you please tell me about it? That is also useful for me to study. If this works, other people who have the same issue with you can also base your question as a question which can be solved. If you have issues for my answer yet, I apologize. At that time, can I ask you about your current situation? I would like to study to solve your issues.
2

This function removes rows where there are duplicates in column A.

function removeDuplicates() {
  var sh=SpreadsheetApp.getActiveSheet();
  var dt=sh.getDataRange().getValues();
  var uA=[];
  var d=0;
  for(var i=0;i<dt.length;i++) {
    if(uA.indexOf(dt[i][0])==-1) {
      uA.push(dt[i][0]);
    }else{
      sh.deleteRow(i+1-d++);
    }
  }
}
Improve this answer

3 Comments

Might be useful for someone using this function: if you want to change the column it uses for duplicate detection; Change the 0 in those two instances of dt[i][0]
If they want to change it they are welcome to change it
How would you modify this to remove the oldest duplicate rather than the most recent?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.