加快谷歌脚本值访问和元数据的速度?

Question

我编写了许多函数来查看单元格值并使用它们来设置其他单元格值或开发人员元数据，以及查看元数据以决定要做什么。当我对电子表格的功能进行压力测试时，当为大范围的单元格运行脚本会超过最大运行时间时，我遇到了问题。
研究它，我注意到获取和设置预定义单元格的单元格值大约需要 0.2 秒，查找和设置元数据大约需要 0.5 - 0.8 秒。
我写了一个示例脚本来显示这一点，但由于某种原因，时间更短，我不知道这是否与单元格中的内容有关，或者电子表格更小或......
但是，更改工作表中已具有指定键的一些元数据的值并触发此脚本：

function onEdit(e){
  timetest(e)
}
function timetest(e) {
  var range = e.range;
  var sheet = range.getSheet();
  for (var r = 1; r <= range.getNumRows(); r++){
    for (var c = 1; c <= range.getNumColumns(); c++){
      var cell = range.getCell(r, c);
      var value = cell.getValue();
      var new_val = value + ' ' + r + c;
      cell.setValue(new_val);
    }
    var metadata = sheet.createDeveloperMetadataFinder().withKey("Updated").find()
    if (metadata.length > 0){
      metadata[0].setValue(new_val)
    } else {
      sheet.addDeveloperMetadata("Updated", new_val, SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT);
    }
  }
}

给这个

[19-11-21 01:05:44:105 PST] Starting execution
[19-11-21 01:05:44:252 PST] PropertiesService.getDocumentProperties() [0.14 seconds]
[19-11-21 01:05:44:253 PST] SpreadsheetApp.Range.getSheet() [0 seconds]
[19-11-21 01:05:44:254 PST] SpreadsheetApp.Range.getNumRows() [0 seconds]
[19-11-21 01:05:44:254 PST] SpreadsheetApp.Range.getNumColumns() [0 seconds]
[19-11-21 01:05:44:255 PST] SpreadsheetApp.Range.getCell([1, 1]) [0 seconds]
[19-11-21 01:05:44:357 PST] SpreadsheetApp.Range.getValue() [0.101 seconds]
[19-11-21 01:05:44:413 PST] SpreadsheetApp.Range.setValue([p 12 11]) [0.056 seconds]
[19-11-21 01:05:44:413 PST] SpreadsheetApp.Range.getNumColumns() [0 seconds]
[19-11-21 01:05:44:414 PST] SpreadsheetApp.Sheet.createDeveloperMetadataFinder() [0 seconds]
[19-11-21 01:05:44:414 PST] SpreadsheetApp.DeveloperMetadataFinder.withKey([Updated]) [0 seconds]
[19-11-21 01:05:44:682 PST] SpreadsheetApp.DeveloperMetadataFinder.find() [0.267 seconds]
[19-11-21 01:05:44:836 PST] SpreadsheetApp.DeveloperMetadata.setValue([p 12 11]) [0.153 seconds]

关于是什么让这些调用变慢的任何想法？有没有办法优化代码，使它们尽可能快地运行？

Answer 1

在嵌套的 for 循环中，您正在发出对 getCell() 、 getValue() 和 setValue() 的请求。这会导致对电子表格 API 的许多远程请求，从而减慢脚本速度。

为了大大提高脚本的性能，您可以通过一次调用(getValues()(获取工作表上的所有数据，在其上应用您喜欢的操作，然后使用另一个单个调用(setValues()(将其设置回工作表。

另外，我建议您将"元数据"操作排除在循环之外，因为它似乎只对new_val的最后一个值很重要。这些修改后的最终代码可能如下所示：

function onEdit(e){
  timetest(e)
}
function timetest(e) {
  var range = e.range;
  var sheet = range.getSheet();
  var data = range.getValues();
  var new_val;
  for (var i=0; i<data.length; i++) {
    var row = data[i];
    for (var j=0; j<row.length; j++) {
      var value = row[j];
      new_val = value + ' ' + (i+1) + (j+1);
      row[j] = new_val;
    }
  }
  range.setValues(data);
  if (data.length > 0 && data[0].length > 0) {
    var metadata = sheet.createDeveloperMetadataFinder().withKey("Updated").find()
    if (metadata.length > 0){
      metadata[0].setValue(new_val)
    } else {
      sheet.addDeveloperMetadata("Updated", new_val, SpreadsheetApp.DeveloperMetadataVisibility.DOCUMENT);
    }
  }
}

最后，我建议您查看使用Google Apps Script的最佳实践。

配额

• 简单触发器的执行限制为 30 秒。
• "安装"onEdit 触发器时，该执行限制会增加到"正常"脚本运行时级别，即消费者版、G Suite 免费版和 G Suite 基本版/政府版为 6 分钟，抢先体验版和 G Suite 企业版/教育版为 30 分钟。
• 每个消费者帐户允许的每日总触发运行时间为 90 分钟，对于 G Suite 免费版帐户为 3 小时，对于更高级别的帐户为 6 小时。

引用

• 应用脚本配额