2

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);

};
ryano
  • 231
  • 1
  • 5
  • 18
  • data taken from spreadsheets are 2D arrays, what you need is a transpose function that will "convert" rows to columns (and reversely) You'll easily find posts on that subject, [this one for example](http://stackoverflow.com/questions/16621470/google-spreadsheet-script-how-to-transpose-rotate-multi-dimensional-array) – Serge insas Jun 07 '13 at 18:59
  • @Sergeinsas I don't have a lot of experience with arrays at all. Any help would be appreciated. – ryano Jun 07 '13 at 22:33

1 Answers1

2

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));
};
Community
  • 1
  • 1
Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • Thanks for the reply. I got it to work but I have one more question. Is there an easy way to change the transpose function so that all the rows in sheet 1 would be put into a single column on sheet 2? Right now when the function is executed it just transposes each individual row into it's own column on sheet 2. – ryano Jun 08 '13 at 17:46
  • Depends on what you mean by "put all together". A spreadsheet MERGE will only preserve the value of the first cell, for instance. If you want to treat all cells in a row as strings, and create a comma-delimited list, you could 'join()' the row array. – Mogsdad Jun 08 '13 at 21:35
  • I edited my question to include the code that I ended up using. You can see what I was trying to accomplish from that. If that process could be shortened or sped up that would be great. – ryano Jun 09 '13 at 20:31
  • Shortened, yes - see edited answer. As for speed - if you need to speed this up, follow the guidance in [Best Practices](https://developers.google.com/apps-script/best_practices). First though, get enough javascript tutelage so you're comfortable with the logic, arrays, objects, and String object, then spend some time with the SpreadsheetApp API so you know what methods are available. A few days' investment should get you to the point that you can read and understand the code in this answer, after which you should be able to customize it to your exact needs. – Mogsdad Jun 10 '13 at 13:24
  • Cool. Thanks for the tip it's a bit easier to read now which is a plus. I already knew this kind of thing was going to slow it down but as long as it works I don't have a huge problem with that. – ryano Jun 10 '13 at 16:40