Copy unique rows to a clean tab

1 · Prepare

Put these pieces in place

• Create a tab named Contacts with headers in row 1 and one person per row.
• Put the email address in column B. That is the value the script uses to spot a repeat.
• Do not keep notes of your own on a tab named Clean, because that tab is rewritten on every run.

2 · Review before running

What the script can access and change

3 · Copy

Paste this into the Apps Script editor

Create or open a script file, replace the example values marked above, then save the project.

const SOURCE_TAB = 'Contacts'; // This must match the tab that holds your list.
const OUTPUT_TAB = 'Clean'; // The script creates this tab when it is missing and clears it before each write.
const KEY_COLUMN_INDEX = 1; // Column B is index 1. Counting starts at 0, so column A is index 0 and column C is index 2.

function copyUniqueRowsToCleanTab() {
  const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  const source = spreadsheet.getSheetByName(SOURCE_TAB);
  if (!source) throw new Error('Add a Sheet tab named ' + SOURCE_TAB + ' before running this function.');

  const values = source.getDataRange().getValues();
  if (values.length < 2) throw new Error('The ' + SOURCE_TAB + ' tab needs a header row and at least one row of data.');

  const header = values[0];
  const seenKeys = new Set();
  /** @type {unknown[][]} */
  const uniqueRows = [];
  let duplicateCount = 0;

  values.slice(1).forEach((row) => {
    const key = String(row[KEY_COLUMN_INDEX]).trim().toLowerCase();
    if (!key) return;
    if (seenKeys.has(key)) {
      duplicateCount = duplicateCount + 1;
      return;
    }
    seenKeys.add(key);
    uniqueRows.push(row);
  });

  const clean = spreadsheet.getSheetByName(OUTPUT_TAB) || spreadsheet.insertSheet(OUTPUT_TAB);
  clean.clearContents();

  const output = [header].concat(uniqueRows);
  clean.getRange(1, 1, output.length, header.length).setValues(output);

  Logger.log('Unique rows kept: ' + uniqueRows.length + '. Duplicate rows skipped: ' + duplicateCount + '.');
}

4 · Run safely

Follow these steps in order

1. Build a small test list

   On the Contacts tab, add five or six rows and give two of them the same email address. That way you can see one row kept and one row skipped.

2. Paste the function and save

   Open Extensions, choose Apps Script, paste the function, and save it.

3. Run it and read the log

   Choose copyUniqueRowsToCleanTab and run it. Approve access when Google asks. The Execution log panel shows a line such as "Unique rows kept: 5. Duplicate rows skipped: 1."

4. Check both tabs

   The Clean tab holds the header and one row per address. The Contacts tab is exactly as you left it. Run the function again after adding rows. It clears Clean and writes it from scratch each time.

5 · Confirm

How to tell it worked

The Clean tab holds the header and one row per email address, and the execution log reports how many rows were kept and how many duplicates were skipped.

If the result is different

Troubleshooting

The script says "Add a Sheet tab named Contacts before running this function."

That is the check at the top of the function. Rename your tab to Contacts, or change the name in the code and in the editable values.

Two rows that look the same both landed in Clean.

The script compares column B only, after trimming spaces and lowering the case. Look for a small difference such as a different domain or an extra character. If a different column identifies a repeat, change KEY_COLUMN_INDEX to that column's index.

The Clean tab lost rows I typed there myself.

Every run clears the Clean tab and writes it again. Keep your own notes on another tab, or change the output tab name in the code before you run it.

The script says "The Contacts tab needs a header row and at least one row of data."

The tab holds fewer than two rows. Put your column titles in row 1 and at least one person below them.