编辑：我更改了代码以包括按名称提供范围的可能性(以 A1 表示法(，因为这可能比提供Range对象更有效(如果范围最终没有移动(，并且肯定在简单情况下更容易使用。亚当L的想法(见下面的答案(。
在某些电子表格中，我需要排列行或列。要求用户手动执行此操作并不是很好。因此，在运行脚本的菜单中制作适当的命令似乎是一个合理的解决方案。
奇怪的是，我找不到任何可以排列行/列的功能(内置或由其他人编写(。所以我自己写了一个，然后考虑出版它。但是由于我对JavaScript和Google Apps Script的经验很少，我想让其他人检查这个功能。我也有一些问题。所以我们开始了。
// Parameters:
// - ranges An Array with ranges which contents are to be permuted.
// All the ranges must have the same size. They do not have to be
// vectors (rows or columns) and can be of any size. They may come from
// different sheets.
// Every element of the array must be either a Range object or a string
// naming the range in A1 notation (with or without sheet name).
// - permutation An Array with 0-based indexes determining desired permutation
// of the ranges. i-th element of this array says to which range
// should the contents of i-th range be moved.
// - temp A range of the same size as the ranges in "ranges". It is used to
// temporarily store some ranges while permuting them. Thus the initial
// contents of this range will be overwritten and its contents on exit is
// unspecified. Yet if there is nothing to be moved ("ranges" has less
// than 2 elements or all ranges are already on their proper places) this
// range will not be used at all.
// It is advised to make this range hidden so the "garbage" doesn't
// bother user.
// This can be either a Range object or a string naming the range in A1
// notation (with or without sheet name) - just as with the "ranges".
// - sheet An optional Sheet object used to resolve range names without sheet
// name. If none is provided active sheet is used. Note however that it
// may cause issues if user changes the active sheet while the script is
// running. Thus if you specify ranges by name without sheet names you
// should provide this argument.
//
// Return Value:
// None.
//
// This function aims at minimizing moves of the ranges. It does at most n+m
// moves where n is the number of permuted ranges while m is the number of
// cycles within the permutation. For n > 0 m is at least 1 and at most n. Yet
// trivial 1-element cycles are handled without any moving (as there is nothing
// to be moved) so m is at most floor(n/2).
//
// For example to shift columns A, B and C by 1 in a cycle (with a temp in
// column D) do following:
//
// permuteRanges(
// ["A1:A", "B1:B", "C1:C"],
// [1, 2, 0],
// "D1:D",
// SpreadsheetApp.getActiveSheet()
// );
function permuteRanges(ranges, permutation, temp, sheet) {
// indexes[i] says which range (index of ranges element) should be moved to
// i-th position.
var indexes = new Array(permutation.length);
for(var i = 0; i < permutation.length; ++i)
indexes[permutation[i]] = i;
// Generating the above array is linear in time and requires creation of a
// separate array.
// Yet this allows us to save on moving ranges by moving most of them to their
// final location with only one operation. (We need only one additional move
// to a temporary location per each non-trivial cycle.)
// Range extraction infrastructure.
// This is used to store reference sheet once it will be needed (if it will be
// needed). The reference sheet is used to resolve ranges provided by string
// rather than by Range object.
var realSheet;
// This is used to store Range objects extracted from "ranges" on
// corresponding indexes. It is also used to store Range object corresponding
// to "temp" (on string index named "temp").
var realRanges;
// Auxiliary function which for given index obtains a Range object
// corresponding to ranges[index] (or to temp if index is "temp").
// This allows us to be more flexible with what can be provided as a range. So
// we accept both direct Range objects and strings which are interpreted as
// range names in A1 notation (for the Sheet.getRange function).
function getRealRange(index) {
// If realRanges wasn't yet created (this must be the first call to this
// function then) create it.
if(!realRanges) {
realRanges = new Array(ranges.length);
}
// If we haven't yet obtained the Range do it now.
if(!realRanges[index]) {
var range;
// Obtain provided range depending on whether index is "temp" or an index.
var providedRange;
if(index === "temp") {
providedRange = temp;
} else {
providedRange = ranges[index];
}
// If corresponding "ranges" element is a string we have to obtain the
// range from a Sheet...
if(typeof providedRange === "string") {
// ...so we have to first get the Sheet itself...
if(!realSheet) {
// ...if none was provided by the caller get currently active one. Yet
// note that we do this only once.
if(!sheet) {
realSheet = SpreadsheetApp.getActiveSheet();
} else {
realSheet = sheet;
}
}
range = realSheet.getRange(providedRange);
} else {
// But if the corresponding "ranges" element is not a string then assume
// it is a Range object and use it directly.
range = providedRange;
}
// Store the Range for future use. Each range is used twice (first as a
// source and then as a target) except the temp range which is used twice
// per cycle.
realRanges[index] = range;
}
// We already have the expected Range so just return it.
return realRanges[index];
}
// Now finally move the ranges.
for(var i = 0; i < ranges.length; ++i) {
// If the range is already on its place (because it was from the start or we
// already moved it in some previous cycle) then don't do anything.
// Checking this should save us a lot trouble since after all we are moving
// ranges in a spreadsheet, not just swapping integers.
if(indexes[i] == i) {
continue;
}
// Now we will deal with (non-trivial) cycle of which the first element is
// i-th. We will move the i-th range to temp. Then we will move the range
// which must go on the (now empty) i-th position. And iterate the process
// until we reach end of the cycle by getting to position on which the i-th
// range (now in temp) should be moved.
// Each time we move a range we mark it in indexes (by writing n on n-th
// index) so that if the outer for loop reaches that index it will not do
// anything more with it.
getRealRange(i).moveTo(getRealRange("temp"));
var j = i;
while(indexes[j] != i) {
getRealRange(indexes[j]).moveTo(getRealRange(j));
// Swap index[j] and j itself.
var old = indexes[j];
indexes[j] = j;
j = old;
}
getRealRange("temp").moveTo(getRealRange(j));
// No need to swap since j will not be used anymore. Just write to indexes.
indexes[j] = j;
}
}
问题是：
这是否得到正确实施？可以改进吗？
参数验证怎么样？我应该这样做吗？如果它们无效，我该怎么办？
我不确定是使用
copyTo还是moveTo.我决定moveTo因为在我看来，我更想做什么。但现在再三考虑，我认为也许copyTo会更有效率。我还注意到，从并不总是清除的
Range。尤其是在调试器中时。撤消/重做似乎是此功能的问题。似乎每个
moveTo都是对电子表格的单独操作(甚至更糟，但也许这只是我测试时 Google Docs 的低响应性(，撤消排列不是单个操作。有什么办法吗？我为该函数编写的文档声称它适用于不同的工作表甚至不同的电子表格。我实际上还没有检查过;)但Google Apps Script文档似乎并不否认这一点。它会这样工作吗？
我不确定这是否是提出此类问题的合适地方(因为这不是一个真正的问题(，但由于Google Apps Script社区支持正在转移到Stack Overflow，我不知道还能问哪里。
你不认为使用数组在执行速度方面可能更有效吗？
例如尝试这个：(我在任何地方添加了日志以显示发生了什么((另请注意，工作表限制为 255 列...注意列表长度(
function permutation() {
var sh = SpreadsheetApp.getActiveSheet();
var ss = SpreadsheetApp.getActiveSpreadsheet();
var lr = ss.getLastRow()
var lc=ss.getLastColumn();
var data = sh.getRange(1,1,lr,lc).getValues()
Logger.log(data)
var temp2= new Array();
var h=data.length
Logger.log(h)
var w=data[0].length
Logger.log(w)
for(nn=0;nn<w;++nn){
var temp1= new Array();
for (tt=0;tt<h;++tt){
temp1.push(data[tt][nn])
}
temp2.push(temp1)
}
Logger.log(temp2)
Logger.log(temp2.length)
Logger.log(temp2[0].length)
sh.getRange(1,1,lr,lc).clear()
sh.getRange(1,1,lc,lr).setValues(temp2)
}
此致敬意哔叽
Adam，从我在 Apps Script GPF 上的有限经验中，我了解到最好尽可能限制 get 和 set 调用(你也可以在其中包括 moveTo/copyTo(。
您是否认为将范围名称而不是范围作为参数传递会更好(为此，您可能还需要一种机制来传递工作表名称和电子表格键，以支持您跨不同工作表/电子表格工作的要求(，然后可以避免琐碎的"getRange"以及琐碎的"moveTo"。
此外，如果您只是传输值，最好不要将其移动到临时范围，而是将这些数组分配给脚本中的变量，然后可以在正确的位置"设置"该变量。 但是，如果您需要复制格式或公式，那就另当别论了。