Need alternative to .makeCopy in Google Apps Script to decrease run time

0
votes

I wrote a mail merge script that works great, but execution transcripts revealed that the .makeCopy task alone consumed 60% of the 6 minute run time. I am trying to re-write the script in a way that enables me to:

  • Open a document template
  • populate the body of the template with spreadsheet data
  • create a new document
  • copy the body of the populated template to the new document
  • save the new document as a PDF, attach it to an email and send it
  • delete the new document (I don't need to retain a copy)

At present, I am receiving a "TypeError" appendtoDoc is not a function, it is undefined." Error in line 72.

//Creates the custom menu in the spreadsheet "Run Script"
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Run Script')
      .addItem('Create Certs', 'menuItem1')
      .addToUi();
}
 //Nest the createDocument function within the menuItem1 function for execution
function menuItem1() {
   function createDocFromSheet() {
}
//Defines the start row and calculates the number of rows to be processed
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = Browser.inputBox("Enter Start Row");
  var endRow = Browser.inputBox("Enter End Row");
  var numRows = (endRow - startRow) + 1;
  var dataRange = sheet.getRange(startRow, 1, numRows, 7);

//defines the variables and the email body
    var data = dataRange.getValues();
    for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var date = row[0];
    var nic = row[1];
    var course = row[2];
    var lastname = row[3];
    var firstname = row[4];
    var middle = row[5]
    var email = row[6];
    var docname = lastname+" "+nic+" PME Cert";
    var subjectTxt = "NWC "+ course +" Online PME Course Certificate";
    var fullBody = "PME COURSE COMPLETION CERTIFICATE" + "\n\n";
      fullBody += "Your " + course + " course completion certificate is attached." + "\n\n";
      fullBody += "Regards," + "\n\n";
      fullBody += "Professor Steve Pierce" + "\n";
      fullBody += "U.S. Naval War College "+ "\n";
      fullBody += "Online PME Program Team" + "\n\n";
      fullBody += "Learn more about NWC's Online PME Program at the link below:" + "\n";
      fullBody += "http://www.usnwc.edu/Academics/College-of-Distance-Education/PME-(1).aspx" + "\n";

// The old makeCopy code
//    var docId = DriveApp
//     .getFileById("1CjdoldpJmPskkqStpmBk3dRznFyURgY5mMsfVHfIGz4")
//     .makeCopy(docname).getId();

// Open the document template
//function createDocFromSheet(){
    var templateid = "1CjdoldpJmPskkqStpmBk3dRznFyURgY5mMsfVHfIGz4"
    var sheet = SpreadsheetApp.getActiveSpreadsheet();
    var data = dataRange.getValues();
    for (var i = 0; i < data.length; ++i) {
//create the new document
    var newDoc = DocumentApp.create(lastname+" "+nic+" PME Cert");
    var newDocId = newDoc.getId()
    var file = DriveApp.getFileById(newDocId)
// fill in the template with data
    for (var i in data){
      var row = data[i];
// opens the template and populates it with data from the sheet      
    var docid = DriveApp.getFileById(templateid).getId();
    var doc = DocumentApp.openById(docid);
    var body = doc.getActiveSection();
        body.replaceText('fname', firstname);
        body.replaceText('lname', lastname);
        body.replaceText('midname', middle);
        body.replaceText('course', course);
        body.replaceText('date', date);    
        doc.saveAndClose();
// appends data from the template to the new document
    var body = doc.getActiveSection();
    var newBody = newDoc.getActiveSection();
    appendToDoc(body, newBody);
    DocsList.getFileById(docid).setTrashed(true); //deletes the temp file
    }
}    

function appendToDoc(src, dst) {
    for (var i = 0; i < src.getNumChildren(); i++) {
    appendElementToDoc (dst, src.getChild(i));
    }
}    

function appendElementToDoc (doc, object) {
    var type = object.getType();
    var element = object.copy();
    if (type == DocumentApp.ElementType.PARAGRAPH) {
        if (element.asParagraph().getNumChildren() != 0 && element.asParagraph().getChild(0).getType() == DocumentApp.ElementType.INLINE_IMAGE) {
            var blob = element.asParagraph().getChild(0).asInlineImage().getBlob();
            doc.appendImage(blob);
      }
        else doc.appendParagraph(element.asParagraph());
    }

    MailApp.sendEmail(email, subjectTxt, fullBody, {attachments: Newdoc.getAs("application/pdf")});

    SpreadsheetApp.flush ();
    DriveApp.getFileById(docId).setTrashed(true);
}}}
2
performancegoogle-apps-script
You'll need to restructure your code in order to access the appendtoDoc function. You'll need to learn about scopes. You can watch a video from Udacity on Object-Oriented JavaScript - Scopes. Udacity - Object oriented javascript - Alan Wells
The nested for loop with the function call to appendDoc isn't able to interact with the rest of your program, where the appendDoc function is. It has to do with scopes and closures. - Alan Wells
You have a nested for loop that has the same counter variable i. I'd change the counter variable i in the inner nested for loop to something like j. for (var i in data){ should be: for (var j in data){ - Alan Wells
Sandy, I greatly welcome your comments. I will follow your recommendations soonest and will comment back to you on what I have learned. I do not have a lot of strength as a programmer, but I will do my best. I greatly appreciate the time you took to respond. --Steve - Steve P
I would abandon the nested functions. You really don't need to structure your code with functions inside of other functions. Unless you understand scopes, you are really just "rolling the dice" and hoping that your code works. Also, we can't access your spreadsheet, so there is no data to test the code. You can "hard code" an array of data for testing purposes. Data returned with getValues() is inside of a two dimensional array, an array inside of an outer array. - Alan Wells

2 Answers

1
votes

I modified your script to do it differently: I took a template, did replaceText then created a pdf from the changed template and sent the email; then I did replaceText again, pdf, email etc etc. I could email 10 certs in 15 secs this way. I didn't try a bigger number, and didn't check for errors. 

function creatCertPdfAndEmail() {   
 var sheet = SpreadsheetApp.openById('1fDe0ju0zkDr0cdA5hWBC4RsxZgFv6mIFn6W1WU-0S0w').getSheets()[0];  //I created a separate spreadsheet for testing purposes.
 var startRow =2;   
 var endRow =10 ;   
 var numRows = (endRow - startRow) + 1;   
 var dataRange = sheet.getRange(startRow, 1, numRows, 7);

 var counter =0;   
 var data = dataRange.getValues();   
 var templateid = "1DizlNa2ENpEMTUGhM78J0ozgh8A8Sc9fI1q1XmhvuLk"   
 var docid = DriveApp.getFileById(templateid).getId();

 var dateOld;   
 var courseOld;   
 var allTheNameOld;   
  for (var i = 0; i < data.length; ++i) {
        var doc = DocumentApp.openById(docid);
        var body = doc.getActiveSection();
        var row = data[i];
        var date = new Date().getTime() - new Date(row[0]).getTime(); //like this for testing to get a different value on each pdf
        var nic = row[1];
        var course = row[2];
        var lastname = row[3];
        var firstname = row[4];
        var middle = row[5]
        var email = row[6];
        //    var docname = lastname+" "+nic+" PME Cert"; //not used in this version
        var subjectTxt = "NWC "+ course +" Online PME Course Certificate";
        var fullBody = "PME COURSE COMPLETION CERTIFICATE" + "\n\n";
        fullBody += "Your " + course + " course completion certificate is attached." + "\n\n";
        fullBody += "Regards," + "\n\n";
        fullBody += "Professor Steve Pierce" + "\n";
        fullBody += "U.S. Naval War College "+ "\n";
        fullBody += "Online PME Program Team" + "\n\n";
        fullBody += "Learn more about NWC's Online PME Program at the link below:" + "\n";
        fullBody += "http://www.usnwc.edu/Academics/College-of-Distance-Education/PME-(1).aspx"
    + "\n";

        var row = data[i];
        var allTheName = firstname+' '+middle+' '+lastname
        if(counter ==0){
          body.replaceText('allTheName',allTheName);  // body.replaceText('Congratulations .*?\.',allTheName);
          body.replaceText('coursex', course);
          body.replaceText('datex', date);  
        }
        else {   
          body.replaceText(allTheNameOld,allTheName);
          body.replaceText(courseOld, course);
          body.replaceText(dateOld, date);  
        }

        dateOld = date;
        courseOld = course;
        allTheNameOld = allTheName

        counter ++

          doc.saveAndClose()
          var attachment = doc.getAs("application/pdf")

          MailApp.sendEmail(email, subjectTxt, fullBody, {attachments: attachment});   
 } 
}
1
votes

I tried to re-create your basic code structure in a simpler format, trying to reproduce the error.

//Nest the createDocument function within the menuItem1 function for execution
function menuItem1() {
  function createDocFromSheet() {}

  for (var i = 0; i < 2; ++i) {
    //create the new document
    Logger.log("First Loop ran i = " + i);
    for (var i = 0; i < 2; ++i) {
      Logger.log('  Inner For loop: i = ' + i)
      appendToDoc();
    }
  }

  function appendToDoc() {
    Logger.log('appendToDoc ran!');
    for (var i = 0; i < 2; ++i) {
      Logger.log('appendToDoc For Loop i=' + i);
      appendElementToDoc();
    }
  }

  function appendElementToDoc() {
    Logger.log('appendElementToDoc ran!');
    Logger.log('');
  }
}

That code actually runs for me, and is able to access the appendToDoc function when I run the menuItem1() function.

It looks like you have a function function createDocFromSheet() {} with nothing in it. I don't understand that.