Highlight duplicate rows in a sheet - map and reduce

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.

function highlightDuplicatesID() {
  var ss = SpreadsheetApp.openById('1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o');
  var sheet = ss.getSheetByName("highlight-duplicates");
  var singleColor = "#ffffff";
  var dupColor = " #ffeb3b";
  
  // get the values
  var data = sheet.getDataRange().getValues();

  // In this case, the ID is in column 0 , and we want to highlight duplicate IDS
  
  // look through the data and get the 
  var counts = data.reduce (function (prev , row , index) {
    
    // if we've seen this one already then its a dup
    // use the ID as a property
    var key = row[0].toString();
    prev[key] = prev[key] || {indices:[]};
    
    // increment the rows we've seen it on
    prev[key].indices.push (index);
    return prev;
    
  }, {});
  
  // now we have an object organized by ID, with a property of how many times it was spotted
  var colors = data.map (function (row) {
    var key = row[0].toString();
    return row.map(function (e) {
      return counts[key].indices.length === 1 ? singleColor : dupColor;
    });  
  });

  // now write the updated colors
  sheet.getDataRange().setBackgrounds (colors);
  
}


Setting up


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

  var ss = SpreadsheetApp.openById('1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o');
  var sheet = ss.getSheetByName("highlight-duplicates");
  var singleColor = "#ffffff";
  var dupColor = " #ffeb3b";

The data


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

  // get the values
  var data = sheet.getDataRange().getValues();

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

  // In this case, the ID is in column 0 , and we want to highlight duplicate IDS
  
  // look through the data and get the 
  var counts = data.reduce (function (prev , row , index) {

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

    // if we've seen this one already then its a dup
    // use the ID as a property
    var key = row[0].toString();
    prev[key] = prev[key] || {indices:[]};
    
    // increment the rows we've seen it on
    prev[key].indices.push (index);


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.
    return prev;
    
  }, {});

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

  // now we have an object organized by ID, with a property of how many times it was spotted
  var colors = data.map (function (row) {
    var key = row[0].toString();
    return row.map(function (e) {
      return counts[key].indices.length === 1 ? singleColor : dupColor;
    });  
  });

Finishing it 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.





For help and more information join our forum,follow the blog or follow me on twitter .

Comments