Google Apps Script to create an invoice

Question

I am using Google Execution API to create an invoice and it's a small part of a bigger system. However, my boss is pretty displeased at the total execution time before an invoice is generated. It takes 16 seconds for an invoice to be generated, and even longer for multiple payments in one invoice. I have been requested to cut it down to minimal. Parameters come from user input.

function doPost(e){
  return handleResponse(e);  
}
function handleResponse(e) {

  var lock = LockService.getPublicLock();
  lock.waitLock(30000);

  try {
    var studentData = e.data; //Sent from function within system.
    var conceptData = e.data2; //Sent from function within system.
    var headers = Object.keys(studentData);
    var row = [];
    var date = Utilities.formatDate(new Date(), "GMT-06:00", "d/M/yyyy hh:mm");
    studentData.horadate = date;
    for (i in headers){
      row.push(studentData[headers[i]]);  
}
    var docTemplates = {'DM':'1UIloenopaZH3J3MXqWQGlzUNNNC6WwsnShj2xROm1WQ',
                      'PM':'1UIloenopaZH3J3MXqWQGlzUNNNC6WwsnShj2xROm1WQ',
                      'SM':'1UIloenopaZH3J3MXqWQGlzUNNNC6WwsnShj2xROm1WQ',
                      'PT':'1UIloenopaZH3J3MXqWQGlzUNNNC6WwsnShj2xROm1WQ',
                      'SM':'1UIloenopaZH3J3MXqWQGlzUNNNC6WwsnShj2xROm1WQ',
                      'CM':'1KTmV1JFueqw6-7OvIyROWXxQM9dOyNLEjAEiWxemy8g',
                      'CT':'1KTmV1JFueqw6-7OvIyROWXxQM9dOyNLEjAEiWxemy8g',
                      '??':'1UIloenopaZH3J3MXqWQGlzUNNNC6WwsnShj2xROm1WQ'
                     }
      if (!docTemplates[studentData.campus]) {
       studentData.campus = '??'; 
      }

    var template = DriveApp.getFileById(docTemplates[studentData.campus]).makeCopy(),
        id = template.getId(),
        doc = DocumentApp.openById(id),
        body = doc.getBody(),
        header = doc.getHeader(),
        footer = doc.getFooter(),
        columnIndex = 0

    for (;columnIndex < headers.length; columnIndex++) {
      if (row[columnIndex]) {
      header.getParent().replaceText('<<' + headers[columnIndex] + '>>', 
                                     row[columnIndex])
      }
    } 
    for (i=0; i<conceptData.length; i++) {
      var conc = conceptData[i];

      var body = doc.getBody();
      var table = body.getTables()[i*1+1];
      var copy = table.copy();

      var td = table.getCell(0,0);
      td.setText('No.Folio:\n'+conc.folio+'\nNo. Doc. \n'+studentData.documento);

      var td = table.getCell(0,1);
      td.setText('Concepto:\n'+conc.conceptoname);

      var td = table.getCell(0,2);
      td.setText('Método de pago:\n'+conc.formaDePago+'\nReferencia: \n'+conc.referencia);

      var td = table.getCell(0,3);
      td.setText('Pagos anterior\n$'+(parseInt(conc.pagos)).toFixed(2)+'\nPagos actual. \n$'+(parseInt(conc.pagosDespues)).toFixed(2));

      var td = table.getCell(0,4);
      td.setText('Saldo anterior:\n$'+(parseInt(conc.saldoAntes)).toFixed(2)+'\nSaldo actual. \n$'+(parseInt(conc.saldoDespues)).toFixed(2));

      var td = table.getCell(0,5);
      td.setText('Importe total:\n$'+(parseInt(conc.totalImport)).toFixed(2)+'\nPago realizado. \n$'+(parseInt(conc.monto)).toFixed(2));

      var td = table.getCell(0,6);
      td.setText('Importe aplicable a \n'+conc.realMonth+' '+conc.realYear);


      var td = table.getCell(1,0);
      td.setText(conc.montoLetra);

      if (i+1 < conceptData.length) {
      var newTable = body.insertTable(body.getNumChildren()-1,copy);
      }

    }


    doc.saveAndClose() 
    var newFile = DriveApp.createFile(template.getAs('application/pdf'))  
    newFile.setName('Invoice concept '+studentData.conceptoname+' for '+studentData.fullName);
    template.setTrashed(true)
    var fileid = newFile.getId()
    // return json success results

    return {'fileid':fileid,'name':newFile.getName(),'data':conc,'data2':studentData}
  } catch(e){
    return {'A wild error appears':e,'result':e};
  } finally { //release lock
    lock.releaseLock();
  }
}

If you need even more data such as my execution API function which sends two parameters, or what the actual parameters look like. Don't hestitate to request them.

Answer 1

It is difficult to know exactly what the root cause of the slowness but I see some possible inefficiencies in the code.

The first thing I see is that the body and list of tables are fetched once for each iteration of the length of conceptData:

var body = doc.getBody();
var table = body.getTables()[i*1+1];

I am not aware of how expensive those calls are but if they take a lot of time the that likely gets multiplied by the number of loop iterations. You could try moving the calls to fetch the body and list of tables out of the for loop:

var body = doc.getBody();
var tables = body.getTables();

Then inside the for loop set the table accordingly:

for (i=0; i<conceptData.length; i++) {
  var table = tables[i*1+1];

---

After reading this answer to a similar post about a google apps script tot hide cells and this Google Apps Scripts Best Practices page I would suggest you look into fetching a range of cells into an array, modifying the array and then writing out the modified array to the cells. I see there is documentation for reading and writing ranges of cells though you may need to use different API objects than you currently are using, as well as promises, though maybe you are using an older version of the API.

---

Another thing I noticed is that var td gets repeated for each cell modified in the loop. While it isn’t wrong, you technically don’t need to repeat the var keyword after it has been declared once within the function, and some might argue that there is little need to assign the value because it is only used once (before being re-assigned).

You could avoid the assignment by chaining the calls- for example:

table.getCell(0,0)
  .setText('No.Folio:\n'+conc.folio+'\nNo. Doc. \n'+studentData.documento);

table.getCell(0,1)
  .setText('Concepto:\n'+conc.conceptoname);

Also, newTable doesn’t appear to be used so there is no point in assigning it:

var newTable = body.insertTable(body.getNumChildren()-1,copy);

Just insert the table:

body.insertTable(body.getNumChildren()-1,copy);