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

We'll start with the complete code, and then do a walkthrough.

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 ss = SpreadsheetApp.openById('1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o');
  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 ss = SpreadsheetApp.openById('1181bwZspoKoP98o4KuzO0S11IsvE59qCwiw4la9kL4o');
  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.

      
      // 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;
      });

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.

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






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

Comments