I’m trying to automate some changes in a file that has two versions: one with about 5,000 rows and another with around 80,000 rows.
When I run my script on the smaller file, it works fine. But when I run it on the larger file, it times out.
Originally, I used a for loop to write a formula into each row one by one. To improve performance, I switched to building an array of formulas first and then writing them all at once.
Here’s the relevant part of my code.
The argument sheet comes from spreadsheet.geSheetByName(sheetName).
The main sheet contains only data, number and letters, and this function tries to import information from other 3 sheets that I have on the file through VLOOKUP, and these other sheets contain only simple data as well, numbers and letters.
function insertNewColumns(sheet){
Logger.log("Inserting new columns: Commited Projects, Skillsets and Country PPM!");
var startRow = (SheetType.timeUnit === "YEAR") ? 20 : 21
sheet.insertColumnAfter(2);
var columnC = 3;
sheet.getRange(startRow, columnC).setValue("Committed Projects");
sheet.insertColumnAfter(7);
var columnH = 8;
sheet.getRange(startRow, columnH).setValue("Skillset");
sheet.insertColumnAfter(13);
var columnN = 14;
sheet.getRange(startRow, columnN).setValue("Country PPM");
var lastRow = sheet.getRange("A:A").getLastRow();
var numRows = lastRow-startRow;
var formulasC = [];
var formulasH = [];
var formulasN = [];
for (var row = startRow+1; row <= lastRow; row++) {
formulasC.push([`=IFERROR(VLOOKUP(value(A${row}), 'Committed Projects'!A:B, 2, 0), "XXXXX")`]);
formulasH.push([`=IFERROR(VLOOKUP(G${row}, 'Skillset'!A:B, 2, 0), "XXXXX")`]);
formulasN.push([`=IFERROR(VLOOKUP(M${row}, 'Country PPM'!A:B, 2, 0), "XXXXX")`]);
}
sheet.getRange(startRow + 1, columnC, numRows, 1).setFormulas(formulasC); // IT TIMES OUT HERE
sheet.getRange(startRow + 1, columnH, numRows, 1).setFormulas(formulasH);
sheet.getRange(startRow + 1, columnN, numRows, 1).setFormulas(formulasN);
SpreadsheetApp.flush();
}
I first tried using a for loop to write each formula directly to the cell in every iteration. I expected this to work, but it was very slow for large files and timed out. To improve this, I changed my approach to build an array of all the formulas first and then write them to the sheet in one operation. I expected this to solve the timeout issue, but it still times out on the larger file with 80,000 rows.
How I can stop the script from timing out on the larger file?