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.