I covered this topic in Highlight duplicate rows in a sheet – map, filter and every, so now we’ll build on that a little and introduce the .reduce Array function. As before the problem is simply how to change the background color of rows that are duplicates – except this time a duplicate is defined by it having the same ID value (as opposed to the entire row matching)

In this case we’ll use the .reduce function and the ID will become a property of the object that reduce will create from the sheet data.

The Code
We’ll start with the complete code, and then do a walkthrough.
Setting up

Set up the sheet id, the sheet name the colors to use for duplicate or non- duplicate rows.

The data

Read the entire data values into a an array of rows and columns.

The .reduce function

Use the .reduce array function to transform an array to both transform its contents and potentially create something other than an array. In this case we’ll be creating a single object, the properties of which will be the ID values found in the sheet. These are the arguments that reduce pass to your anonymous function

  • prev – the result you are building up from each row.
  • row – the current row
  • index – the row offset
Using the ID as a key
The prev object is the object we’ve built up so far. Let’s say that the ID is in the first column – that’ll be the property and there will be one for each unique ID. If this is the first time we’ve seen this ID then we’ll create an object with an indices arrray – this will contain a list of each row at which a particular ID was found, so we’ll push the current row’s index to that array
Closing off .reduce
Next we close off the .reduce anonymous function, which is the first argument to .reduce, but note that the second argument is the initial value of prev. Since we are creating an object, it starts as an empty object. Remember also to return prev from your function since this is work done so far.
Creating a row of colors
The data is the correct shape for an array of background colors, so we’ll use .map to generate one. We can reference the the object created by .reduce and pick up the number of times a given key occurred by examining the indices array. If more than one, then it’s a duplicate so we return an array of the duplicate color, otherwise return the single color
Finishing it off

All that’s left now is to write the array of colors as the backgrounds to the sheet.

And that’s all there is to it.


For more like this see Google Apps Scripts Snippets
For help and more information join our forum,follow the blog or follow me on twitter .