CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
David Posas Leo
David Posas Leo

Reputation: 3

Copy values from column and transpose into next blank row

I want to copy values from an specific column and then paste them in transpose in the first the columns, so, If a enter 3 values vertically, paste them horizontaly into the next available row.

Example

I've used the appendRow function but paste the vales under the source values in not after the destination header.

After use this code, I get this:, but doesn't paste the values after first row (header)

Results

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

  sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("test");
  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,4,2,lastCol);
    //Logger.log(rowrange);

    rowValues = rowrange.getValues();//
    rowValues = rowValues[1];//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

Views: 366

Answers (1)

Jonas B&#230;k
Jonas B&#230;k

Reputation: 100

I'm not entirely certain what you wish to accomplish here.

If more than three values are input in your example, would you want the 4th, 5th and 6th values to be on a second row? In other words, are you looking to paste specifically 3 values per row?

Edit:

This function should do the trick. It isn't the prettiest of code, but should get the job done. There's plenty of other ways to create the object, but this was how it came naturally to me as I wrote it :)

function myFunction() {
  //Get values of all nonEmpty cells
  var ss = SpreadsheetApp.getActiveSheet();
  var values = ss.getRange("D:D").getValues().filter(String);

  //Create object with 3 columns max
  var pasteValues = [];
  var row = ["","",""];
  for (i = 1; i<values.length+1; i++){
    row.splice((i%3)-1,1,values[i-1]);
    if(i%3 == 0){
      pasteValues.push(row);
      var row = ["","",""]
    }
  }
  if(row != []){
    pasteValues.push(row)
  }

  //Paste the object in columns A to C
  ss.getRange(1,1,pasteValues.length,pasteValues[0].length).setValues(pasteValues);

}

Upvotes: 1

Related Questions