Validate and Clean Data in Google Sheets with Apps Script

Dirty data causes bad decisions. Apps Script can automate the tedious work of validating, cleaning, and standardising data in your spreadsheets — far more powerfully than built-in data validation alone.

Trim whitespace from all cells

function trimAllCells() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getDataRange();
  const values = range.getValues();

  const cleaned = values.map(row =>
    row.map(cell => typeof cell === 'string' ? cell.trim() : cell)
  );

  range.setValues(cleaned);
  Logger.log('Trimmed whitespace from all cells.');
}

Standardise email addresses to lowercase

function standardiseEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const emailCol = headers.findIndex(h => h.toLowerCase().includes('email'));

  if (emailCol === -1) {
    Logger.log('No email column found.');
    return;
  }

  const dataRange = sheet.getRange(2, emailCol + 1, sheet.getLastRow() - 1, 1);
  const emails = dataRange.getValues();

  const cleaned = emails.map(([email]) => [
    typeof email === 'string' ? email.toLowerCase().trim() : email
  ]);

  dataRange.setValues(cleaned);
  Logger.log('Emails standardised.');
}

Flag invalid email addresses

function flagInvalidEmails() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const emailCol = headers.indexOf('Email');
  const emailRegex = /^[^\s@]+@[^\s@]+\.[^\s@]+$/;

  for (let i = 1; i < data.length; i++) {
    const email = data[i][emailCol];
    const cell = sheet.getRange(i + 1, emailCol + 1);

    if (!emailRegex.test(email)) {
      cell.setBackground('#fce8e6'); // Red highlight
      Logger.log(`Row ${i + 1}: Invalid email — "${email}"`);
    } else {
      cell.setBackground(null); // Clear if previously flagged
    }
  }
}

Find and highlight duplicate values

function highlightDuplicates() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const colLetter = 'B'; // Column to check for duplicates
  const lastRow = sheet.getLastRow();
  const range = sheet.getRange(`${colLetter}2:${colLetter}${lastRow}`);
  const values = range.getValues().flat();

  const seen = {};
  const duplicates = new Set();

  values.forEach(v => {
    if (v === '') return;
    if (seen[v]) duplicates.add(v);
    seen[v] = true;
  });

  values.forEach((v, i) => {
    const cell = range.getCell(i + 1, 1);
    cell.setBackground(duplicates.has(v) ? '#fff2cc' : null);
  });

  Logger.log(`Found ${duplicates.size} duplicate value(s).`);
}

Standardise phone number format

function cleanPhoneNumbers() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const headers = sheet.getRange(1, 1, 1, sheet.getLastColumn()).getValues()[0];
  const phoneCol = headers.findIndex(h => h.toLowerCase().includes('phone'));

  if (phoneCol === -1) return;

  const range = sheet.getRange(2, phoneCol + 1, sheet.getLastRow() - 1, 1);
  const phones = range.getValues();

  const cleaned = phones.map(([phone]) => {
    const digits = String(phone).replace(/\D/g, '');
    if (digits.length === 10) {
      return [`(${digits.slice(0,3)}) ${digits.slice(3,6)}-${digits.slice(6)}`];
    }
    return [phone]; // Leave unchanged if unexpected length
  });

  range.setValues(cleaned);
}

Remove blank rows

function removeBlankRows() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  let removed = 0;

  // Iterate from bottom to top to avoid index shifting
  for (let i = data.length - 1; i >= 1; i--) {
    const isEmpty = data[i].every(cell => cell === '' || cell === null);
    if (isEmpty) {
      sheet.deleteRow(i + 1);
      removed++;
    }
  }

  Logger.log(`Removed ${removed} blank row(s).`);
}

Run all cleaning functions in sequence

function cleanAllData() {
  trimAllCells();
  standardiseEmails();
  flagInvalidEmails();
  highlightDuplicates();
  removeBlankRows();
  Logger.log('Full data clean complete.');
}

Tips

Always work on a copy of your data the first time you run a cleaning script.
Reading and writing the entire range at once (with getValues() / setValues()) is much faster than cell-by-cell operations.
Use setBackground(null) to clear highlights rather than setBackground('#ffffff') — it respects the sheet's default theme.
Combine with an onEdit trigger for real-time validation as data is entered.