Highlight duplicate rows in a sheet – map, filter and every

Someone asked this question on the forum the other day, so I thought it would make a good example of how to use Array functions to simplify looking at sheet data. I normally would use A functional approach to fiddling with sheet data when working with sheet data, but this is a very simple example so we’ll just stick with array manipulation.

The problem was simply how to change the background color of rows that are duplicates – so it’s an ideal opportunity for you learn about the .map , .filter and .every function.

For an alternative approach using .reduce, see Highlight duplicate rows in a sheet – map and reduce

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

function highlightDuplicates() {
  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();

  // look through the data and get the 
  var colors = data.map (function (row, i , a) {
    
    // filter out matches for this row - assuming every column needs to match to be a dup
    var matches = a.filter (function (d) {
      return d.every(function (e,j) {
        return e === row[j];
      });
    });
    
    // should be at least 1
    if (!matches.length) throw 'failed to match target row:' + i;
    
    // if its 1, then its single, otherwise dup 
    return row.map(function (e) {
      return matches.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 an array of rows and columns.

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

The .map function

Use the .map array function to transform an array from one thing to another. Although the array “data” has the values of the sheet, the objective here is create a similar shaped array, but containing colors, which will be used as the background colors for the sheet. .map will call a function for each row in the data array and pass to it. Whatever you return from this function becomes the new value for that row – in our case it’ll be an array of colors depending on whether the row is a duplicate or not. If you use .map, you’ll end up with an array the same size as the original.

// look through the data and get the 
  var colors = data.map (function (row, i , a) {

The .filter and .every function

.filter is similar to .map, except instead of using the return value as a new value, returning true or false will determine whether or not to include a row (the original values) in the final array it creates. The anonymous function you pass to filter receives the same arguments as .map. .filter will create a new array that has a length between zero and the length of the original array. Here we’re using it to count how many times a row appears in the total data. To decide whether to accept a value or reject it (by returning true or false), we use another array function – this time .every returns a boolean value – true if every column value in the current row matches every column in other rows

// filter out matches for this row - assuming every column needs to match to be a dup
    var matches = a.filter (function (d) {
      return d.every(function (e,j) {
        return e === row[j];
      });
	  });

Creating a row of colors

The array created by the filter operation will be 1 long if no duplicates, or more if there are duplicates – so we can use another map to recreate the column values in this row as colors depending on how many matches there were

// should be at least 1
    if (!matches.length) throw 'failed to match target row:' + i;
    
    // if its 1, then its single, otherwise dup 
    return row.map(function (e) {
      return matches.length === 1 ? singleColor : dupColor;
	  }); 

Finishing it off

All that’s left now is to finish off the row loop, and 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