Transposing Column to Row

Question

I am trying to write a script that will transpose a column from one sheet into a row on another sheet. I have already figured out how to do the copy, but I need to figure out how to change the data from vertical to horizontal on the other sheet. Can anyone help me?

Answer 1

A column of values is represented in Apps Script as [['a'], ['b'], ['c']]. A row is represented as [['a', 'b', 'c']]. The following functions transform one to the other:

function col2row(column) {
  return [column.map(function(row) {return row[0];})];
} 

function row2col(row) {
  return row[0].map(function(elem) {return [elem];});
}

Answer 2

This was helpful for me:

var ss = SpreadsheetApp.getActiveSpreadsheet();
var originSheet = ss.getSheetByName("main");
var destinationSheet = ss.getSheetByName("weeks");
var destinationRowNum = 1;
var destinationColNum = 1;

colToRow(originSheet.getRange("labelsCol").getValues(), destinationSheet, destinationRowNum, destinationColNum);
colToRow(originSheet.getRange("mainCol").getValues(), destinationSheet, destinationRowNum +1, destinationColNum);

function colToRow(values, destinationSheet, destinationRowNum, destinationColNum) {//Inspired by http://ramblings.mcpher.com/Home/excelquirks/gassnips/transpose
  // Transpose it & write it out
  destinationSheet.getRange(destinationRowNum, destinationColNum, values[0].length, values.length)
    .setValues(Object.keys(values[0]).map ( function (columnNumber) {
      return values.map( function (row) {
        return row[columnNumber];
      });
    })); 
}

Answer 3

This will allow you to have one single function which converts row->column and column->row, and also works on 2D matrices (called transposing):

function transposeMatrix(array) {
  return array[0].map((col, i) => array.map(row => row[i]));
}

This is basically restating @Fahwad Ghafoor's excellent answer from here: https://stackoverflow.com/a/17428705/380607

Note: There is even a built-in formula called TRANSPOSE, which you can use inside the spreadsheet if you only need it there (and not need to access it in your code). Use that formula in a cell like this =TRANSPOSE(A1:B2).