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