I have the below Google AppScript code which is used to take data from a google sheet and populates it on placeholders on a google doc. It takes more than 4 mins to run on one row of data. Is there any way I could optimise the script and speed up the way the script runs? Any guidance in the right direction would be helpful and is appreciated. Thanks in advance!
function generateLPO() {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("LPOs").activate();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
for (var i =2;i<=lr;i++){
if(ss.getRange(i, 1).getValue()){
//Make a copy of the template file
var documentId = DriveApp.getFileById('File_ID').makeCopy().getId();
var LPONumber = ss.getRange(i, 2).getValue();
var Name = ss.getRange(i, 3).getValue();
var Address = ss.getRange(i, 4).getValue();
var Email = ss.getRange(i, 5).getValue();
var Attention = ss.getRange(i, 6).getValue();
var LPODate = ss.getRange(i, 7).getValue();
var temp = new Date(LPODate)
var LPODateFormat = Utilities.formatDate(temp, "GMT+0400", "dd MMM yyyy")
var Currency = ss.getRange(i, 8).getValue();
var PreparedBy = ss.getRange(i, 9).getValue();
var InvoiceNo = ss.getRange(i, 10).getValue();
var InvoiceDate = ss.getRange(i, 11).getValue();
var temp2 = new Date(InvoiceDate)
var InvoiceDateFormat = Utilities.formatDate(temp2, "GMT+0400", "dd MMM yyyy")
var JobCode = ss.getRange(i, 12).getValue();
var AccountCode1 = ss.getRange(i, 13).getValue();
var Description1 = ss.getRange(i, 14).getValue();
var Qty1 = ss.getRange(i, 15).getValue();
var Qty1Format = Qty1.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var Unit1 = ss.getRange(i, 16).getValue();
var Unit1Format = Unit1.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var Amount1 = ss.getRange(i, 17).getValue();
var Amount1Format = Amount1.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var AccountCode2 = ss.getRange(i, 18).getValue();
var Description2 = ss.getRange(i, 19).getValue();
var Qty2 = ss.getRange(i, 20).getValue();
var Qty2Format = Qty2.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var Unit2 = ss.getRange(i, 21).getValue();
var Unit2Format = Unit2.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var Amount2 = ss.getRange(i, 22).getValue();
var Amount2Format = Amount2.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var AccountCode3 = ss.getRange(i, 23).getValue();
var Description3 = ss.getRange(i, 24).getValue();
var Qty3 = ss.getRange(i, 25).getValue();
var Qty3Format = Qty3.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var Unit3 = ss.getRange(i, 26).getValue();
var Unit3Format = Unit3.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var Amount3 = ss.getRange(i, 27).getValue();
var Amount3Format = Amount3.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var Amount = ss.getRange(i, 28).getValue();
var AmountFormat = Amount.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var VAT = ss.getRange(i, 29).getValue();
var VATFormat = VAT.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var Total = ss.getRange(i, 30).getValue();
var TotalFormat = Total.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, '$1,');
var LegalEntity = ss.getRange(i, 31).getValue();
var Address2 = ss.getRange(i, 32).getValue();
var VATReg = ss.getRange(i, 33).getValue();
//Rename the copied file
DriveApp.getFileById(documentId).setName(LPONumber+"_"+Name+" ["+InvoiceNo+"]");
//Get the document body as a variable
var body = DocumentApp.openById(documentId).getBody();
body.replaceText('##LPO No.##', LPONumber).replaceText('##Name##', Name).replaceText('##Address##', Address).replaceText('##Email##', Email).replaceText('##Attention##', Attention).replaceText('##LPO Date##', LPODateFormat).replaceText('##Currency##', Currency).replaceText('##Prepared by##', PreparedBy).replaceText('##Invoice No.##', InvoiceNo).replaceText('##Invoice Date##', InvoiceDateFormat).replaceText('##Job Code##', JobCode).replaceText('##Account code 1##', AccountCode1).replaceText('##Description 1##', Description1).replaceText('##Qty 1##', Qty1Format).replaceText('##Unit 1##', Unit1Format).replaceText('##Amount 1##', Amount1Format).replaceText('##Account code 2##', AccountCode2).replaceText('##Description 2##', Description2).replaceText('##Qty 2##', Qty2Format).replaceText('##Unit 2##', Unit2Format).replaceText('##Amount 2##', Amount2Format).replaceText('##Account code 3##', AccountCode3).replaceText('##Description 3##', Description3).replaceText('##Qty 3##', Qty3Format).replaceText('##Unit 3##', Unit3Format).replaceText('##Amount 3##', Amount3Format).replaceText('##Amount##', AmountFormat).replaceText('##VAT##', VATFormat).replaceText('##Total##', TotalFormat).replaceText('##LegalEntity##', LegalEntity).replaceText('##Address2##', Address2).replaceText('##VATReg##', VATReg);
}
else {}
}
}
Attempt 1 @ Batch operations:
function generatetest() {
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1").activate();
var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
var lr = ss.getLastRow();
for (var i =2;i<=lr;i++){
if(ss.getRange(i, 1).getValue()){
//Make a copy of the template file
var documentId = DriveApp.getFileById('1j36HPQkTPc0R4GCtA0XKcmeHUVPsgBKoyNIl93HFhp0').makeCopy().getId();
//var Name = ss.getRange(i, 2).getValue();
//var Email = ss.getRange(i, 3).getValue();
var range = ss.getRange("B:C");
var values = range.getValues();
//Rename the copied file
DriveApp.getFileById(documentId).setName("["+values[i-1][0]+"]");
//Get the document body as a variable
var body = DocumentApp.openById(documentId).getBody();
body.replaceText('{Name}', values[i-1][0]).replaceText('{Email}', values[i-1][1])
}
else {}
}
}
How do I run this dynamically for the row I have selected where I want "Check 3" and "Email 3" to come on my google doc?