Script to permute columns, rows or any ranges

Question

EDIT: I changed the code to include possibility of providing ranges by name (in A1 notation) as this could be potentially more efficient than providing Range object (if the range ends up not moved) and for sure is easier to use in simple cases. Idea by AdamL (see answers bellow).

---

In some spreadsheets I need to permute rows or columns. Requiring user to do this manually isn't very nice. So making proper commands in menu which would run script seemed a reasonable solution.

Oddly I wasn't able to find any function (either build in or wrote by someone else) which would permute rows/columns. So I wrote one myself and then considered publishing it. But since my experience with JavaScript and Google Apps Script is low I wanted to have someone else check on this function. Also I have some questions. So here we go.

---

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

---

The questions are:

1. Is this properly implemented? Can it be improved?

2. How about parameters validation? Should I do it? What should I do if they are invalid?

3. I wasn't sure whether to use copyTo or moveTo. I decided on moveTo as it seemed to me more what I intended to do. But now in second thoughts I think that maybe copyTo would be more efficient.

4. Also I noticed that the Range moved from not always is cleared. Especially when in Debugger.

5. Undo/redo seems to be an issue with this function. It seems that every moveTo is a separate operation (or even worse, but maybe that was just a low responsiveness of the Google Docs when I was testing) on the spreadsheet and undoing the permutation is not a single action. Can anything be done about it?

6. The documentation I wrote for the function claims that it works across different sheets or even different spreadsheets. I haven't actually checked that ;) but Google Apps Script documentation doesn't seem to deny it. Will it work that way?

---

I'm not sure whether this is a proper place to ask such questions (since this is not truly a question) but since Google Apps Script community support is moving to Stack Overflow I didn't knew where else to ask.

Answer 1

Don't you think it might be more efficient in terms of execution speed to do it with arrays ?

try this for example : (I added logs everywhere to show what happens) (Note also that sheets are limited to 255 columns... take care of the list length)

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

best regards, Serge

Answer 2

Adam, from my limited experience on the Apps Script GPF, I have learned that it is best to limit get and set calls as much as possible (and you could include moveTo/copyTo in that as well).

Do you think it would be better to pass the range names, rather than the ranges, as parameters (and to that end, you might need a mechanism to pass sheet names and spreadsheet keys as well, to support your requirement of working across different sheets/spreadsheets), and then trivial "getRange's" can be avoided as well as a trivial "moveTo's".

Also, if you are just transferring values only, it would probably be better to not move it to a temporary range but rather assign those arrays to a variable in the script which can then be later "set" in the correct spot. But if you need to copy over formats or formulae, that's a different story.