javascript - Google 电子表格中的排列/组合

Question

我的目标是找到一组范围的所有排列。

我在使用以下代码时遇到问题(来自:Script to permute columns, rows or any ranges)。我在单元格 E1 的示例中使用了以下函数，但无济于事:

=permuteRanges(["A1:A", "B1:B", "C1:C"],[1, 2, 0],"D1:D",SpreadsheetApp.getActiveSheet())

例子:

a1,b1,c1
a2,b2,c2

期望的输出:

a1,b1,c1
a1,b1,c2
a1,b2,c1
a1,b2,c2
a2,b1,c1
a2,b1,c2
a2,b2,c1
a2,b2,c2

我希望能够更改 a、b、c 列，以便函数可以执行 b、a、c 或 c、a、b。请注意，我需要能够更改列，而不是我明确需要上面使用的 b、a、c 或 c、a、b。

错误是#ERROR 公式解析错误。问题可能出在公式格式上吗？

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

Answer 1

I've used the following function used in the example in cell E1 but to no avail:

=permuteRanges(["A1:A", "B1:B", "C1:C"],[1, 2, 0],"D1:D",
SpreadsheetApp.getActiveSheet())

上面的问题是 permuteRanges 不能用作自定义函数，它应该从另一个函数而不是从公式中调用。