Welcome to LadWhoCodes
Home / Blog / Google sheets / Remove duplicate rows from your Google Spreadsheet

Remove duplicate rows from your Google Spreadsheet

Google Spreadsheet is a powerful tool that has emerged as a powerful competitor to Microsoft Excel. One of the main reasons for its popularity is the ability to collaborate and view/edit sheets. One more reason for its popularity is the ability to use JavaScript to perform custom operations in the Google sheet. You can write JavaScript code and add a wide range of features and functionalities to your Sheet.

Today, we will be writing a Google Sheet Script which will help us in removing duplicate entries from a sheet. This script is highly modular and you can easily extend it to modify the unique criteria.

Remove duplicates from Google Sheet

function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var data = sheet.getDataRange().getValues();
  var newData = [];
  // At this point we are iterating from 1st row to the last one
  // If you want to have the most recent unique row only, then reverse the loop here
  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var duplicate = false;
    for (j in newData) {
      // At this point we are checking if the 0th column value is exisiting or not
      // If it exists, then its not included in the newData array
      // Hence, we get rows with unique first column value
      if (row[0] == newData[j][0]) {
        duplicate = true;
      }
    }

    // Also, if you had reverse the loop condition above, unComment the line below
    // So that you get the array back in original order
    //newData.reverse();

    if (!duplicate) {
      newData.push(row);
    }
  }
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
}

Please let me know if you faced any issue.

2 Comments

  1. I try to keep the newest data and delete duplicate old data but script doesn’t work please help me.

    function removeDuplicates() {
    var sheet = SpreadsheetApp.getActiveSheet();
    var data = sheet.getDataRange().getValues();
    var newData = [];

    for (var i = data.length; i >0; i–) {
    var row = data[i];
    var duplicate = false;
    for (j in newData) {
    if (row[0] == newData[j][0]) {
    duplicate = true;
    }
    }

    newData.reverse();
    if (!duplicate) {
    newData.push(row);
    }
    }
    sheet.clearContents();
    sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);
    }

Leave a Reply

Address
304 North Cardinal
St. Dorchester Center, MA 02124

Work Hours
Monday to Friday: 7AM - 7PM
Weekend: 10AM - 5PM