Skip to content

Instantly share code, notes, and snippets.

@lhsfcboy
Created August 25, 2017 17:52
Show Gist options
  • Save lhsfcboy/ceaa50a1f5ff33505dc3dd97805dcee9 to your computer and use it in GitHub Desktop.
Save lhsfcboy/ceaa50a1f5ff33505dc3dd97805dcee9 to your computer and use it in GitHub Desktop.
Google Script check duplication item
function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{name : "Check Duplicates",functionName : "checkDuplicates"}];
sheet.addMenu("Scripts", entries);
};
function checkDuplicates() {
var sheet = SpreadsheetApp.getActiveSheet();
//var dataRange = sheet.getDataRange();
var dataRange = sheet.getRange("A:A"); // Set Any Range
var data = dataRange.getValues();
var numRows = data.length;
var numColumns = data[0].length;
var formats = [];
var values = [];
for (var i = 0; i < numRows; i++) {
formats[i] = [];
for (var j = 0; j < numColumns; j++) {
formats[i][j] = 'WHITE';
if (data[i][j] != '') {
values.push([data[i][j], i, j]);
}
}
}
var numValues = values.length;
for (var k = 0 ; k < numValues - 1; k++) {
if (formats[values[k][1]][values[k][2]] == 'WHITE') {
for (var l = k + 1; l < numValues; l++) {
if (values[k][0] == values[l][0]) {
formats[values[k][1]][values[k][2]] = 'RED';
formats[values[l][1]][values[l][2]] = 'RED';
}
}
}
}
dataRange.setBackgroundColors(formats);
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment