为什么我的Apps脚本删除循环运行得这么慢？我能提高性能吗？

Question

我的公司每天通过Google Form responses收集几十次制造业数据。现在，我将要引用的工作表接近10,000行。最近，我科学地编写了一些Google Apps脚本代码(来自其他SO线程和论坛)，以便从一个超过7天的大型电子表格中删除行。在试验期间(在工作表的副本上)，脚本每秒删除3-4行。现在，代码每7-8秒只删除一行。我不知道为什么我的代码运行得这么慢。我的循环有什么问题吗？

在开发期间，我已经在其他几个工作表上尝试过这个代码，它工作得非常好。当脚本第一次应用于目标工作表时，它的操作符合预期。然而，当我几天前再次运行它时，脚本执行得非常慢。

删除行的功能由在浏览器中打开图纸时加载的自定义菜单选项触发。

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预设的时间限制(如果我没记错的话)。

提前感谢！

编辑:这是代码最后一次运行的部分执行脚本(整个执行脚本相当长)。今天早上进行的最后一次跑步，几乎所有的条目似乎都在4秒以上。

[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]

Answer 1

需要使用批处理操作。

代码片段：

  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服务来访问sheets api并发出 Delete Dimension Request。还可以通过手动删除工作表底部的所有空行来裁剪工作表。