慢循环查找和复制

本文介绍了慢循环查找和复制的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我写了一个创建报告的功能:

I have a written a function to create reports:

检测在特定范围内是否有特定字母(函数要求用户输入字母和列)
如果有字母，则抓住整行.
将其复制到另一张纸上.

我的代码当前正在运行，但是要花一些时间才能完成.另外，如果我有数百个结果，则会遇到超时问题.

My code is currently working, but it takes ages to finish. Also, if I have more than hundreds of results, I get a timeout issue.

您可以在此处重现该问题:

You can reproduce the issue here:

https://docs.google.com/spreadsheets/d/1ggVvxquruYfckNWxhsV6-Od2J8QIkMOYpJps7qz9PkI/edit?usp = sharing

这是代码:

for(var i = 0; i<rapport.length-1; i++) {
    if(colonneCode[i] == code.getResponseText()) {
      ligneCode[v] = i;
      v++;
    }
  }

for(var i = 0; i<ligneCode.length;i++) {

    var codeLastRow = 12;
    var copySource = sheet.getRange(ligneCode[i]+10, 1, 1, 16);
    var copyTarget = feuille.getRange(feuille.getLastRow()+1,1,1,16);
    copyTarget.setValues(copySource.getValues());
    copySource.copyTo(copyTarget, {formatOnly:true});

  }

点击个性化菜单(ÉvaluationsPhilippe Caron)->分类人员->第一输入框"2"被输入到第一输入框"2".->第二输入框"e"表示输入框"e".->第三个是您想要新工作表的名称.

Click on the personalized menu (Évaluations Philippe Caron) -> Classement personnalisé -> First input box "2" -> Second input box "e" -> Third one is the name you want the new sheet.

分析代码

我用这些代码片段检查了代码的时间.

Profiling your code

I used these snippets to check the times of your code.

let start = new Date()

Logger.log("before first for loop")
Logger.log(new Date().getTime() - start.getTime())

您可能已经猜到，对于工作表上的 e 示例，直到第二个for循环的大部分代码都在大约1秒钟内运行.但是，第二个for循环花费了大约45秒.这部分:

And as you probably guessed, for the e example on your sheet, most of the code until the second for loop ran in about 1 second. The second for loop, however, took around 45 seconds. This part:

for(var i = 0; i<ligneCode.length;i++) {

    var codeLastRow = 12;
    var copySource = sheet.getRange(ligneCode[i]+10, 1, 1, 16);
    var copyTarget = feuille.getRange(feuille.getLastRow()+1,1,1,16);
    copyTarget.setValues(copySource.getValues());
    copySource.copyTo(copyTarget, {formatOnly:true});

  }

为什么这段代码很慢?

因为在每次迭代中，它都会调用 getRange ， getValues ， setValues ， copyTo .所有这些命令均要求从电子表格读取和写入Apps脚本.这很慢.

Why is this code slow?

Because during every single iteration it is calling getRange, getValues, setValues, copyTo. All these commands require that the Apps Script execution read and write from the spreadsheet. This is slow.

将整个范围收集到一个大型2D数组中并将所有 setValues 一起收集起来要快得多.这将需要首先在Apps脚本中构建范围.因此，不是将行的索引号存储在 ligneCode 中，而是将整行存储在输出中.

It is much faster to collect the whole range in a large 2D array and setValues all together. This will require building the range within Apps Script first. So instead of storing the index numbers of the rows in ligneCode you store the whole row in an output.

所以代替这个:

var ligneCode = [];
for(var i = 0; i<rapport.length-1; i++) {
    if(colonneCode[i] == code.getResponseText()) {
      ligneCode[v] = i;
      v++;
    }
  }

构建一个数组，该数组表示要粘贴到新表中的内容.

Build an array that represents what you will paste in the new sheet.

var output = [];
for (var i = 0; i < rapport.length - 1; i++) {
  if (colonneCode[i] == code.getResponseText()) {
    output.push(rapport[i]);
  }
}

然后，一旦有了新的工作表 feuille ，您所需要做的就是:

Then once you have your new sheet feuille, all you need to do is:

var target = feuille.getRange(
    11, // first row
    1, // first column
    output.length, // the height of the output
    output[0].length // the width of the output
    );
target.setValues(output);

结果

现在以 e 示例开头，整个脚本大约需要2秒钟才能运行.

Result

Now with the e example at the start, the whole script takes about 2 seconds to run.

Apps脚本文档中的范围类
setValues 详细信息

这篇关于慢循环查找和复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

getRange