performance - getdata() 脚本仅在时间触发时超时

Question

我正在从另一个电子表格中导入一个范围，然后使用此脚本将其粘贴到当前电子表格中:

function getdata() { 
var values = SpreadsheetApp.openById('XXXXXX').
getSheetByName('SheetB').getRange('A4:AZ484').getValues();
var destsheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'); 
var destrange = destsheet.getRange('B520:BA1000'); 
destrange.setValues(values);
}

当手动触发脚本时，执行持续大约 20 秒并且完全没有问题:

[19-09-06 23:34:48:847 PDT] Starting execution
[19-09-06 23:34:48:999 PDT] SpreadsheetApp.openById([XXXXXX]) [0.145 seconds]
[19-09-06 23:34:49:000 PDT] Spreadsheet.getSheetByName([SheetB]) [0 seconds]
[19-09-06 23:34:49:000 PDT] Sheet.getRange([A4:AZ484]) [0 seconds]
[19-09-06 23:35:07:353 PDT] Range.getValues() [18.352 seconds]
[19-09-06 23:35:07:361 PDT] SpreadsheetApp.getActive() [0 seconds]
[19-09-06 23:35:07:510 PDT] Spreadsheet.getSheetByName([Sheet1]) [0.148 seconds]
[19-09-06 23:35:07:511 PDT] Sheet.getRange([B520:BA1000]) [0 seconds]
[19-09-06 23:35:09:907 PDT] Range.setValues([[[Tue Jan 01 00:00:00 GMT+01:00 2019, 235.1840336134454, 9.0, 41.525999999999996, 10.0, 21.0, 13.0, 13.0, 8.0, 59.43, 0.0, 0.0, 0.0, 0.0, 166.36, 0.0, 269.14, 8.0, 33.6425, 246.7, 1.0909606809890555,...) [2.374 seconds]
[19-09-06 23:35:10:571 PDT] Execution succeeded [21.055 seconds total runtime]

但是，当被时间驱动触发时(这个脚本应该每天自动运行一次)执行时间上升到 374.518 秒并超时。

已编辑----->我一直在拆分脚本并记录它:

function getdata() { 
var ss = SpreadsheetApp.openById('XXXXX');console.log('s1');
  var sh =ss.getSheetByName('SheetB');console.log('s2');
  var si = sh.getRange('A4:AZ484');console.log('s3');
  var sj = si.getValues(); console.log('step1')
  var destsheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'); console.log('step2')
  var destrange = destsheet.getRange('B520:BA1000'); console.log('step3')
  destrange.setValues(sj); console.log('step4')
}

显然它卡在“var sj = si.getValues(); console.log('step1')”中。

整个范围大约是 25.000 个细胞，我认为应该不会太多。

有人知道这怎么会发生吗？

非常感谢您的帮助!

Answer 1

TL;DR 解决此问题的最快方法是更新 II。

原始答案:getValues & setValues

我们试图通过创建比您的电子表格大六倍(总共 150,000 个单元格)的电子表格来重现此错误。然后我们运行一个函数，使用您的方法将其复制到空白电子表格中。多次运行后，我们平均需要 43 秒才能完成。这正是我们用来复制您的操作的函数:

function copySheet(){
  var ss1 = SpreadsheetApp.openById('{ORIGINAL SPREADSHEET}').getSheetByName('Sheet1');
  var ss2 = SpreadsheetApp.openById('{COPY SPREADSHEET}').getSheetByName('Sheet1');
  var originalData = ss1.getRange(1, 1, 75000, 2).getValues();
  var targetRange = ss2.getRange(1, 1, 75000, 2).setValues(originalData);
}

这是一个真实执行记录的例子:

[19-09-09 04:09:27:021 PDT] Starting execution
[19-09-09 04:09:27:122 PDT] SpreadsheetApp.openById([{ORIGINAL SPREADSHEET}]) [0.093 seconds]
[19-09-09 04:09:27:123 PDT] Spreadsheet.getSheetByName([Sheet1]) [0 seconds]
[19-09-09 04:09:27:211 PDT] SpreadsheetApp.openById([{COPY SPREADSHEET}]) [0.086 seconds]
[19-09-09 04:09:27:211 PDT] Spreadsheet.getSheetByName([Sheet1]) [0 seconds]
[19-09-09 04:09:27:212 PDT] Sheet.getRange([1, 1, 75000, 2]) [0 seconds]
[19-09-09 04:09:50:166 PDT] Range.getValues() [22.953 seconds]
[19-09-09 04:09:50:396 PDT] Sheet.getRange([1, 1, 75000, 2]) [0.001 seconds]
[19-09-09 04:10:06:199 PDT] Range.setValues() [15.59 seconds]
[19-09-09 04:10:07:128 PDT] Execution succeeded [39.172 seconds total runtime]

当我们使用触发器运行此函数时，我们得到了相似的时间值。有了所有这些信息，我们可以推断出范围的大小不是问题。由于您的函数在 getValues() 行中停止工作，并且正如您所说，它是一个包含大量信息并链接到多个来源的电子表格，我们建议在零复制的电子表格中重复此操作解决问题的链接。或者，您可以向我们提供匿名电子表格来对其进行测试。我们希望这对您有用。请不要犹豫，向我们提供更多信息以获得进一步的帮助。

更新一:copyTo , getValues & setValues

要仅复制数据本身(也不是链接或公式)，我们可以使用带有以下参数的方法 copyTo。不幸的是，该方法只能用于复制同一电子表格中的数据。作为解决方法，以下代码将在原始电子表格中创建一个新工作表，稍后仅将数据复制到该新工作表，然后将该工作表复制到目标电子表格。最后，中间表将被删除。

function copySheet2() {
  var ss1 = SpreadsheetApp.openById('{ORIGINAL SPREADSHEET}');
  var copySheet = ss1.insertSheet('Clone');
  var ss2 = SpreadsheetApp.openById('{COPY SPREADSHEET}').getSheetByName('Sheet1');
  var originalData = ss1.getSheetByName('Sheet1').getRange(1, 1, 75000, 2);
  var destinationRange = ss1.getSheetByName('Clone').getRange(1, 1, 75000, 2);
  originalData.copyTo(destinationRange, {
    contentsOnly: "true"
  });
  var cloneData = ss1.getSheetByName('Clone').getRange(1, 1, 75000, 2).getValues();
  var destinationSpreadsheet = ss2.getRange(1, 1, 75000, 2).setValues(cloneData);
  ss1.deleteSheet(ss1.getSheetByName('Clone'));
}

正如我们在新的执行记录中看到的那样，通过这种方法我们可以大大减少执行时间。

[19-09-11 10:11:50:333 CEST] Starting execution
[19-09-11 10:11:50:391 CEST] SpreadsheetApp.openById([{ORIGINAL SPREADSHEET}]) [0.052 seconds]
[19-09-11 10:11:52:132 CEST] Spreadsheet.insertSheet([Clone]) [1.741 seconds]
[19-09-11 10:11:54:012 CEST] SpreadsheetApp.openById([{COPY SPREADSHEET}]) [1.879 seconds]
[19-09-11 10:11:54:012 CEST] Spreadsheet.getSheetByName([Sheet1]) [0 seconds]
[19-09-11 10:11:57:376 CEST] Spreadsheet.getSheetByName([Sheet1]) [3.364 seconds]
[19-09-11 10:11:57:377 CEST] Sheet.getRange([1, 1, 75000, 2]) [0 seconds]
[19-09-11 10:11:57:377 CEST] Spreadsheet.getSheetByName([Clone]) [0 seconds]
[19-09-11 10:11:57:378 CEST] Sheet.getRange([1, 1, 75000, 2]) [0 seconds]
[19-09-11 10:11:57:378 CEST] Range.copyTo([Range, {contentsOnly=true}]) [0 seconds]
[19-09-11 10:11:57:379 CEST] Spreadsheet.getSheetByName([Clone]) [0 seconds]
[19-09-11 10:11:57:379 CEST] Sheet.getRange([1, 1, 75000, 2]) [0 seconds]
[19-09-11 10:12:04:314 CEST] Range.getValues() [6.934 seconds]
[19-09-11 10:12:04:445 CEST] Sheet.getRange([1, 1, 75000, 2]) [0 seconds]
[19-09-11 10:12:16:389 CEST] Range.setValues() [11.798 seconds]
[19-09-11 10:12:16:390 CEST] Spreadsheet.getSheetByName([Clone]) [0 seconds]
[19-09-11 10:12:16:950 CEST] Spreadsheet.deleteSheet([Sheet]) [0.56 seconds]
[19-09-11 10:12:18:021 CEST] Execution succeeded [26.613 seconds total runtime]

我们希望这个新程序对您有用，但如果您需要，请随时寻求更多帮助。

更新二:copyTo

为了改进之前的代码，我实现了方法copyTo，因为它的性能更高。这个新代码将开始复制范围并删除同一个电子表格中的公式(就像第一次更新一样)。它稍后会将数据从该克隆表复制到目标电子表格。最后，它会将导入的克隆表中的值复制到主表中。我添加了最后一个功能，因为我假设您希望在目标电子表格中四处移动数据。

function copySheet3() {
  var ss1 = SpreadsheetApp.openById('{ORIGINAL SPREADSHEET}');
  var ss2 = SpreadsheetApp.openById('{COPY SPREADSHEET}');
  ss1.insertSheet('Clone');
  var cloneSheet = ss1.getSheetByName('Clone');
  var dataRange = ss1.getSheetByName('Sheet1').getRange(1, 1, 75000, 2);
  var destinationRange = ss1.getSheetByName('Clone').getRange(1, 1, 75000, 2);
  dataRange.copyTo(destinationRange, {
    contentsOnly: "true"
  });
  cloneSheet.copyTo(ss2);
  ss1.deleteSheet(cloneSheet);
  var cloneSheet = ss2.getSheetByName(
  'Copy of Clone'); // Clone on the destination
  var tempRange = cloneSheet.getRange(1, 1, 75000, 2);
  var finalRange = ss2.getSheetByName('Sheet1').getRange(1, 1, 75000, 2);
  tempRange.copyTo(finalRange, {
    contentsOnly: "true"
  });
  ss2.deleteSheet(cloneSheet);
}

正如我们都可以在执行记录中看到的那样，这种方法比以前的选项更能最大限度地减少运行时间:

[19-09-16 10:24:04:036 CEST] Starting execution
[19-09-16 10:24:04:113 CEST] SpreadsheetApp.openById([{ORIGINAL SPREADSHEET}]) [0.071 seconds]
[19-09-16 10:24:04:444 CEST] SpreadsheetApp.openById([{COPY SPREADSHEET}]) [0.33 seconds]
[19-09-16 10:24:06:150 CEST] Spreadsheet.insertSheet([Clone]) [1.705 seconds]
[19-09-16 10:24:06:525 CEST] Spreadsheet.getSheetByName([Clone]) [0.374 seconds]
[19-09-16 10:24:06:525 CEST] Spreadsheet.getSheetByName([Sheet1]) [0 seconds]
[19-09-16 10:24:06:526 CEST] Sheet.getRange([1, 1, 75000, 2]) [0 seconds]
[19-09-16 10:24:06:527 CEST] Spreadsheet.getSheetByName([Clone]) [0 seconds]
[19-09-16 10:24:06:528 CEST] Sheet.getRange([1, 1, 75000, 2]) [0 seconds]
[19-09-16 10:24:06:528 CEST] Range.copyTo([Range, {contentsOnly=true}]) [0 seconds]
[19-09-16 10:24:12:755 CEST] Sheet.copyTo([Spreadsheet]) [6.226 seconds]
[19-09-16 10:24:13:271 CEST] Spreadsheet.deleteSheet([Sheet]) [0.515 seconds]
[19-09-16 10:24:13:272 CEST] Spreadsheet.getSheetByName([Copy of Clone]) [0 seconds]
[19-09-16 10:24:13:273 CEST] Sheet.getRange([1, 1, 75000, 2]) [0 seconds]
[19-09-16 10:24:13:273 CEST] Spreadsheet.getSheetByName([Sheet1]) [0 seconds]
[19-09-16 10:24:13:274 CEST] Sheet.getRange([1, 1, 75000, 2]) [0 seconds]
[19-09-16 10:24:13:275 CEST] Range.copyTo([Range, {contentsOnly=true}]) [0 seconds]
[19-09-16 10:24:15:594 CEST] Spreadsheet.deleteSheet([Sheet]) [2.319 seconds]
[19-09-16 10:24:15:767 CEST] Execution succeeded [11.553 seconds total runtime]

如果这个新代码示例对您的项目有帮助，请告诉我们所有人。随意添加更多想法或分享执行记录以进行更多开发。