How to Remove Duplicate Rows in Google Spreadheets

Google Apps Script, or COUNTIF, or UNIQUE formulas

Duplicate rows in Google Sheets can clutter your data and lead to incorrect analysis and wasting your time. Whether you’re managing a large dataset, collecting form responses, or working with imported files, you will definitely need to know how to remove duplicate rows in your Google Spreadsheets.

You may use:

• Google Apps Script
• COUNTIF formula
• UNIQUE formula

Method 1: Using the UNIQUE Formula (Quick & Easy)

Best for:

Simple deduplication based on entire row or specific columns.

How to Use:

If you want to extract only unique rows from a dataset in A2:D100, you can use:

=UNIQUE(A2:D100)

This will return all unique rows from the range.

What’s good:

• Automatically updates when new data is added.
• Non-destructive — original data stays intact.

Method 2: Using COUNTIF to Highlight or Filter Duplicates

Best for:

Identifying duplicates without removing them immediately.

How to Use:

In a new column, use the following formula:

=COUNTIF(A$2:A2, A2)

This counts how many times the value in A2 has appeared up to that row. If the result is greater than 1, it’s a duplicate.

You can then use Filters or Conditional Formatting to highlight or remove those rows manually.

Method 3: Using Google Apps Script (Automated & Customizable)

Best for:

Large datasets or automating deduplication tasks.

Step-by-Step Guide:

1. Open your Google Sheet.
2. Click on Extensions → Apps Script.
3. Delete any code in the editor and paste this:
4. Save the script and run removeDuplicateRows.

function removeDuplicateRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const uniqueData = [];
  const seen = new Set();

  for (let i = 0; i < data.length; i++) {
    const row = JSON.stringify(data[i]);
    if (!seen.has(row)) {
      seen.add(row);
      uniqueData.push(data[i]);
    }
  }

  sheet.clearContents();
  sheet.getRange(1, 1, uniqueData.length, uniqueData[0].length).setValues(uniqueData);
}

This script removes entire duplicate rows and replaces the sheet content with only unique rows.

Customize It:

• Modify the sheet variable to target a specific sheet.
• Use getRange(startRow, startColumn, numRows, numColumns) if you want to limit the range.

Right now the script assumes all rows are the same length. You could add:

if (data.length === 0 || data[0].length === 0) {
  SpreadsheetApp.getUi().alert("No data found.");
  return;
}

How to Set a Time-Driven Trigger (e.g., Once Per Day) or OnEdit Trigger For Automation?

1. Time-Driven Trigger (e.g., Once Per Day)

This trigger will run your script automatically on a schedule – daily, hourly, etc.

How to Set It Up:

1. Open your Google Sheet.
2. Go to Extensions → Apps Script.
3. In the script editor, make sure your function (e.g., removeDuplicateRows) is saved.
4. Click the clock icon Triggers in the left sidebar. Or go to Triggers → Add Trigger.
5. Set it up like this:
   • Choose which function to run: removeDuplicateRows
   • Choose which deployment should run: Head
   • Select event source: Time-driven
   • Select type of time-based trigger: e.g., Day timer
   • Select time of day: Choose your preferred time
6. Click Save. Done!

Now the script will run automatically every day.

2. onEdit Trigger (Run Script When Sheet Is Edited)

This trigger will run your script every time any cell is edited – super handy but be cautious with large data.

How to Use:

Update your function like this:

function onEdit(e) {
  removeDuplicateRows(); // your existing function
}

Alternatively, wrap the logic directly in onEdit if you want more control:

function onEdit(e) {
  const sheet = e.source.getActiveSheet();
  if (sheet.getName() === "Sheet1") { // Optional: run only on a specific sheet
    removeDuplicateRows();
  }
}

Note:

• onEdit cannot access triggers or UI elements (e.g., SpreadsheetApp.getUi()).
• It runs automatically with limited permissions unless you use an installable trigger.

Optional: Installable onEdit Trigger (More Power)

If you need more control (e.g., send emails, access services), you can create an installable version of the edit trigger:

1. Go to Triggers → Add Trigger
2. Function: onEdit
3. Event source: From spreadsheet
4. Event type: On edit

Try them out, and pick the one that works best for your needs!