我们正在寻找一个允许我们执行以下操作的函数:
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()
函数来自这个答案 https://stackoverflow.com/a/16705104/1677912.
/**
* 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);
}
编辑 - 通过附加多行创建单列
无需分解实用函数来完成您想要做的事情,您只需为转置数据提供适当的锚点即可。
如果可以的话,我鼓励您找到使用固定范围的方法 - 这将使您的脚本更适应工作表中的更改。
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));
};