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?

6

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];});
}
| improve this answer | |
  • Note that these don't work for transposing matrices. That requires more advanced functions. – Magne Apr 15 at 18:38
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];
      });
    })); 
}
| improve this answer | |
0

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).

| improve this answer | |

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.