Search a Google Sheet for Duplicates with this Macro

Being a teacher I often am working with datasets that contain hundreds or even thousands of rows of information. I need to be able to easily locate duplicate data within spreadsheets, it is an essential tool for me.

In this tutorial I continue to build upon the find duplicate program I have been working on. The program already can find duplicate information within a single column or row, this adds the ability to search an entire sheet for duplicates.

Links to the previous tutorials of me building this program and the full code are below the video. Feel free to copy, edit, rework, ruin or mock my code.

Plain Text


// Find Duplicates
// Kurt Kaiser, 2018

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var lastRow = sheet.getLastRow();
var lastColumn = sheet.getLastColumn();

function readSheetData() {
  var rowRange = sheet.getRange(1, 1, lastRow, lastColumn);
  var rangeArray = rowRange.getValues();
  // Convert to a one dimensional array
  rangeArray = [].concat.apply([], rangeArray);
  return rangeArray;
}

// Creates an array with data from a chosen column
function readColumnData(column) {
  var columnRange = sheet.getRange(1, column, lastRow);
  var rangeArray = columnRange.getValues();
  // Convert to one dimensional array
  rangeArray = [].concat.apply([], rangeArray);
  return rangeArray;
}

// Creates an array with data from a chosen row
function readRowData(row) {
  var rowRange = sheet.getRange(row, 1, 1, lastColumn);
  var rangeArray = rowRange.getValues();
  // Convert to one dimensional array
  rangeArray = [].concat.apply([], rangeArray);
  Logger.log(rangeArray);
  return rangeArray;
}

// Sort data and find duplicates
function findDuplicates(data) {
  var sortedData = data.slice().sort();
  var duplicates = [];
  for (var i = 0; i < sortedData.length - 1; i++) {
    if (sortedData[i + 1] == sortedData[i] && sortedData[i] != "") {
      duplicates.push(sortedData[i]);
    }
  }
  return duplicates;
}

// Find locations of all duplicates
function getIndexes(data, duplicates) {
  var column = 2;
  var indexes = [];
  i = -1;
  // Loop through duplicates to find their indexes
  for (var n = 0; n < duplicates.length; n++) {
    while ((i = data.indexOf(duplicates[n], i + 1)) != -1) {
      indexes.push(i);
    }
  }
  return indexes;
}

// Highlight all instances of duplicate values in a sheet
function highlightSheetDuplicates(indexes) {
  var row;
  for (n = 0; n < indexes.length; n++) { row = 1; if (indexes[n] > lastColumn) {
      row = Math.floor(indexes[n] / lastColumn);
      indexes[n] = indexes[n] - lastColumn * row;
      row++;
    }
    sheet.getRange(row, indexes[n] + 1).setBackground("yellow");
  }
}


// Highlight all instances of duplicate values in a column
function highlightColumnDuplicates(column, indexes) {
  for (n = 0; n < indexes.length; n++) {
    sheet.getRange(indexes[n] + 1, column).setBackground("yellow");
  }
}

// Highlight all instances of duplicate values in a row
function highlightRowDuplicates(row, indexes) {
  for (n = 0; n < indexes.length; n++) {
    sheet.getRange(row, indexes[n] + 1).setBackground("yellow");
  }
}

//----------- Main -------------

function sheetMain() {
  var data = readSheetData();
  var duplicates = findDuplicates(data);
  var indexes = getIndexes(data, duplicates);
  highlightSheetDuplicates(indexes);
}

function columnMain(column) {
  var data = readColumnData(column);
  var duplicates = findDuplicates(data);
  var indexes = getIndexes(data, duplicates);
  highlightColumnDuplicates(column, indexes);
}

function rowMain(row) {
  var data = readRowData(row);
  var duplicates = findDuplicates(data);
  var indexes = getIndexes(data, duplicates);
  highlightRowDuplicates(row, indexes);
}

// ---------- Menu ----------
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Find Duplicates')
    .addItem('Search Sheet', 'sheetMain')
    .addItem('Select a Row', 'showRowPrompt')
    .addItem('Select a Column', 'showColumnPrompt')
    .addToUi();
}

// ---------- Prompt ----------
function showColumnPrompt() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt(
    'Find Duplicates',
    'Enter letter of column to search:',
    ui.ButtonSet.OK_CANCEL);
  // Get user response, run main
  var button = response.getSelectedButton();
  var text = response.getResponseText();
  if (button == ui.Button.OK) {
    text = sheet.getRange(text + "1");
    text = text.getColumn();
    columnMain(text);
  }
}

function showRowPrompt() {
  var ui = SpreadsheetApp.getUi();
  var response = ui.prompt(
    'Find Duplicates',
    'Enter number of row to search:',
    ui.ButtonSet.OK_CANCEL);
  // Get user response, run main
  var button = response.getSelectedButton();
  var text = response.getResponseText();
  if (button == ui.Button.OK) {
    rowMain(text);
  }
}

Leave a Reply Cancel reply