4

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?

5

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];});
}
0

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];
      });
    })); 
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.