使用应用程序脚本将电子表格行复制到另一列 [英] Copy a spreadsheet row to another column using app script

问题描述

一段时间以来，我一直在寻找这个问题的答案，一点也没有运气.我需要做的是使用应用程序脚本将给定行中的所有单元格(即使是空白单元格也可以)复制到另一张纸上的列中.能够对单个列也进行一定范围的行也是很好的，但是我会选择任何解决方案.如果不可能的话，也很高兴知道这一点.

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.

谢谢

我已经从帖子中提取了这个数组示例，但是我不知道如何将其应用于我的情况?

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

我最终只是使用下面的代码来完成我想要的工作.它逐行进行转置，然后在先前条目下方的同一列(第二页B2)中设置值.

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

};

推荐答案

我们正在寻找一种可以使我们执行以下操作的函数:

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!
}

按照Serge的建议，这是flipFlopAndFly()实用程序的简单版本，没有错误检查.您可以看到没有太多东西.顺便说一句，我正在使用此答案中的transpose()函数.

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

带有错误检查:

/**
 * 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));
};

这篇关于使用应用程序脚本将电子表格行复制到另一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！