Copy a spreadsheet row to another column using app script

Question

I've been looking for an answer to this question for a while and haven't had any luck with it at all. What I need to do is copy all the cells in a given row (even blank ones are ok) to a column on a different sheet using an app script. To be able to also do a range of rows to a single column would be great too, but I'll settle for any solution. If it's not possible it would be nice to know that too.

I'm currently using a formula but I'm hitting a limit on formulas in a single spreadsheet because of the amount of data I'm trying to process.

Thanks

I've pulled this array example from a post but I don't know how to apply it to my situation?

 function transposeArray(array){
    var result = [];
    for(var row = 0; row < array.length; row++){ // Loop over rows
      for(var col = 0; col < array[row].length; col++){ // Loop over columns
        result[row][col] = array[col][row]; // Rotate
      }
    }
    return result;
}

EDIT:

I ended up just using the code below to accomplish what I was going for. It goes row by row and transposes them and then sets the values in the same column (B2 on second page) below the prior entries.

function transpose(a)
{
  return Object.keys(a[0]).map(function (c) { return a.map(function (r) { return r[c];     }); });
}

function betaUpdate(fromRange,toAnchor) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheets()[0];
  var destination = ss.getSheets()[1];

////////////////////////////////////////////////////////////////////   July 1
//Row 1
  var fromRange = source.getRange("B5:AD5");  
  var toAnchor = destination.getRange("B2");
  var data = fromRange.getValues();
  var flip = transpose(data);
  var toRange = toAnchor.offset(0, 0, flip.length, flip[0].length);
  toRange.setValues(flip);

//Row2
  var fromRange1 = source.getRange("B6:AD6");  
  var toAnchor1 = destination.getRange("B2");
  var data1 = fromRange1.getValues();
  var flip1 = transpose(data1);
///Offset must be set to the length of previous row 
  var toRange1 = toAnchor1.offset(28, 0, flip1.length, flip1[0].length);
  toRange1.setValues(flip1);

//Row3
  var fromRange2 = source.getRange("B7:AD7");  
  var toAnchor2 = destination.getRange("B2");
  var data2 = fromRange2.getValues();
  var flip2 = transpose(data2);
///Offset must be set to the length of previous row
  var toRange2 = toAnchor2.offset(56, 0, flip2.length, flip2[0].length);
  toRange2.setValues(flip2);

};

Answer 1

We're looking for a function that will allow us to do something like this:

function test_flipFlopAndFly() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheets()[0];
  var sheet2 = ss.getSheets()[1];
  var fromRange = sheet1.getDataRange();  // Everything on sheet 1
  var toAnchor = sheet2.getRange("A1");   // Top Left corner of sheet 2

  flipFlopAndFly(fromRange,toAnchor);    // <<<<<<< Make that work!
}

Following Serge's suggestion, here's a simple version of the flipFlopAndFly() utility, with no Error checking. You can see there's not much to it. BTW, I'm using the transpose() function from this answer.

/**
 * Transpose and copy data in fromRange to another range
 * whose top-left corner is at toAnchor.
 *
 * @param {Range} fromRange Range containing source data
 * @param {Range} toAnchor  Top Left corner of Range to
 *                          receive transposed data
 */
function flipFlopAndFly(fromRange,toAnchor) {
  var data = fromRange.getValues();
  var flip = transpose(data);
  var toRange = toAnchor.offset(0, 0, flip.length, flip[0].length);
  toRange.setValues(flip);
}

With error checking:

/**
 * Transpose and copy data in fromRange to another range
 * whose top-left corner is at toAnchor.
 *
 * @param {Range} fromRange Range containing source data
 * @param {Range} toAnchor  Start of Range to receive transposed data
 */
function flipFlopAndFly(fromRange,toAnchor) {
  if (arguments.length !== 2) throw new Error ("missing paramater(s)");
  try {
    // Test that arguments are both Ranges.
    fromRange.getDataTable();
    toAnchor.getDataTable();
  }
  catch (e) {
    throw new Error ("parameters must be type Range");
  }

  var data = fromRange.getValues();
  var flip = transpose(data);
  var toRange = toAnchor.offset(0, 0, flip.length, flip[0].length);
  toRange.setValues(flip);
}

Edit - create single column by appending multiple rows

There's no need to tear apart the utility function to accomplish what you were trying to do, you simply need to provide appropriate anchor points for the transposed data.

I encourage you to find ways around the use of fixed ranges, if you can - it will make your script more adaptable to changes in your sheets.

function betaUpdate(fromRange,toAnchor) {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var source = ss.getSheets()[0];
  var destination = ss.getSheets()[1];

//Row 1
  flipFlopAndFly(source.getRange("B5:AD5"),
                 destination.getRange("B2"));

//Row2
  flipFlopAndFly(source.getRange("B6:AD6"),
                 destination.getRange("B2").offset(28, 0));

//Row3
  flipFlopAndFly(source.getRange("B7:AD7"),
                 destination.getRange("B2").offset(56, 0));
};