How to optimize appscript to run faster?

Viewed 34

I have this running script: It updates the template sheet with the values from school list sheet then export PDF files in a specific folder. How Can I optimize this following code to RUN FASTER

Here is the sample code:

function checkSheet() {

var sheetName = "Copy of Sheet1";// template sheet
var folderID = "**************"; // Folder id to save in a folder.

var sourceSpreadsheet = SpreadsheetApp.getActive();
var dataSheet = sourceSpreadsheet.getSheetByName('school_list');
var templateSheet = sourceSpreadsheet.getSheetByName('Copy of Sheet1');

var data = dataSheet.getDataRange().getValues(); 
console.log(data.length) 
for (var i = 2; i <= data.length; i++) {
console.log(i)
var valueA = dataSheet.getRange('A'+i).getValue(); 
templateSheet.getRange('C1').setValue(valueA);
var pdfName = "OMR_ "+valueA;
var sourceSpreadsheet = SpreadsheetApp.getActive();
var sourceSheet = sourceSpreadsheet.getSheetByName(sheetName);
var folder = DriveApp.getFolderById(folderID);
//Copy whole spreadsheet
var destSpreadsheet = SpreadsheetApp.open(DriveApp.getFileById(sourceSpreadsheet.getId()).makeCopy("tmp_convert_to_pdf", folder))
//delete redundant sheets
var sheets = destSpreadsheet.getSheets();
for (k = 0; k < sheets.length; k++) {
 if (sheets[k].getSheetName() != sheetName){
   destSpreadsheet.deleteSheet(sheets[k]);
  }
}
var destSheet = destSpreadsheet.getSheets()[0];
//repace cell values with text (to avoid broken references) 
var sourceRange = sourceSheet.getRange(1,1,sourceSheet.getMaxRows(),sourceSheet.getMaxColumns());
var sourcevalues = sourceRange.getValues();
var destRange = destSheet.getRange(1, 1, destSheet.getMaxRows(), destSheet.getMaxColumns());
destRange.setValues(sourcevalues);
//save to pdf
var theBlob = destSpreadsheet.getBlob().getAs('application/pdf').setName(pdfName);
var newFile = folder.createFile(theBlob);
//Delete the temporary sheet
DriveApp.getFileById(destSpreadsheet.getId()).setTrashed(true);

} }

0 Answers
Related