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

Question

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);
}}}

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](https://www.udacity.com/course/object-oriented-javascript--ud015) — Alan Wells, Sep 15 '15 at 00:30
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, Sep 15 '15 at 00:41
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, Sep 15 '15 at 00:58
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, Sep 15 '15 at 14:34
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, Sep 15 '15 at 16:37
Just wondering about your original method: I have a script which does parts .makeCopy from template and fill from spreadsheet. This template is 1 page and about 10 replacements. There are several other things going on (save a copy to a logging spreadsheet; update the original spreadsheet) and this all takes about 25 seconds. So, my query: is your template many pages long ... is it this that taking up the time (vs 25 secs in my case)? — David Tew, Sep 16 '15 at 23:53
@Sandy Good - Thank you for your suggestions. I will see what I can do. If you like, I can also share the spreadsheet and doc template with you if you wish. Just let me know. — Steve P, Sep 29 '15 at 13:49
@DavidTew - My template is only one page and does 5 replacements. The script I currently have works great, but I am only able to perform about 57 copies in a 6 minute run. I'm trying to gain some efficiency so that I can do more copies per run. At times, I need to run nearly 1,000 copies. Thanks for your interest! — Steve P, Sep 29 '15 at 13:53
See my profile for my email address. I'd like to see your sheet and doc. — Alan Wells, Sep 29 '15 at 14:51

score 1 · Answer 1 · answered Sep 15 '15 at 01:12

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.

Sandy - don't be shy about editing questions when you answer them, or even just peruse them. This one was missing relevant tags - having them would get it in front of the GAS community better. — Mogsdad, Dec 04 '15 at 18:58

score 1 · Accepted Answer · answered Sep 30 '15 at 00:44

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});   
 } 
}

David, I will try this out and get back to you on how it works out. Thanks! — Steve P, Sep 30 '15 at 17:30
David, I had a chance to try this out. It is indeed fast! From what I can see from running it, the template must contain allTheName, course, and date as default merge fields before running it. If that is set, all is good and it works as it should. My only question is if there is any way to reset the merge fields on the template back to their defaults when the batch is complete? — Steve P, Sep 30 '15 at 19:23
Without thinking much about this, I would say to use the revision history to manually take the template back to the very beginning of its life. That would be quick to do manually. If this is important to be done through script I would say that you have a Master Template, and at the beginning of the "fast script" above you write a small script just to make a "Copy Template" from the Master Template (which as you know, this step will add a couple of seconds to the total run time) ; then the "fast script" will use/replaceText in this Copy Template rather than the Master Template. — David Tew, Oct 01 '15 at 14:35
David, understood. Thank you very much. This script has made a tremendous difference in my workflow and I am very grateful. THANK YOU!!! — Steve P, Oct 05 '15 at 17:24
You are welcome. Could you 'check' this answer to accept it as an answer to your query? This way I get a little kudos and also this question/answer then adds to the knowledge-base for the people coming along after us. — David Tew, Oct 05 '15 at 18:10
Hey, @DavidTew - don't be shy about editing questions when you answer them, or even just peruse them. This one was missing relevant tags - having them would get it in front of the GAS community better. — Mogsdad, Dec 04 '15 at 18:58

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

2 Answers2

Linked