谷歌应用程序脚本中的 CopyTo 将无法完成超过 1000 行的执行

I am using originalRange.copyTo(rangeToCopyTo) to basically pull down functions for the number of rows of data I have. CopyTo works fine for a smaller number of rows of data but when I get to large reports that have 1000 results it doesn't finish. It looks like it pulls down the data but it doesn't actually fill in the cells correctly, they are left blank. I have tried using SpreadsheetApp.flush() but that doesn't work and I have also tried making the program sleep directly after the call with Utilities.sleep(some value in ms). I have been stuck for a while so any help would be greatly appreciated. Thank you.

红色框是我的原始范围，这些单元格充满了引用左侧数据的公式（我必须滚动，以便一些数据与行号一起离开屏幕到左侧）。蓝色框是我向下拖动到底部的范围（有 1000 行，所以想象一下蓝色框向下延伸 1000 行）。 G 列右侧的绿线一开始只有 3 行长，因为它是从模板表中复制的。因此，由于我看到该行已一直延伸到底部，我知道该函数几乎可以正常工作，只是在进入下一张纸之前没有填写我需要的所有公式，因此它会被跳过并留空。

function autoFillReport(sheet) {
  var rowsInSheet = sheet.getRange("A5:A").getLastRow();
  
  //get last row number of data
  var labelColumn = sheet.getRange("A5:A").getValues();
  var lastRowOfData;
  for (var i = 0; i < rowsInSheet - 4; i++) {
    if (labelColumn[i].toString().length < 1) {
      lastRowOfData = i + 4;
      break;
    };
  };
  
  //get last row number of rank formulas
  var trafficRankColumn = sheet.getRange("H5:H").getValues();
  var lastRowOfFormulas;
  for (i = 0; i < rowsInSheet - 4; i++) {
    if (trafficRankColumn[i].toString().length < 1) {
      lastRowOfFormulas = i + 4;
      break;
    };
  };
  
  //autofill remaining rows with rank formulas
  if (lastRowOfFormulas < lastRowOfData) {
    var originalRange = sheet.getRange("G5:L5");
    var targetRange = sheet.getRange("G5:L" + (lastRowOfData));
   // originalRange.copyTo(targetRange);
    originalRange.autoFill(targetRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);
    runRemainingFormulas = true;
  };
};

您想将公式复制下来吗？尝试这个

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('YOUR_SHEET');
  var range = sheet.getRange(5, 7, 3, 6);
  var targetRange = sheet.getRange(5, 7, sheet.getLastRow(), 6);
  range.autoFill(targetRange, SpreadsheetApp.AutoFillSeries.DEFAULT_SERIES);

这应该将公式一直复制到工作表的最后一行。 “getRange()”方法接受的参数表示左上角单元格（G7 - 第 5 行，第 7 列）和最右下角单元格（最后一行，第 6 列）的坐标。