Highlight Duplicates in Google Sheets Using Apps Script

by

In this tutorial, we will create a Google Apps Script that highlights duplicates in a specified column of a Google Sheet. The script will ignore the first occurrence of each value and only highlight subsequent duplicates. This can be particularly useful for data analysis, ensuring that you can easily spot repeated entries.

Step-by-Step Guide

Step 1: Open Your Google Sheet

Open the Google Sheet where you want to implement the script.

Step 2: Open the Script Editor

  1. Click on Extensions in the menu.
  2. Select Apps Script.

Step 3: Write the Script

Replace any code in the script editor with the following:

function highlightDuplicates() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange('A:A'); // Modify this to the column you want to check
  const values = range.getValues();
  const duplicates = {};
  
  // Loop through each value in the column
  for (let i = 0; i < values.length; i++) {
    const value = values[i][0];
    
    if (value && duplicates[value]) {
      // Highlight the cell if it's a duplicate
      sheet.getRange(i + 1, 1).setBackground('yellow');
    } else if (value) {
      // Mark the first occurrence
      duplicates[value] = true;
    }
  }
}

This script loops through each value in the specified column (A:A), checks if it has been seen before, and highlights the cell if it is a duplicate. The first occurrence of each value is not highlighted.

Step 4: Save and Run the Script

  1. Save your script by clicking the disk icon or selecting File > Save.
  2. Give your project a name, such as “Highlight Duplicates”.
  3. To run the script, click the play button (▶) in the toolbar.

Step 5: Set Up a Trigger (Optional)

If you want the script to run automatically (e.g., every time the sheet is edited), you can set up a trigger:

  1. Click on the clock icon in the toolbar or go to Triggers in the left sidebar.
  2. Click Add Trigger.
  3. Choose highlightDuplicates as the function to run.
  4. Select From spreadsheet as the event source.
  5. Choose On edit as the event type.
  6. Click Save.

Conclusion

With this simple script, you can easily highlight duplicate values in a Google Sheets column, ignoring the first occurrence. This can help you quickly identify and manage duplicate data entries, improving the overall quality and accuracy of your data analysis.