### Creating and working with transposed sheet data arrays

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

The transpose function
` /**`
`  * transpose rows and columns`
`  * @param {*[][]} data an array of arrays of columns .. assumes its not jagged`
`  * @return {*[][]} transposed version`
`  */`
`function transpose(data) {`
`  `
`  // use the first row of data to decide on `
`  return (data[0] || []).map (function (col , colIndex) {`
`    return data.map (function (row) {`
`      return row[colIndex];`
`    });`
`  });`
`  `
`}`

The main code
function highlightDuplicatesCells() {
var sheet = ss.getSheetByName("highlight-duplicates");
var singleColor = "#ffffff";
var dupColor = " #ffeb3b";

// this one will set dup color for individual cellss that
// are duplicated in specific rows.
// only the 2nd and subsequent dup cells will be highlighted with dup colors.
// the first will get a third color
var firstOfDupsColor = "#80d8ff";

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

// start by setting all the colors to non dups
var colors = data.map (function (row) {
return row.map (function () { return singleColor; });
});

// its going to be easier to work by column, so we'll transpose the data first
var transposed = transpose (data);

// only doing for a selection of columns
[0, 2].forEach (function (col) {

// look at the data columnwise
transposed[col].forEach (function (value) {

// how many times a value appears in this column
var matches = transposed[col].map (function (d, index) {
// return the indices of matches
return value === d ?  index : null;
})
.filter (function (d) {
// get rid of non-matches
return d !== null;
});

// if there's duplicates set the dup color, but only for the 2nd and subsequent
if (matches.length > 1 ) {
matches.forEach (function (d, i) {
colors[d][col] = i ? dupColor : firstOfDupsColor;
});
}

});

});

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

}

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

`  // use the first row of data to decide on `
`  return (data[0] || []).map (function (col , colIndex) {`

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

`    return data.map (function (row) {`
`      return row[colIndex];`
`    });`

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

var sheet = ss.getSheetByName("highlight-duplicates");
var singleColor = "#ffffff";
var dupColor = " #ffeb3b";

// this one will set dup color for individual cellss that
// are duplicated in specific rows.
// only the 2nd and subsequent dup cells will be highlighted with dup colors.
// the first will get a third color
var firstOfDupsColor = "#80d8ff";

#### 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.
// get the values
var data = sheet.getDataRange().getValues();

// start by setting all the colors to non dups
var colors = data.map (function (row) {
return row.map (function () { return singleColor; });
});

#### transpose function

// its going to be easier to work by column, so we'll transpose the data first
var transposed = transpose (data);

#### 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].
// only doing for a selection of columns
[0, 2].forEach (function (col) {

#### 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
// look at the data columnwise
transposed[col].forEach (function (value) {

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

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

`      // if there's duplicates set the dup color, but only for the 2nd and subsequent`
`      if (matches.length > 1 ) {`
`        matches.forEach (function (d, i) {`
`          colors[d][col] = i ? dupColor : firstOfDupsColor;`
`        });`
`      }`

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

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

And that's all there is to it.

### `JOT_postEvent('registerForRpc', this, ['7113814310855155440', 339697457, '//2k026imuihnrho207b8ideld4uc88ppu-a-sites-opensocial.googleusercontent.com/gadgets/ifr?url\x3dhttps://storage.googleapis.com/goinggas.com/public/hosting/sites/xliberation/xml/sitesense.xml\x26container\x3denterprise\x26view\x3ddefault\x26lang\x3den\x26country\x3dALL\x26sanitize\x3d0\x26v\x3d7c2aebd66054096e\x26libs\x3dcore:dynamic-height\x26mid\x3d77\x26parent\x3dhttp://ramblings.mcpher.com/Home/excelquirks/gassnips/highlightdupcellstranspose#up_client\x3dca-pub-0073976545556837\x26up_slot\x3d5457792947\x26st\x3de%3DAIHE3cC3kAjJgUZyk4bVvs3m4wEOJ2Gb3xUlDOmujf3IeWYt6C%252Ff7v8XRB%252BZbZwVxDkQnuEXt1EqBFDWsuaTX2WkhwZtBXP%252BAX98ZZH%252Bdbq2sh4exgxSBPoStW%252BM2%252BfLe4op%252BIXMQ5or%26c%3Denterprise\x26rpctoken\x3d7113814310855155440'])JOT_postEvent('registerForRpc', this, ['7113814310855155440', 1251305501, '//2k026imuihnrho207b8ideld4uc88ppu-a-sites-opensocial.googleusercontent.com/gadgets/ifr?url\x3dhttps://storage.googleapis.com/goinggas.com/public/hosting/sites/xliberation/xml/sitesense.xml\x26container\x3denterprise\x26view\x3ddefault\x26lang\x3den\x26country\x3dALL\x26sanitize\x3d0\x26v\x3d7c2aebd66054096e\x26libs\x3dcore:dynamic-height\x26mid\x3d77\x26parent\x3dhttp://ramblings.mcpher.com/Home/excelquirks/gassnips/highlightdupcellstranspose#up_client\x3dca-pub-0073976545556837\x26up_slot\x3d5457792947\x26st\x3de%3DAIHE3cDRNqciM5tdAseJlPsT7fzNCH73Fnp5FFBhLe%252Bt3ag%252BtcNv52AYjT7h2EBpfolWE%252FSwspyDqrZNt9s4QtjGAILDtLwXV4HB6A48NXvdbeJjXakjkVF3M1dn9JSgxmRW%252FOv6bhT0%26c%3Denterprise\x26rpctoken\x3d7113814310855155440'])JOT_postEvent('registerForRpc', this, ['7113814310855155440', 1976749311, '//2k026imuihnrho207b8ideld4uc88ppu-a-sites-opensocial.googleusercontent.com/gadgets/ifr?url\x3dhttps://storage.googleapis.com/goinggas.com/public/hosting/sites/xliberation/xml/sitesense.xml\x26container\x3denterprise\x26view\x3ddefault\x26lang\x3den\x26country\x3dALL\x26sanitize\x3d0\x26v\x3d7c2aebd66054096e\x26libs\x3dcore:dynamic-height\x26mid\x3d77\x26parent\x3dhttp://ramblings.mcpher.com/Home/excelquirks/gassnips/highlightdupcellstranspose#up_client\x3dca-pub-0073976545556837\x26up_slot\x3d5457792947\x26st\x3de%3DAIHE3cCi6R8sT9iNV8%252FIILzRGlD%252FM6eUj26K%252FqG0jFhbL2PpWYu7Cldsr28rVFpFpt10s3dhmXzLS6UF%252Fngs%252B18QuIsRASxkteqtAn6aco%252FxtYfPXEiPMVVQOrJJLvLRR3paibKwQGrX%26c%3Denterprise\x26rpctoken\x3d7113814310855155440'])`

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