Google Sheets - Find Duplicates

find_dupes.js

// Find Duplicates
// Kurt Kaiser, 2018

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();

// Creates an array with data from a chosen column
function readData() {
  var column = 2;
  var lastRow = sheet.getLastRow();
  var columnRange = sheet.getRange(1, column, lastRow);
  var rangeArray = columnRange.getValues();
  // Convert to one dimensional array
  rangeArray = [].concat.apply([], 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]) {
      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);
    }
  }
  // Highlight all instances of duplicate values
  for (n = 0; n < indexes.length; n++) {
    sheet.getRange(indexes[n] + 1, column).setBackground("yellow");
  }
}

//----------- Main -------------
function main() {
  var data = readData();
  var duplicates = findDuplicates(data);
  getIndexes(data, duplicates);
}