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.