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
-
there's a function called transpose you can use: webapps.stackexchange.com/questions/87824/…– SunCommented Jun 28, 2016 at 16:03
-
can I call that in a script? I am aware of the function but I want to call it as part of the script that copies the data.– JSwordyCommented Jun 28, 2016 at 16:10
-
igoogledrive.blogspot.com/2013/07/…– SunCommented Jun 28, 2016 at 16:11
-
I had tried running that script, it said "TypeError: Cannot read property "0" from undefined. (line 2, file "Transpose")" How can I make this work?– JSwordyCommented Jun 28, 2016 at 16:16
-
I noticed that error too. Maybe this link will be helpful. It mentions arrays, but maybe this can help: stackoverflow.com/questions/16621470/…– SunCommented Jun 28, 2016 at 16:29
Add a comment
|
3 Answers
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];});
}
-
Note that these don't work for transposing matrices. That requires more advanced functions.– MagneCommented Apr 15, 2020 at 18:38
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];
});
}));
}
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).
