0
235
ViewsTranspose column to row google app script
I tried to transpose the column data into row. But this code is transposing to only one row. For example: In my code, only the data that is in the first row of Column3 Column4 Column5 Column6 of sheet 1 (I J K L) is being transferred to the first row of row1 row2 row3 row4 of sheet 2. With this, the data contained in Column1 and Column2 of sheet 1 (A B C D E) (F G H) is being transferred to the first row of row2 row5 row6 row7 row8 row9 row10 row10 row11 row12 of sheet 2. But the problem is, the data in the second row of Column3, Column4, Column5 of Sheet 1 (M N O P) is transferred to the second row of row2, row2, row3, row4 of Sheet 2, but the data in Column1 and Column2 of Sheet 1 (A B C D E) (F G H) it is not being moved to the second row of sheet2 row5 row6 row7 row8 row9 row10 row10 row11 row12. However, if you run the script a second time, the same thing is happening again. Now all I want is to move the data that is in (A B C D E) (F G H) in Column1 and Column2 of sheet 1 to the first row of row 2 row5 row6 row7 row8 row9 row10 row10 row11 row12 and the second row to the second row (M N O P).
The data contained in columns 1 and 2 of sheet 1 should be transposed to sheet 2 along with the data from columns 3 to 6 each time the script is being executed.
function transpose() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh1 = ss.getSheetByName('Sheet1');
var sh2 = ss.getSheetByName('Sheet2');
var lr = sh2.getLastRow()+1;
var gv1 = sh1.getRange('D10:G13').getValues().filter(r => r.some(e => e));
var gv2 = sh1.getRange('A2:A6').getValues();
var gv3 = sh1.getRange('B3:B5').getValues();
var result1 = [];
var result2 = [];
for(var col = 0; col < gv2[0].length; col++){
result1[col]=[];
for(var row = 0; row < gv2.length; row++){
result1[col][row] = gv2[row][col];
}
}
for(var col = 0; col < gv3[0].length; col++){
result2[col]=[];
for(var row = 0; row < gv3.length; row++){
result2[col][row] = gv3[row][col];
}
var sv1 = sh2.getRange(lr,1,gv1.length,gv1[0].length).setValues(gv1);
var sv2 = sh2.getRange(lr,5,result1.length,result1[0].length).setValues(result1);
var sv3 = sh2.getRange(lr,10,result2.length,result2[0].length).setValues(result2);
}
2 answers
Answer question0
Not sure if I understand the goal.
Do you want this?
Here is the code that does this:
function transpose() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh1 = ss.getSheetByName('Sheet1');
var sh2 = ss.getSheetByName('Sheet2');
var lr = sh2.getLastRow()+1;
var gv1 = sh1.getRange('D10:G13').getValues().filter(r => r.some(e => e));
var gv2 = sh1.getRange('A2:A6').getValues();
var gv3 = sh1.getRange('B3:B5').getValues();
var result1 = [...gv1[0], ...gv2.flat(), ...gv3.flat()];
var result2 = [...gv1[1], ...gv2.flat(), ...gv3.flat()];
var results = [result1, result2];
sh2.getRange(lr,1,results.length, results[0].length).setValues(results);
}
0
Column to Row
function coltorow() {
const ss = SpreadsheetApp.getActive();
const sh = ss.getSheetByName("Sheet Name");//add sheet name
return [sh.getRange(1,col,sh.getLastRow(),1).getValues().flat()];//edit col
}