Tuesday, April 1, 2014

yogi_Highlight Duplicate Entries In A Column -- All FirstInstance SubsequentInstances

                                         Google Spreadsheet   Post  #1583
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Apr-01-2014
post by Casey Cassel (https://productforums.google.com/forum/#!mydiscussions/docs/WpuYBjrhVJ0)
Spreadsheets Script Cell Background Color
I'm creating a spreadsheet for my workplace that is a pretty basic work log. Users will fill out the log with different information on each order. We are wanting a way of "duplicate orders" being highlighted in an effort to eliminate two people doing the same order simultaneously. The first column of our spreadsheet is the order number that we are wanting to highlight as RED if it's a duplicate number. The following code does that.. but it also changed non-duplicates to WHITE. 

One of our coworkers came up with the following code, but the problem is that it creates all of the cells that are not duplicates as 'WHITE'. We have an alternating row pattern that I want to keep, but the code makes the entire column 'WHITE'. Is there a way of altering this code to having the non-duplicates retain their original cell color?


function onOpen() {
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{name : "Check Duplicates",functionName : "checkDuplicates"}];
.addMenu("Scripts", entries);

function onEdit() {
var sheet = SpreadsheetApp.getActiveSheet();
var dataRange = sheet.getRange("A32:A60"); // 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++) {
[i] = [];
for (var j = 0; j < numColumns; j++) {
[i][j] = 'WHITE';
if (data[i][j] != '') {
.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]) {
[values[k][1]][values[k][2]] = 'RED';
[values[l][1]][values[l][2]] = 'RED';


Any help would be much appreciated. If I need to provide any more information or clarify anything please let me know.