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

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

   Choose Extensions, then Apps Script. Paste the function over the starter code and save.

3. Run it and read the log

   Choose copyUniqueRowsToCleanTab in the function dropdown above the code, then click Run. Approve access when Google asks. The Execution log 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 email address. Contacts is unchanged. Each later run clears Clean and writes it again from the current Contacts tab.

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 run stops with "Add a Sheet tab named Contacts before running this function."

Rename your tab to Contacts, or change SOURCE_TAB in the code to the name of your tab.

Two rows that look the same both landed in Clean.

Compare the two email addresses character by character. The script compares column B only, ignoring spaces around the address and capital letters, so a different domain or an extra character makes two rows different. If another column identifies a repeat, change KEY_COLUMN_INDEX to that column's index.

The Clean tab lost rows I typed there myself.

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

The run stops with "The Contacts tab needs a header row and at least one row of data."

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