为什么我的Apps脚本删除循环运行如此缓慢?我可以提高性能吗? [英] Why is my Apps Script deletion loop running so slowly? Can I improve performance?

问题描述

我的公司每天通过Google表单回复收集制造数据数十次.现在，我要引用的工作表将近10,000行.最近，我对某些Google Apps脚本代码(来自其他SO线程和论坛)进行了怪异的处理，以从7天以上的大型电子表格中删除行.在试用期间(在工作表副本上)，脚本每秒删除3-4行.现在，该代码每7-8秒仅删除一行.我不知道为什么我的代码运行这么慢.我的循环有问题吗?

My company collects manufacturing data a few dozen times a day through Google Form responses. Right now the Sheet I'll be referencing is near 10,000 rows. Recently I frankensteined some Google Apps Script code (from other SO threads and forums) to delete rows from a large spreadsheet that are older than 7 days. During trials (on a copy of the Sheet) the script deleted 3-4 rows per second. Now, the code is deleting only one row every 7-8 seconds. I don't know why my code is running so slowly. Is there something wrong with my loop?

在开发过程中，我已经在其他几个Sheets上尝试过此代码，并且效果很好.当脚本首次应用于目标工作表时，它按预期运行.但是，几天前我再次运行它时，脚本执行得非常慢.

I've tried this code on a couple of other Sheets during development and it worked perfectly fine. When the script was first applied to the target Sheet it operated as expected. However, when I went to run it again a few days ago the script executed extremely slowly.

删除行的功能由自定义菜单选项触发，该选项一旦在浏览器中打开工作表便会加载.

The function that deletes the rows is triggered by a custom menu option that is loaded once the Sheet is opened in the browser.

function onOpen() {
  SpreadsheetApp.getUi()
                .createMenu('Delete')
                .addItem('Delete Rows Older than 7 Days', 'deleteOldEntries')
                .addToUi();
}

function deleteOldEntries() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Form Responses 1");    //name of the sheet
  //var sheet = ss.getActiveSheet();
  var datarange = sheet.getDataRange();
  var lastrow = datarange.getLastRow();
  var values = datarange.getValues();   //get all data in a 2D array

  var currentDate = new Date();   //today
  var weekOld = Date.now() + -7*24*3600*1000;   //for dates longer than 7 days, change only the 7 to a higher number
  for (i=lastrow;i>=2;i--) {
    var tempDate = values[i-1][0];  //2D arrays are organized as [row][column]; arrays are 0 indexed so row1 = values[0] and col12 = [11]
    if ((tempDate!="") && (tempDate <= (weekOld)))
    {
      sheet.deleteRow(i);
    }
  }
}

我希望性能至少为每秒几行，但实际性能已降至每分钟6-7行.该脚本会在30分钟后超时，这是Google预先设置的时间限制(如果我没记错的话).

I expect the performance to at least be a few rows per second, but actual performance has slowed to 6-7 rows per minute. The script times out at 30 minutes, which is a preset time limit by Google (if I'm not mistaken).

提前谢谢！

这是最后一次运行代码的部分执行成绩单(整个执行成绩单很长).几乎从今天早上进行的上次运行开始的所有条目似乎都在4秒以上.

Here is the partial Execution Transcript from the last run of the code (the whole execution transcript was quite long). Almost all entries from the last run, conducted this morning, seem to be just above 4 seconds.

[19-05-31 08:57:24:875 EDT] Sheet.deleteRow([5681]) [4.048 seconds]
[19-05-31 08:57:29:295 EDT] Sheet.deleteRow([5680]) [4.419 seconds]
[19-05-31 08:57:34:013 EDT] Sheet.deleteRow([5679]) [4.718 seconds]
[19-05-31 08:57:38:561 EDT] Sheet.deleteRow([5678]) [4.547 seconds]
[19-05-31 08:57:42:991 EDT] Sheet.deleteRow([5677]) [4.43 seconds]
[19-05-31 08:57:47:599 EDT] Sheet.deleteRow([5676]) [4.607 seconds]
[19-05-31 08:57:52:122 EDT] Sheet.deleteRow([5675]) [4.522 seconds]
[19-05-31 08:57:56:683 EDT] Sheet.deleteRow([5674]) [4.56 seconds]
[19-05-31 08:58:00:950 EDT] Sheet.deleteRow([5673]) [4.266 seconds]
[19-05-31 08:58:05:450 EDT] Sheet.deleteRow([5672]) [4.5 seconds]
[19-05-31 08:58:10:349 EDT] Sheet.deleteRow([5671]) [4.898 seconds]
[19-05-31 08:58:14:736 EDT] Sheet.deleteRow([5670]) [4.387 seconds]
[19-05-31 08:58:19:697 EDT] Sheet.deleteRow([5669]) [4.96 seconds]
[19-05-31 08:58:28:412 EDT] Sheet.deleteRow([5668]) [8.714 seconds]
[19-05-31 08:58:33:601 EDT] Sheet.deleteRow([5667]) [5.189 seconds]
[19-05-31 08:58:38:845 EDT] Sheet.deleteRow([5666]) [5.243 seconds]
[19-05-31 08:58:43:313 EDT] Sheet.deleteRow([5665]) [4.468 seconds]
[19-05-31 08:58:47:964 EDT] Sheet.deleteRow([5664]) [4.65 seconds]
[19-05-31 08:58:52:947 EDT] Sheet.deleteRow([5663]) [4.982 seconds]
[19-05-31 08:58:58:172 EDT] Sheet.deleteRow([5662]) [5.223 seconds]
[19-05-31 08:59:03:036 EDT] Sheet.deleteRow([5661]) [4.864 seconds]
[19-05-31 08:59:07:957 EDT] Sheet.deleteRow([5660]) [4.92 seconds]
[19-05-31 08:59:12:727 EDT] Sheet.deleteRow([5659]) [4.769 seconds]
[19-05-31 08:59:12:800 EDT] Execution failed: Exceeded maximum execution time [1,803.124 seconds total runtime]

推荐答案

您需要使用批处理操作.

You need to use batch operations.

  var thisBatch = 0;//added
  for (i=lastrow;i>=2;i--) {
    var tempDate = values[i-1][0];  
    if ((tempDate!="") && (tempDate <= (weekOld)))
    {
        ++thisBatch;//increment this batch
    } else if(thisBatch){ // previous batch ended
        sheet.deleteRows(i+1, thisBatch); //delete next row and all of that batch 
        thisBatch = 0;
    }
  }
 thisBatch ? sheet.deleteRows(i+1, thisBatch) : null;//to remove any batch after remaining after end of loop

如果仍然很慢，则需要使用Google的高级服务来访问表格api并发出

If that's still slow, you need to use Advanced Google services to access the sheets api and issue Delete Dimension Request. Also trim your sheet by deleting all empty rows at the bottom of the sheet manually.

这篇关于为什么我的Apps脚本删除循环运行如此缓慢?我可以提高性能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！