Data from sheets is organized as an array of rows of arrays of column values. Sometimes that’s not convenient and prevents you from effectively using the useful mapping array methods that I covered in Highlight duplicate rows in a sheet – map, filter and every and Highlight duplicate rows in a sheet – map and reduce. If you are new to those you can take a look at those tutorials to get grounded. In this tutorial I’ll show you how to build a transpose function to turn the usual row-wise sheet data into a column-wise arrangement (and visa versa).

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 main code

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

Next, for each of the column values we just iterate through the rows and create a map of that shape.

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

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.

transpose function

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

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

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.

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.

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.

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 .