用于排列列，行或任何范围的脚本 [英] Script to permute columns, rows or any ranges
问题描述
Range 对象（如果范围最终没有移动），并且确实在简单情况下更容易使用。 Idea by AdamL（请参阅下面的答案）。
在某些电子表格中，我需要排列行或列。要求用户手动执行此操作并不是很好。所以在运行脚本的菜单中做出正确的命令似乎是一个合理的解决方案。
奇怪的是，我无法找到任何函数（无论是内部编写还是其他人编写）这将排列行/列。所以我自己写了一个，然后考虑发布它。但是由于我对JavaScript和Google Apps脚本的使用经验很低，我想让其他人检查这个功能。我也有一些问题。
//参数：
// - 范围具有要置换内容的范围的数组。
//所有的范围必须具有相同的大小。它们不必是
//向量（行或列），并且可以具有任何大小。它们可能来自
//不同的纸张。
//数组中的每个元素都必须是一个Range对象或一个字符串
//以A1表示法（带或不带表名）命名该范围。
// - 置换具有基于0的索引的数组，用于确定期望的置换
//范围。这个数组的第i个元素说明哪个范围
//应该移动第i个范围的内容。
// - temp与范围中的范围具有相同大小的范围。它用于
//在排列它们时临时存储一些范围。因此，该范围的初始
//内容将被覆盖，其退出内容为
//未指定。然而，如果没有任何东西可以移动（范围少于
//比2个元素更少，或者所有范围都已经放在适当的位置），则
//范围根本不会被使用。
//建议隐藏这个范围，这样垃圾不会
//打扰用户。
//这可以是Range对象，也可以是命名A1
//表示法（带或不带表格名称）范围的字符串 - 就像范围一样。
// - 工作表一个可选的表格对象，用于解析范围名称而不用工作表
//名称。如果没有提供活动纸张被使用。但请注意，如果用户在脚本为
//运行时更改活动工作表，它
//可能会导致问题。因此，如果您通过没有表名的名称来指定范围，那么
//应该提供此参数。
//
//返回值：
//无。
//
//此功能旨在最小化范围的移动。它最多可以做n + m
//移动，其中n是置换范围的数量，而m是置换中
//周期的数量。对于n> 0 m至少为1，最多为n。然而，
//不重要的1个单元周期在没有任何移动的情况下被处理（因为没有任何
//被移动），所以m最多是楼层（n / 2）。
//
//例如，要在一个循环中将列A，B和C移动1（在
//列D中有一个临时变量），请执行以下操作：
//
// permuteRanges（
// [A1：A，B1：B，C1：C]，
// [1,2,0]，
//D1：D，
// SpreadsheetApp.getActiveSheet（）
//）;
函数permuteRanges（范围，排列，温度，表单）{
// indices [i]表示哪个范围（范围元素的索引）应该移动到
//第i个位置。
var indexes = new Array（permutation.length）;
for（var i = 0; i< permutation.length; ++ i）
indexes [permutation [i]] = i;
//生成上面的数组在时间上是线性的，需要创建一个
//单独的数组。
//然而，这可以让我们节省移动范围，只需一次操作即可将大部分移动到
//最终位置。 （我们只需要一个额外的移动
//到每个非平凡周期的临时位置。）
//范围提取基础结构。
//这将用于存储参考表一旦需要（如果它将是
//需要）。参考表用于解析由字符串
//而不是Range对象提供的范围。
var realSheet;
//这用于存储从
//相应索引的范围中提取的Range对象。它还用于将Range对象
//保存到temp（在名为temp的字符串索引上）。
var realRanges;
//给定索引获得Range对象
//对应于范围[index]的辅助函数（如果index为temp，则返回temp）。
//这让我们可以更灵活地提供范围内的任何东西。所以
//我们接受直接的Range对象和字符串，它们被解释为
//以A1表示法（用于Sheet.getRange函数）的范围名称。
函数getRealRange（index）{
//如果realRanges尚未创建（这必须是对此
//函数的第一次调用）然后创建它。
if（！realRanges）{
realRanges = new Array（ranges.length）;
}
//如果我们还没有获得Range，现在就做。
if（！realRanges [index]）{
var range;
//根据索引是temp还是索引获取提供的范围。
var providedRange;
if（index ===temp）{
providedRange = temp;
} else {
providedRange = ranges [index];
}
//如果相应的ranges元素是一个字符串，我们必须从Sheet获得
//范围...
if（typeof providedRange ===string）{
// ...所以我们必须先得到Sheet本身...
if（！realSheet）{
// ...如果没有由调用者提供，获得当前活动的一个。然而，
//注意我们只做一次。
if（！sheet）{
realSheet = SpreadsheetApp.getActiveSheet（）;
} else {
realSheet = sheet;
}
}
range = realSheet.getRange（providedRange）;
} else {
//但是，如果相应的ranges元素不是字符串，则假定
//它是一个Range对象并直接使用它。
range = providedRange;
}
//存储范围以供将来使用。每个范围使用两次（首先作为
//源，然后作为目标），除了每个循环使用两次
//的临时范围。
realRanges [index] = range;
}
//我们已经有了预期的范围，只需返回即可。
return realRanges [index];
}
//现在最后移动范围。
for（var i = 0; i< ranges.length; ++ i）{
//如果范围已经在它的位置上（因为它是从一开始或我们
//在之前的某个循环中已经移动了它），那么不要做任何事情。
//检查这应该会为我们节省很多麻烦，毕竟我们在电子表格中移动
//范围，而不仅仅是交换整数。
if（indexes [i] == i）{
continue;
}
//现在我们将处理其中第一个元素是
// i-th的（非平凡）循环。我们将把第i个范围移到temp。然后，我们将移动范围
//，该范围必须位于（现在为空）的第i个位置。并且迭代进程
//直到我们到达循环结束时，通过获取第i个
//范围（现在在temp中）应该移动的位置。
//每当我们移动一个范围时，我们都会在索引中标记它（通过在第n个
//索引中写入n），这样如果outer for循环达到该索引，它将不会执行$ b $ / / / /更多的东西。
getRealRange（i）.moveTo（getRealRange（temp））;
var j = i;
while（indices [j]！= i）{
getRealRange（indices [j]）。moveTo（getRealRange（j））;
//交换指数[j]和j本身。
var old = indexes [j];
个索引[j] = j;
j =旧;
}
getRealRange（temp）。moveTo（getRealRange（j））;
//不需要交换，因为j不会再被使用。只需写入索引。
个索引[j] = j;
$ / code $ / pre
$ hr
问题是：
-
这是否正确实施？可以改进吗？
参数验证如何？我应该这样做吗？如果它们无效，我应该怎么做？ 我不确定是否使用 copyTo 或的moveTo 。我决定 moveTo ，因为它对我来说更像我打算做的事情。但现在我想第二个想法可能是 copyTo 会更有效率。 我还注意到移动的范围不总是被清除。特别是在Debugger中。
-
撤销/重做似乎是该功能的一个问题。似乎每个 moveTo 在电子表格中都是一个单独的操作（或者更糟糕，但这可能只是Google Docs在我测试时的低响应）排列不是一个单一的行为。
-
我为这个函数编写的文档声称它可以在不同的工作表或甚至不同的电子表格中使用 。我没有真正检查过）;但Google Apps脚本文档似乎并未否认。它会以这种方式工作吗？
不知道这是否是一个适当的地方来问这样的问题（因为这不是一个真正的问题），但自
对于数组来说，执行速度可能更高效吗？
>试试这个例子：（我在任何地方都添加了日志以显示发生了什么）
（还要注意，表单被限制在255列...照顾列表长度）
函数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
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）
}
最好的问候，
Serge
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:
Is this properly implemented? Can it be improved?
How about parameters validation? Should I do it? What should I do if they are invalid?
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.
Also I noticed that the Range moved from not always is cleared. Especially when in Debugger.
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?
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.
解决方案 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
这篇关于用于排列列，行或任何范围的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
