慢循环查找和复制 [英] Slow loop to find and copy

问题描述

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

I have a written a function to create reports:

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

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

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.

顺便说一句，您可以使用 array.push(item)将一个项目添加到数组的末尾.无需跟踪索引号.

By the way, you can use array.push(item) to add an item to the end of an array. No need to keep track of index numbers.

所以代替这个:

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 详细信息

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