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

我正在从另一个电子表格导入一个范围，并使用以下脚本将其粘贴到当前电子表格中：

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 个单元，我认为这应该不会太多。

任何人都知道这是怎么发生的？

非常感谢您的帮助！

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

原答案：getValues https://developers.google.com/apps-script/reference/spreadsheet/range#getvalues & setValues https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluesvalues

我们尝试通过创建一个比您的电子表格大六倍的电子表格（总共 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 https://developers.google.com/apps-script/reference/spreadsheet/range#copytodestination-options, getValues https://developers.google.com/apps-script/reference/spreadsheet/range#getvalues & setValues https://developers.google.com/apps-script/reference/spreadsheet/range#setvaluesvalues

要仅复制数据本身（也不复制链接或公式），我们可以使用该方法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 https://developers.google.com/apps-script/reference/spreadsheet/range#copytodestination-options

为了改进之前的代码，我实现了该方法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]

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