The example I’ll use has to highlight cells that are duplicate in specific columns, using a different color for the first of the duplicates and the 2nd and subsequent, so the output will look like this
There are of course many ways to solve this problem, but I’ll be transposing the data to make it easier to selectively work with it.
The Code
We’ll start with the complete code, and then do a walkthrough.
The transpose function
/**
* transpose rows and columns
* @param {*[][]} data an array of arrays of columns .. assumes its not jagged
* @return {*[][]} transposed version
*/
function transpose(data) {
// use the first row of data to decide on
return (data[0] || []).map (function (col , colIndex) {
return data.map (function (row) {
return row[colIndex];
});
});
}
The main code
function highlightDuplicatesCells() {
var ss = SpreadsheetApp.openById('1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o');
var sheet = ss.getSheetByName("highlight-duplicates");
var singleColor = "#ffffff";
var dupColor = " #ffeb3b";
// this one will set dup color for individual cellss that
// are duplicated in specific rows.
// only the 2nd and subsequent dup cells will be highlighted with dup colors.
// the first will get a third color
var firstOfDupsColor = "#80d8ff";
// get the values
var data = sheet.getDataRange().getValues();
// start by setting all the colors to non dups
var colors = data.map (function (row) {
return row.map (function () { return singleColor; });
});
// its going to be easier to work by column, so we'll transpose the data first
var transposed = transpose (data);
// only doing for a selection of columns
[0, 2].forEach (function (col) {
// look at the data columnwise
transposed[col].forEach (function (value) {
// how many times a value appears in this column
var matches = transposed[col].map (function (d, index) {
// return the indices of matches
return value === d ? index : null;
})
.filter (function (d) {
// get rid of non-matches
return d !== null;
});
// if there's duplicates set the dup color, but only for the 2nd and subsequent
if (matches.length > 1 ) {
matches.forEach (function (d, i) {
colors[d][col] = i ? dupColor : firstOfDupsColor;
});
}
});
});
// now write the updated colors
sheet.getDataRange().setBackgrounds (colors);
}
Transpose function
Sheets has a built-in function for transposing, but of course it’s not available in Apps Script. However, it’s very easy to build one. As usual, .map comes int very handy. It’s expecting to see a two-dimensional array, and it will transpose the dimensions. This version is only able to handle non jagged arrays of the type that come back from the SpreadsheetApp .getValues() method. Map will return an array the same shape as its input, so to get a column-shaped array, instead of iterating through the rows of the data to start with, we iterate through the columns of the first row
// use the first row of data to decide on
return (data[0] || []).map (function (col , colIndex) {
Next, for each of the column values we just iterate through the rows and create a map of that shape.
return data.map (function (row) {
return row[colIndex];
});
Finally, close it off and we’re done.
});Setting up
Set up the sheet id, the sheet name the colors to use for duplicate or non- duplicate cells. We have a third color, to be used for the first of a duplicate cell value in the columns being analyzed
var ss = SpreadsheetApp.openById('1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o');
var sheet = ss.getSheetByName("highlight-duplicates");
var singleColor = "#ffffff";
var dupColor = " #ffeb3b";
// this one will set dup color for individual cellss that
// are duplicated in specific rows.
// only the 2nd and subsequent dup cells will be highlighted with dup colors.
// the first will get a third color
var firstOfDupsColor = "#80d8ff";
The data
Read the entire data values into a an array of rows and columns, and set up an array of the same shape, initially assuming that there are no duplicates.
// get the values
var data = sheet.getDataRange().getValues();
// start by setting all the colors to non dups
var colors = data.map (function (row) {
return row.map (function () { return singleColor; });
});
transpose function
// its going to be easier to work by column, so we'll transpose the data first var transposed = transpose (data);
forEach method
We only want to do this for a subset of the data – in this example columns 0 and 2, so we can use .forEach to iterate through each value of [0,2].
// only doing for a selection of columns
[0, 2].forEach (function (col) {
Looking at the data columnwise
Since we have a transposed version of the data, we can easily iterate through each row for a given column
// look at the data columnwise
transposed[col].forEach (function (value) {
Creating a list of indices
What we need now is a list of row numbers that a given value appears at. One way is to use map to create a list of row numbers where they match, and then filter to remove the ones that didn’t match.
// how many times a value appears in this column
var matches = transposed[col].map (function (d, index) {
// return the indices of matches
return value === d ? index : null;
})
.filter (function (d) {
// get rid of non-matches
return d !== null;
});
Deciding which color to use
If there is more than one match then its a duplicate. The matches array contains the list of row numbers, and the variable col contains the col number we’re currently looking at. Iterating through the matches array allows us to set the duplicate color for a cell, with the first one being set to a different color.
// if there's duplicates set the dup color, but only for the 2nd and subsequent
if (matches.length > 1 ) {
matches.forEach (function (d, i) {
colors[d][col] = i ? dupColor : firstOfDupsColor;
});
}
Finishing it off
We have two forEach anon functions to close off
}); });
All that’s left now is to write the array of colors as the backgrounds to the sheet.
// now write the updated colors sheet.getDataRange().setBackgrounds (colors);
And that’s all there is to it.