getdata（）脚本仅在时间触发时超时 [英] getdata() Script time out only when time-triggered

问题描述

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

I'm importing a range from another spreadsheet and pasting it in the current spreadsheet with this script:

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秒，并且在全部：

When firing the script manually the execution lasts around 20 seconds and has no problems at all:

[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秒，并且超时。

However, when being time-driven triggered (this Script should run automatically once per day) the execution time goes up to 374.518 secs and gets timed out.

编辑----->我一直在分割脚本并记录下来：

Edited-----> I've been splitting the script and logging it:

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'）。

Apparently it gets stuck in "var sj = si.getValues(); console.log('step1')".

整个范围大约有25.000个单元格，我认为应该不太多。

The whole range is aroung 25.000 cells, which I think it shouldn't be too much.

任何人都知道怎么回事？

Anyone has an idea how could this be happening?

非常感谢您的帮助！ p>

Thank you very much for help!

推荐答案

Update II 的运行速度确实要快得多，但我在执行步骤时遇到了一些问题：

Update II works really much faster, I've had though several problems with the steps:

 ss1.insertSheet('Clone');

和

 cloneSheet.copyTo(ss2);

调整脚本，因此不会删除原始电子表格中的克隆表，因此不必一开始就创建它，脚本运行流畅（在4秒内执行）。

Adjusting the script, so the "Clone" sheet in the original Spreadsheet is not deleted lter and hence this has not to be created any more at the beginning, the script is running smooth (execution under 4 seconds).

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

非常感谢所有帮助！

这篇关于getdata（）脚本仅在时间触发时超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！