For privacy reasons YouTube needs your permission to be loaded. For more details, please see our Privacy Policy.

Introduction

Elevating Data Management in Google Sheets

Discover the full potential of Google Apps Script as we guide you through automating the removal of duplicate entries in your Google Sheets data. From initiating a custom “Dupe Checker” menu to implementing advanced scripts for pinpointing and deleting duplicates, this video tutorial is your gateway to mastering data cleanup operations.

Implementation

Your Blueprint to Eliminating Duplicates

Custom “Dupe Checker” Menu

Ease of Access: Kickstart your journey by adding a custom menu to Google Sheets, offering quick access to duplicate checking functionalities.

Highlighting and Removing Duplicates

Single-Column Focus: Tackle duplicates in individual columns by highlighting them for easy identification or removing them for cleaner data.
Multi-Column Mastery: Elevate your skills by learning to handle duplicates that span across multiple columns, ensuring a thorough cleanup.

Script-Driven Solutions

Interactive Scripting Experience: Engage with a hands-on scripting approach that not only teaches you to write efficient code but also to customize it according to your specific data management needs.
Practical Application: Apply these scripts to real-world scenarios, enhancing the integrity of your datasets and automating tedious manual processes.

Tips and best practices

Why This Tutorial Stands Out

Comprehensive Coverage: From basic principles to advanced techniques, our tutorial covers every aspect of duplicate removal in Google Sheets.
User-Friendly Guidance: With step-by-step instructions and clear explanations, we ensure that users of all skill levels can confidently navigate through the process.
Immediate Application: Implement these strategies directly into your Google Sheets projects, witnessing instant improvements in data quality and organization.

Conclusion

Say farewell to the frustrations of handling duplicate data in Google Sheets. Our tutorial on using Google Apps Script for duplicate removal transforms how you interact with your datasets. By integrating these scripts into your spreadsheets, you’re not just cleaning data; you’re optimizing your workflow for greater efficiency and reliability. Dive into our guide and unlock the secrets to maintaining pristine data in Google Sheets.

Appscript Code

Copy to Clipboard

// Function triggered when the Google Sheets document is opened.
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Dupe Checker')
    .addItem('Highlight Duplicate', 'highlightDuplicates')
    .addItem('Remove Single Column Duplicates', 'removeDuplicates')
    .addItem('Highlight Multi-Column Duplicates', 'highlightMultiColumnDuplicates')
    .addItem('Remove Multi-Column Duplicates', 'removeMultiColumnDuplicates')
    .addToUi();
}

// Function to highlight duplicate values in a specified column.
function highlightDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnLetter = getColumnInput('Highlight Duplicates');
  if (!columnLetter) return;
  
  var columnNumber = columnLetter.charCodeAt(0) - 64;
  var data = sheet.getDataRange().getValues();
  var seenValues = {};
  var duplicateDetails = {};
  
  for (var i = 1; i < data.length; i++) {
    var cellValue = data[i][columnNumber - 1];
    if (seenValues[cellValue]) {
      sheet.getRange(i + 1, columnNumber).setBackground('yellow');
      duplicateDetails[cellValue] = (duplicateDetails[cellValue] || 1) + 1;
    } else {
      seenValues[cellValue] = true;
    }
  }
  
  var message = Object.keys(duplicateDetails).map(function(key) {
    return 'Value "' + key + '" has ' + (duplicateDetails[key] - 1) + ' additional occurrences.';
  }).join('\n');

SpreadsheetApp.getUi().alert(message || 'No duplicates found.');
}

// Function to remove duplicate rows in the sheet based on a single specified column.
function removeDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnLetter = getColumnInput('Remove Duplicates');
  if (!columnLetter) return;

sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).setBackground(null);
  
  var columnNumber = columnLetter.charCodeAt(0) - 64;
  var data = sheet.getDataRange().getValues();
  var uniqueRows = [data[0]];
  var seenValues = {};

for (var i = 1; i < data.length; i++) {
    var cellValue = data[i][columnNumber - 1];
    if (!seenValues[cellValue]) {
      uniqueRows.push(data[i]);
      seenValues[cellValue] = true;
    }
  }

sheet.clearContents();
  sheet.getRange(1, 1, uniqueRows.length, uniqueRows[0].length).setValues(uniqueRows);
}

// Function to highlight duplicates across multiple columns.
function highlightMultiColumnDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnLetters = getColumnInput('Highlight Multi-Column Duplicates', true);
  if (!columnLetters) return;
  
  var columnNumbers = columnLetters.split(',').map(function(letter) {
    return letter.trim().toUpperCase().charCodeAt(0) - 64;
  });
  
  var data = sheet.getDataRange().getValues();
  var seenCombinations = {};
  var duplicateDetails = {};

for (var i = 1; i < data.length; i++) {
    var combination = columnNumbers.map(function(number) {
      return data[i][number - 1];
    }).join('|');
    
    if (seenCombinations[combination]) {
      columnNumbers.forEach(function(number) {
        sheet.getRange(i + 1, number).setBackground('yellow');
      });
      duplicateDetails[combination] = (duplicateDetails[combination] || 1) + 1;
    } else {
      seenCombinations[combination] = true;
    }
  }
  
  var message = Object.keys(duplicateDetails).map(function(key) {
    return 'Combination "' + key.replace(/\|/g, ', ') + '" has ' + (duplicateDetails[key] - 1) + ' additional occurrences.';
  }).join('\n');

SpreadsheetApp.getUi().alert(message || 'No duplicates found.');
}

// Function to remove duplicates across multiple columns.
function removeMultiColumnDuplicates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var columnLetters = getColumnInput('Remove Multi-Column Duplicates', true);
  if (!columnLetters) return;

sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).setBackground(null);
  
  var columnNumbers = columnLetters.split(',').map(function(letter) {
    return letter.trim().toUpperCase().charCodeAt(0) - 64;
  });
  
  var data = sheet.getDataRange().getValues();
  var uniqueRows = [data[0]];
  var seenCombinations = {};

for (var i = 1; i < data.length; i++) {
    var combination = columnNumbers.map(function(number) {
      return data[i][number - 1];
    }).join('|');

if (!seenCombinations[combination]) {
      uniqueRows.push(data[i]);
      seenCombinations[combination] = true;
    }
  }

sheet.clearContents();
  sheet.getRange(1, 1, uniqueRows.length, uniqueRows[0].length).setValues(uniqueRows);
}

// Helper function to get column input from the user.
function getColumnInput(actionName, isMultiColumn = false) {
  var message = isMultiColumn ? 'Enter the column letters separated by comma (e.g., A,B):' : 'Enter the column letter:';
  var response = SpreadsheetApp.getUi().prompt(actionName, message, SpreadsheetApp.getUi().ButtonSet.OK_CANCEL);
  if (response.getSelectedButton() !== SpreadsheetApp.getUi().Button.OK) {
    return null;
  }
  return response.getResponseText();
}

This blog is my canvas, where I paint with words, sharing tutorials, tips, and tidbits that have enriched my life.

I welcome you. Dive into my articles, engage with my stories, and let’s embark on this journey of discovery together.

Happy reading, and thank you for visiting ArisAzhar.com!

Aris Azhar
Knowledge Seeker

Over 8,000
Benefited from my Channel

Check out my tutorials

I hope you can join in as well!

Effortlessly Remove Duplicates in Google Sheets Using Apps Script

Introduction

Implementation

Tips and best practices

Appscript Code

Introduction