Transposing sheet data

You all know that when you use getValues(), you’ll get an array of rows of data, each element of which is an array of columns. There are times that you’d rather have it the other way round. An array of columns of data, where each element is a row. Here’s a simple transpose function to do that.

Transpose function

Here’s the code for the function

// turn data columnwise
  function transpose(data) {
    var t = [];
    data.forEach(function(row,r) {
      row.forEach(function(column,c) {
        if (!t)t = [];
        t[r] = column;
      })
    });
    return t;
  }

Test

Quick test to transpose the currently selected range

var before =SpreadsheetApp.getActiveRange().getValues();
  
  var after = transpose(before);
  
  Logger.log(JSON.stringify(before));
  
  Logger.log(JSON.stringify(after));

Result

[15-07-06 18:28:36:240 CEST] [[“CHP”,”Electricity”,58],[“CHP”,”Losses”,60],[“Coal”,”CHP”,61],[“Coal”,”Other”,64],[“Coal”,”Power”,153],[“Crude oil”,”Export”,58]]
[15-07-06 18:28:36:241 CEST] [[“CHP”,”CHP”,”Coal”,”Coal”,”Coal”,”Crude oil”],[“Electricity”,”Losses”,”CHP”,”Other”,”Power”,”Export”],[58,60,61,64,153,58]]

Another way

Because the keys of an array are just its numeric position, and because an array is just a special kind of object, we can improve on this, assuming that our data is a regular shaped table.

function transpose2(data) {
    return Object.keys(data.length ? data[0] : [] ).map(function(columnIndex) {
      return data.map(function (row) {
        return row[columnIndex];
      });
    });
	}

Quick test to transpose the currently selected range

var after = transpose2(before);
    Logger.log(JSON.stringify(after));

Result

[[“CHP”,”CHP”,”Coal”,”Coal”,”Coal”,”Crude oil”],[“Electricity”,”Losses”,”CHP”,”Other”,”Power”,”Export”],[58,60,61,64,153,58]]

For more like this, see Google Apps Scripts snippets. Why not join our cummunity , follow the blog, twitter, G+ .