CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
G.T.
G.T.

Reputation: 15

Transpose columns into rows through loop

I'm trying to transpose (copy column content into a row) a range from one sheet to another. Yet the catch is that each row has a different number of columns populated, and I don't want to have blank columns copied over into the output sheet.

So I was trying to do the following:

  1. Go to 'import manipulation' tab
  2. row by row (in a loop), check what the last POPULATED column is
  3. Then col by col (second loop), copy each cell's value then paste it onto "Transpose" sheet in the first available row
  4. Keep doing this until the last Populated column was copied over, and then move onto the next row
  5. Repeat

I've ran the debugger and nothing comes up, however when I run the function it doesn't paste anything into the "Test" sheet. Any suggestions?

function transpose(sh) {

var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Import Manipulation");
var lastrow = sh.getLastRow(); 
var numrows = lastrow;

for(var i= 1; i<= numrows; i++) {
Logger.log(i);
var rowrange = sh.getRange(i, 50);
Logger.log(rowrange);
var lastcol = rowrange.getValues().length;
for(var j=5; j<= lastcol; j++) {

var colrange = sh.getRange(i,j);
var copycell = colrange.getValue();


var pastesheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");
var pasterow = pastesheet.getLastRow();
Logger.log(pasterow);

var pastecell = pastesheet.getRange(pasterow + 1, 1);
Logger.log(pastecell);

var doit = pastecell.setValue(copycell);

doit(pastecell);  

   } 
 }
}

Upvotes: 0

Views: 324

Answers (2)

Alan Wells
Alan Wells

Reputation: 31300

This line of code:

var rowrange = sh.getRange(i, 50);

Is only getting one cell. On the first loop, it gets the cell in row 1 column 50.

The variable lastcol in this line:

var lastcol = rowrange.getValues().length;

will always be 1. getValues() gets a 2D array and in this case it will be [[cellValue]]

I created some new code:

function transpose(sh) {
  var arrayThisRow,cellValue,i,j,lastCol,lastrow,numrows,pastesheet,
      rowrange,rowValues,sh;

  sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Import Manipulation");
  pastesheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Test");

  lastrow = sh.getLastRow();
  lastCol = sh.getLastColumn();
  Logger.log('lastCol: ' + lastCol);

  numrows = lastrow;
  arrayThisRow = [];

  for(i=1; i<= numrows; i++) {
    //Logger.log(i);
    rowrange = sh.getRange(i,1,1,lastCol);
    //Logger.log(rowrange);

    rowValues = rowrange.getValues();//
    rowValues = rowValues[0];//There is only 1 inner array

    for(j=0; j <= lastCol; j++) {

      cellValue = rowValues[j];

      if (cellValue) {
        arrayThisRow.push(cellValue);
      } else {
        break;//Stop pushing values into the array as soon as a blank cell is found
      }
    }
  }

  Logger.log('arrayThisRow: ' + arrayThisRow)
  if (arrayThisRow.length > 0) {
    pastesheet.appendRow(arrayThisRow)
  }
}

Upvotes: 0

G.T.
G.T.

Reputation: 15

I was able to do it so that it transposes, and it's working (albeit probably not the most efficient).... appreciate suggestions for how to make it more efficient!

function transpose(sh) {

  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Import Manipulation");
  var lastrow = sh.getLastRow(); 
  var numrows = lastrow;
  var lastcol = sh.getLastColumn();

for(var i= 1; i<= numrows; i++) {
  Logger.log(i);
  var rowrange = sh.getRange(i,1,1,lastcol);
  Logger.log(rowrange);
  for(var j=5; j<= lastcol; j++) {

     var colrange = sh.getRange(i,j);
     var copycell = colrange.getValue();


     var pastesheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Transpose");
     var pasterow = pastesheet.getLastRow();
      Logger.log(pasterow);

      var pastecell = pastesheet.getRange(pasterow + 1, 1);
          Logger.log(pastecell);

          pastecell.setValue(copycell);



   } 
 }
}

Upvotes: 1

Related Questions