Apps script to remove duplciate rows from a sheet

by


Below is a simple Google Apps Script that you can use to remove duplicate rows from a Google Sheet. This script assumes that your data starts in the first row and column of the sheet and that rows are considered duplicates if every cell in the row matches another row.

To use this script, follow these steps:

  1. Open your Google Sheet.
  2. Go to Extensions > Apps Script.
  3. Delete any code in the script editor and replace it with the code below.
  4. Save the script with a name of your choice.
  5. Run the function removeDuplicates from within the Apps Script editor to remove duplicate rows from your sheet.

This script compares each row with every other row. If it finds a duplicate, it skips adding that row to the newData array. In the end, it clears the original sheet and writes back only the unique rows. Be sure to save and authorize the script before running it. Also, please note that running this script will overwrite your existing data, so you may want to create a copy of your sheet before you use this script if you want to retain the original data.

Here is the script:

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

  // Iterate over the rows and store the index of duplicate rows
  for (var i = 0; i < data.length; i++) {
    var row = data[i];
    var duplicate = false;

    // Check if the row is a duplicate of any previously encountered row
    for (var j = 0; j < newData.length; j++) {
      if (arraysEqual(row, newData[j])) {
        duplicate = true;
        duplicateIndices.push(i);  // Store index of the duplicate row
        break;
      }
    }

    // If not a duplicate, add it to the new data array
    if (!duplicate) {
      newData.push(row);
    }
  }

  // Clear the sheet and set the new data without duplicates
  sheet.clearContents();
  sheet.getRange(1, 1, newData.length, newData[0].length).setValues(newData);

  // Optional: Log the indices of removed rows, adjust based on whether your sheet has headers
  Logger.log("Removed duplicate rows at the following original indices: " + duplicateIndices.join(", "));
}

// Helper function to compare two arrays (rows)
function arraysEqual(a, b) {
  if (a === b) return true;
  if (a == null || b == null) return false;
  if (a.length !== b.length) return false;

  for (var i = 0; i < a.length; i++) {
    if (a[i] !== b[i]) return false;
  }
  return true;
}