Need to easily find duplicates in Google Sheets? This is a critical feature, not finding a good alternative, I built my own.

In this tutorial I continue to work on the duplicates program from my last video.  I added the ability to scan a row or a column for duplicated data, making the application more versatile and useful.

The next feature I plan on adding is a user interface. A simplistic one, just a menu item and a prompt allowing the user to type in the column or row they wish to scan. It helps make Apps Script more approachable to those that don’t want to dive into the actual JavaScript.

Full code and a link to the Google Sheet are all posted below. Feel free to borrow, change, use, criticise or enjoy.

Plain Text

[code]
// Find Duplicates
// Kurt Kaiser, 2018

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

// Creates an array with data from a chosen column
function readColumnData() {
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;
}

// Creates an array with data from a chosen row
function readRowData() {
var row = 2;
var lastColumn = sheet.getLastColumn();
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]) {
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 column
function highlightColumnDuplicates(indexes) {
var column = 2;
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(indexes) {
var row = 2;
for (n = 0; n < indexes.length; n++) {
sheet.getRange(row, indexes[n] + 1).setBackground("yellow");
}
}

//———– Main ————-
function columnMain() {
var data = readColumnData();
var duplicates = findDuplicates(data);
var indexes = getIndexes(data, duplicates);
highlightColumnDuplicates(indexes);
}

function rowMain() {
var data = readRowData();
var duplicates = findDuplicates(data);
var indexes = getIndexes(data, duplicates);
highlightRowDuplicates(indexes);
}
[/code]