How to transpose spreadsheet data with apps script

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