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.

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

  • the row – an array of values for each column
  • the row offset starting at zero
  • the entire data array for convenience (its the same as using data itself)

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

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

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.

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