What trigger should I use for Deduplicate Spreadsheet Rows by Unique Key Column?

This script is designed for: On Demand. Configure it under Apps Script > Triggers (clock icon).

What OAuth permissions does Deduplicate Spreadsheet Rows by Unique Key Column require?

This script requests the following authorized scopes: https://www.googleapis.com/auth/spreadsheets.

Google Sheets AutomationDifficulty: Intermediate⚡ On Demand

Deduplicate Spreadsheet Rows by Unique Key Column

Scans thousands of spreadsheet rows, identifies duplicate values in a designated key column (e.g. Email or Order ID), and deletes duplicate rows while preserving the newest entry.

Required OAuth Scopes: https://www.googleapis.com/auth/spreadsheets

Code.gs (Apps Script V8 Runtime)

function removeDuplicateRowsByKey() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  const data = sheet.getDataRange().getValues();
  const keyColIndex = 1; // 1 = Column B (0-indexed: 0=A, 1=B)
  const seen = new Set();
  const rowsToKeep = [];
  
  // Keep header row
  rowsToKeep.push(data[0]);
  
  for (let i = 1; i < data.length; i++) {
    const key = String(data[i][keyColIndex]).trim().toLowerCase();
    if (key && !seen.has(key)) {
      seen.add(key);
      rowsToKeep.push(data[i]);
    }
  }
  
  sheet.clearContents();
  sheet.getRange(1, 1, rowsToKeep.length, rowsToKeep[0].length).setValues(rowsToKeep);
}

Line-by-Line Code Explanation

Line 5-6: Uses a fast JavaScript `Set` for O(1) duplicate lookup.

Line 11-17: Loops rows, normalizing key strings and accumulating unique rows.

Line 19-20: Clears sheet and writes deduplicated rows in 1 batch.

Step-by-Step Setup & Deployment

Set `keyColIndex` to your identifier column index.
Run the function to deduplicate rows in milliseconds.

Best Practices for Quota & Rate Limit Management

When deploying this automation in Google Workspace, adhere to these operational principles:

Batch Reads & Writes: Never execute getValue() or setValue() inside loops. Read ranges into memory in 1 call, process arrays in V8 memory, and commit changes with a single setValues() call.
Execution Timeouts: Apps Script terminates scripts exceeding 6 minutes. For multi-thousand row datasets, implement batch pagination with resume timestamps stored in PropertiesService.
OAuth Permissions: Scope your manifest (appsscript.json) narrowly to prevent security prompts from alarming external team members.

Frequently Asked Questions

Is Google Apps Script completely free to use?

Yes. Google Apps Script is built into all free Google accounts and Google Workspace editions. There are no server fees or subscription costs for running scripts within daily quotas.

Can I run this script on an automated schedule without keeping my computer on?

Yes. Time-driven triggers execute on Google's cloud infrastructure. Your script will run reliably even when your computer is closed or offline.

More Google Sheets Automation Snippets

Google Sheets