CopyTo in google apps script won't finish its execution on more than 1000 rows

Question

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.

The red box is my original range those cells are full of formulas that reference the data to the left (I had to scroll over so some of the data is off screen to the left along with the row numbers). The blue box is the range I drag down to the bottom (there are 1000 rows so just imagine the blue box extending down for 1000 rows). The green line on the right side of column G starts out at only 3 rows long since this is duplicated from a template sheet. So since I see the line has been extended all the way to the bottom I know the function is almost working it's just not filling in all the formulas I need it to before moving on to the next sheet so it gets skipped and left blank.

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;
  };
};

Have your tired break them into 100 rows and looping 10 times to get all 1000 rows? Also, can you share an example sheet? — Jack Brown
Ok I just updated it. Also no I have not tried breaking them into 100 row increments. I would like to get it working this way to save time since there is a 6 min execution limit and I want to get as many reports done in each execution as possible. — Pony
I'm not looping anything right now I'm using the copyTo function in google apps script. I am going to try looping though since it seems to be something possibly with the copyTo function. — Pony

Anton Dementiev Anton Dementiev · Accepted Answer · 2018-03-21T22:46:24

Would you like to copy the formulas down? Try this

  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);

This should copy the formulas all the way down to the last row of your sheet. The arguments that the 'getRange()' method accepts represent the coordinates of the upper left cell (G7 - row 5, column 7) and the lowermost right cell (last row, column 6).

CopyTo in google apps script won't finish its execution on more than 1000 rows

1 Answers