javascript - 关于如何优化 Google AppScript 代码的建议

Question

我有下面的谷歌 AppScript 代码，它用于从谷歌表格中获取数据并将其填充到谷歌文档的占位符上。在一行数据上运行需要超过 4 分钟。有什么办法可以优化脚本并加快脚本运行的速度吗？任何正确方向的指导都会有所帮助并受到赞赏。提前致谢！

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

尝试1@批量操作：

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

如何为我选择的行动态运行它，我希望“检查 3”和“电子邮件 3”出现在我的谷歌文档上？

谷歌表：

谷歌文档：

标签： javascriptgoogle-apps-scriptoptimizationgoogle-sheetsgoogle-docs