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[c])t[c] = [];
        t[c][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 blogtwitterG+  .

You want to learn Google Apps Script?

Learning Apps Script, (and transitioning from VBA) are covered comprehensively in my my book, Going Gas - from VBA to Apps script, available All formats are available now from O'Reilly,Amazon and all good bookshops. You can also read a preview on O'Reilly

If you prefer Video style learning I also have two courses available. also published by O'Reilly.
Google Apps Script for Developers and Google Apps Script for Beginners.



Comments