0

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?

Google sheet:
enter image description here

Google doc:
enter image description here

Mishal
  • 450
  • 9
  • 27
  • 1
    Have you followed best practices? See [tag info page](https://stackoverflow.com/tags/google-apps-script/info) for more details. – TheMaster Apr 30 '20 at 19:53
  • This is gold. Thanks for sharing! In the meantime, would you be able to guide me towards any direction with the above script. I am not familiar with the technical concepts yet. Thanks again @TheMaster!! – Mishal May 01 '20 at 14:55
  • 1
    See all those `getValue()`s? There should be only one. Read the "best practices" in that page I linked. If you're unfamiliar, take time to familiarize yourself with the concepts. – TheMaster May 01 '20 at 15:00
  • Ok - so I understand how calling batch operations using arrays are useful however how can I make this dynamic for the row I have selected. See edited post for first attempt at restructuring the code. – Mishal May 01 '20 at 23:05
  • Just added one more edit. @TheMaster - is there any other way of optimising the script? – Mishal May 01 '20 at 23:23
  • 1
    `getValues()` should be outside the loop. You're still making many calls. Consider practising [arrays](https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Array) – TheMaster May 02 '20 at 05:36
  • ok so I have got the arrays part of it and my code goes `var rng = ss.getRange("A1:"+"F"+lr).getValues(); for (var i =2;i<=lr;i++){ if(ss.getRange(i, 1).getValue()){ var client = rng[i-1][1]; var email = rng[i-1][5]; ` Now the next challenge is the formatting of particular cells. How can I update the formatting in an array? – Mishal May 05 '20 at 19:01
  • Kindly ask a new question. Also, no you haven't got the arrays part fully.`ss.getRange(i, 1).getValue()` is a call inside the loop and will slow things down significantly. – TheMaster May 05 '20 at 19:47
  • @TheMaster pls see [new question](https://stackoverflow.com/questions/61622332/how-to-optimise-appscript-code-by-using-arrays-to-pull-data-from-google-sheets) – Mishal May 05 '20 at 20:21
  • @TheMaster would be grateful if you could have a look at the [new question](https://stackoverflow.com/questions/61622332/how-to-optimise-apps-script-code-by-using-arrays-to-pull-data-from-google-sheets) – Mishal May 06 '20 at 01:00

0 Answers0