时间戳

首页 > 解决方案 > 表格脚本执行突然变得更慢，所有函数持续时间都增加了

google-apps-script - 表格脚本执行突然变得更慢，所有函数持续时间都增加了

问题描述

我有一个谷歌表格脚本（实际上是几个），它总是表现良好。只需几秒钟即可执行。现在每个都需要几分钟！

我从其中一个选项卡中的图片按钮运行这些脚本，每月大约 2 次，因为它们与支付/计费相关。

上次我运行它时（大约 2 周前），我注意到它非常慢，所以我决定离开电脑，稍后再回来。当我返回时，脚本已经完成。

认为这是侥幸，因为剧本没有改变，我认为有一个网络故障或什么的。所以我再次尝试了这个脚本，它只运行了几秒钟。

现在，两周后，我开始运行脚本，每个脚本都花费了一个数量级的时间，如果不是更多的话。

我查看了执行记录，没有发现任何错误，但只有很长一段时间。请记住，我获得范围的每张纸只有 39 行！

我检查了 Chrome 浏览器的版本是最新的。

我已经刷新并重新启动。

代码：

function grabTotals() {
  var ss = SpreadsheetApp.getActiveSpreadsheet(),
      temp = ss.getSheetByName('TEMPLATE'),
      j = 0, // set for rows to populate amounts
      sumTotal = 0, //adding up for TOTAL on report
      all = ss.getSheetByName('BILLING_DASHBOARD'),
      sheets = ss.getSheets(),
      vals = all.getRange('d6:j').getValues();  // (WAS d5) Must set row before what is needed or it misses last counted down item
  ss.getSheetByName("SummaryTotals").activate().getRange("B2:C100").clearContent();

  for (var i = vals.length-1; i > 0; i--) {
    var name = vals[i][6]; //make array of the names from column
    if (name !='') {
      //Set up here an IF for checking vals[i][1] or [0] for the TRUE checkmark
      var isOn = vals[i][0]; // check for checkbox
      if (isOn) {
        var theActiveSheet = ss.getSheetByName(name);
        if (theActiveSheet !=null) {

          Logger.log(theActiveSheet &"---"& theActiveSheet.getName());

          var theAmounts = theActiveSheet.getRange("I13").getValues(); //Get the Total for check on named sheet

          if( theAmounts > 0) {//Zeros are not shown on summary or totaled
              ss.getSheetByName("SummaryTotals").getRange(3+j, 3).setValue(theAmounts); //Write amount from named sheet to the Summary sheet
              ss.getSheetByName("SummaryTotals").getRange(3+j, 2).setValue(vals[i][1]); //Write the NAME on the sheet to the Summary sheet

              Logger.log(theActiveSheet &"|||"& theActiveSheet.getName() &"|||"& theAmounts & "|||"& vals[i][1]);

              ss.getSheetByName("SummaryTotals").insertRows(4+j, 1);  //insert a row (May not be necessary)
              sumTotal = parseFloat(sumTotal) + parseFloat(theAmounts); //Keep a running balance for the Total on the Summary Sheet... done below
              j++;
          }
        }
      }
    }
  }

  multiSortColumns(); // UNCOMMENT for sorting.
  ss.getSheetByName("SummaryTotals").getRange(4+j, 2).setValue("Total");
  ss.getSheetByName("SummaryTotals").getRange(4+j, 3).setValue(sumTotal);
}

执行记录

[19-05-15 15:23:11:903 PDT] Starting execution
[19-05-15 15:23:11:917 PDT] SpreadsheetApp.getActiveSheet() [0 seconds]
[19-05-15 15:23:12:194 PDT] Sheet.protect() [0.276 seconds]
[19-05-15 15:23:22:627 PDT] Protection.setDescription([Sample protected sheet]) [10.432 seconds]
[19-05-15 15:23:22:628 PDT] Session.getEffectiveUser() [0 seconds]
[19-05-15 15:23:22:629 PDT] User.getEmail() [0 seconds]
[19-05-15 15:23:32:743 PDT] Protection.addEditor([jgarner@usmpllc.com]) [10.114 seconds]
[19-05-15 15:23:32:744 PDT] Protection.getEditors() [0 seconds]
[19-05-15 15:23:43:122 PDT] Protection.removeEditors([[jgarner@usmpllc.com]]) [10.378 seconds]
[19-05-15 15:23:43:123 PDT] Protection.canDomainEdit() [0 seconds]
[19-05-15 15:23:43:124 PDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[19-05-15 15:23:43:125 PDT] Spreadsheet.getSheetByName([TEMPLATE]) [0 seconds]
[19-05-15 15:23:43:125 PDT] Spreadsheet.getSheetByName([BILLING_DASHBOARD]) [0 seconds]
[19-05-15 15:23:43:131 PDT] Spreadsheet.getSheets() [0.006 seconds]
[19-05-15 15:23:43:132 PDT] Sheet.getRange([d6:j]) [0 seconds]
[19-05-15 15:23:53:561 PDT] Range.getValues() [10.428 seconds]
[19-05-15 15:23:53:562 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:23:53:562 PDT] Sheet.activate() [0 seconds]
[19-05-15 15:23:53:563 PDT] Sheet.getRange([B2:C100]) [0 seconds]
[19-05-15 15:23:53:564 PDT] Range.clearContent() [0 seconds]
[19-05-15 15:23:53:564 PDT] Spreadsheet.getSheetByName([jgaul]) [0 seconds]
[19-05-15 15:23:53:564 PDT] Sheet.getName() [0 seconds]
[19-05-15 15:23:53:565 PDT] Logger.log([0.0, []]) [0 seconds]
[19-05-15 15:23:53:566 PDT] Sheet.getRange([I13]) [0 seconds]
[19-05-15 15:24:14:130 PDT] Range.getValues() [20.564 seconds]
[19-05-15 15:24:14:131 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:24:14:132 PDT] Sheet.getRange([3, 3]) [0 seconds]
[19-05-15 15:24:14:133 PDT] Range.setValue([[[4200.0]]]) [0 seconds]
[19-05-15 15:24:14:133 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:24:14:134 PDT] Sheet.getRange([3, 2]) [0 seconds]
[19-05-15 15:24:14:134 PDT] Range.setValue([Jay Gaul]) [0 seconds]
[19-05-15 15:24:14:134 PDT] Sheet.getName() [0 seconds]
[19-05-15 15:24:14:135 PDT] Logger.log([0.0, []]) [0 seconds]
[19-05-15 15:24:14:135 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:24:24:375 PDT] Sheet.insertRows([4, 1]) [10.239 seconds]
[19-05-15 15:24:24:479 PDT] Spreadsheet.getSheetByName([jgarner]) [0.103 seconds]
[19-05-15 15:24:24:480 PDT] Sheet.getName() [0 seconds]
[19-05-15 15:24:24:480 PDT] Logger.log([0.0, []]) [0 seconds]
[19-05-15 15:24:24:481 PDT] Sheet.getRange([I13]) [0 seconds]
[19-05-15 15:24:34:489 PDT] Range.getValues() [10.007 seconds]
[19-05-15 15:24:34:489 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:24:34:490 PDT] Sheet.getRange([4, 3]) [0 seconds]
[19-05-15 15:24:34:490 PDT] Range.setValue([[[3850.0]]]) [0 seconds]
[19-05-15 15:24:34:491 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:24:34:491 PDT] Sheet.getRange([4, 2]) [0 seconds]
[19-05-15 15:24:34:492 PDT] Range.setValue([Jim Garner]) [0 seconds]
[19-05-15 15:24:34:505 PDT] Sheet.getName() [0 seconds]
[19-05-15 15:24:34:506 PDT] Logger.log([0.0, []]) [0 seconds]
[19-05-15 15:24:34:507 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:24:44:758 PDT] Sheet.insertRows([5, 1]) [10.25 seconds]
[19-05-15 15:24:44:862 PDT] Spreadsheet.getSheetByName([gimes]) [0.103 seconds]
[19-05-15 15:24:44:862 PDT] Sheet.getName() [0 seconds]
[19-05-15 15:24:44:863 PDT] Logger.log([0.0, []]) [0 seconds]
[19-05-15 15:24:44:863 PDT] Sheet.getRange([I13]) [0 seconds]
[19-05-15 15:24:54:868 PDT] Range.getValues() [10.004 seconds]
[19-05-15 15:24:54:869 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:24:54:869 PDT] Sheet.getRange([5, 3]) [0 seconds]
[19-05-15 15:24:54:870 PDT] Range.setValue([[[3456.6]]]) [0 seconds]
[19-05-15 15:24:54:870 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:24:54:870 PDT] Sheet.getRange([5, 2]) [0 seconds]
[19-05-15 15:24:54:871 PDT] Range.setValue([Grant Imes]) [0 seconds]
[19-05-15 15:24:54:871 PDT] Sheet.getName() [0 seconds]
[19-05-15 15:24:54:872 PDT] Logger.log([0.0, []]) [0 seconds]
[19-05-15 15:24:54:872 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:25:05:595 PDT] Sheet.insertRows([6, 1]) [10.722 seconds]
[19-05-15 15:25:05:596 PDT] SpreadsheetApp.getActiveSpreadsheet() [0 seconds]
[19-05-15 15:25:05:696 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0.099 seconds]
[19-05-15 15:25:05:697 PDT] Sheet.getRange([B3:C]) [0 seconds]
[19-05-15 15:25:05:697 PDT] Range.sort([[{column=2.0, ascending=true}]]) [0 seconds]
[19-05-15 15:25:05:801 PDT] Spreadsheet.toast([Sort complete.]) [0.103 seconds]
[19-05-15 15:25:05:802 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:25:05:803 PDT] Sheet.getRange([7, 2]) [0 seconds]
[19-05-15 15:25:05:803 PDT] Range.setValue([Total]) [0 seconds]
[19-05-15 15:25:05:804 PDT] Spreadsheet.getSheetByName([SummaryTotals]) [0 seconds]
[19-05-15 15:25:05:804 PDT] Sheet.getRange([7, 3]) [0 seconds]
[19-05-15 15:25:05:805 PDT] Range.setValue([11506.6]) [0 seconds]
[19-05-15 15:25:15:777 PDT] Execution succeeded [82.682 seconds total runtime]

我不是一个好的脚本编写者，但我确实让事情通常运作良好！然而，这超出了我的范围......就像一切都立即崩溃了，让我想知道谷歌是否发生了我不知道的巨大变化。**我是否支付了 GSuite 账单？！

请帮助我找到问题并在下一个计费周期之前解决它！

没有错误。只是非常长的执行，以前从未发生过。

标签： google-apps-scriptgoogle-sheetsexecution-time

解决方案

推荐阅读