How to transpose spreadsheet data with apps script

Although there is a TRANSPOSE function in Sheets, you cant access sheets functions from within Apps Script. 

However, using Advanced Array functions it's fairly easy to transpose an array of values. 

Let's say we want to take a sheet of tabular values and transpose them. Here's how


function myFunction() {

  // get all the data in the sheet
  var ss  = SpreadsheetApp.getActiveSheet();
  var range = ss.getDataRange();
  var values = range.getValues();
  
  // clear existing
  range.clear();

  // transpose it & write it out
  ss.getRange(1,1,values[0].length,values.length)
    .setValues(Object.keys(values[0]).map ( function (columnNumber) {
      return values.map( function (row) {
        return row[columnNumber];
      });
    }));
  
}

As you would expect, this will only work on a tabular shaped set of data, where the columns are of equal length.

Walk through

In JavaScript, an array is just an object, so we can use Object.keys() to return the index number of each item, so this will return the column numbers of whatever data there is, which we can then use to select out the data for each specific column number

Object.keys(values[0]).map ( function (columnNumber) {


The value for that column number in each row is then returned to map a new row with each value for this columns
      return values.map( function (row) {
        return row[columnNumber];
      });

When we write it out, we create the range shape by transposing the old row and column lengths

 ss.getRange(1,1,values[0].length,values.length)

And that's it.


For more like this, see  Google Apps Scripts snippets. Why not join our forum,follow the blog or follow me on twitter to ensure you get updates when they are available. 




Comments