Using DocumentApp's append functions to 'MailMerge' data into single Google Document

Question

Update: I have updated my code with some of the suggestions as well as a feature that allows for easy multiple markers and changed the arrayOfData into a 2D Array or strings. Has similar runtimes, if not slightly slower - 50pg avg: 12.499s, 100pg avg: 21.688s, per page avg: 0.233s.

---

I am writing a script that takes some data and a template and performs a 'mail merge' type function into another document. The general idea for this is easy and I can do it no problem.

However, I am currently 'mail merging' many rows (150-300+) of ~5 columns of data into predefined fields of a single page template (certificates) into a single document. The result is a single Google Document with 150 - 300 pages of the certificate pages. The alternative is to generate many documents and, somehow, combine them.

Is This a Good/Efficient Way of Doing This?

It took me a while to work out put together this example from the documentation alone as I couldn't find anything online. I feel like there should be a simpler way to do this but can not find anything close to it (ie. appending a Body to a Body). Is this the best way to do get this functionality right now?

Edit: What about using bytes from the Body's Blob? I'm not experienced with this but would it work faster? Though then the issue becomes replacing text without generating many Documents before converting to Blobs?

*Note: I know Code Review exists, but they don't seem to have many users who understand Google Apps Script well enough to offer improvement. There is a much bigger community here! Please excuse it this time.

---

Here Is My Code (Updated Feb 23, 2018 @ 3:00PM PST)

Essentially it takes each child element of the Body, replaces some fields, then detects its type and appends it using the appropriate append function.

/* Document Generation Statistics:
* 50 elements replaced:
* [12.482 seconds total runtime]
* [13.272 seconds total runtime]
* [12.069 seconds total runtime]
* [12.719 seconds total runtime]
* [11.951 seconds total runtime]
*
* 100 elements replaced:
* [22.265 seconds total runtime]
* [21.111 seconds total runtime]
*/
var TEMPLATE_ID = "Document_ID";

function createCerts(){
  createOneDocumentFromTemplate(
    [
      ['John', 'Doe'], ['Jane', 'Doe'], ['Jack', 'Turner'], ['Jordan', 'Bell'],['Lacy', 'Kim']
    ],
      ["<<First>>","<<Last>>"]);
}

function createOneDocumentFromTemplate(arrayOfData, arrayOfMarkers) {

  var file = DriveApp.getFileById(TEMPLATE_ID).makeCopy("Certificates");
  var doc = DocumentApp.openById(file.getId());

  var body = doc.getBody();
  var fixed = body.copy();
  body.clear();

  var copy;

  for(var j=0; j<arrayOfData.length;j++){
    var item = arrayOfData[j];
    copy = fixed.copy();

    for (var i = 1; i < copy.getNumChildren() - 1; i++) {

      for(var k=0; k<arrayOfMarkers.length; k++){
        copy.replaceText(arrayOfMarkers[k], item[k]);
      }

      switch (copy.getChild(i).getType()) {
        case DocumentApp.ElementType.PARAGRAPH:
          body.appendParagraph(copy.getChild(i).asParagraph().copy());
          break;
        case DocumentApp.ElementType.LIST_ITEM:
          body.appendListItem(copy.getChild(i).asListItem().copy());
          break;
        case DocumentApp.ElementType.TABLE:
          body.appendTable(copy.getChild(i).asTable().copy());
          break;
      }
    }
  }

  doc.saveAndClose();
  return doc;
}

---

Gist

Answer 1

This is more of a Code Review question, but no, as written, I don't see any way to make it more efficient. I run a similar script for creating documents at work, though mine creates separate PDF files to share with the user rather than creating something we would print. It may save you time and effort to look into an AddOn like docAppender (if you're coming from a form) or autoCrat.

A couple of suggestions:

I'm more of a for loop person because it's easier to log errors on particular rows with the indexing variable. It's also more efficient if you're pulling from a spreadsheet where some rows could be skipped (already merged, let's say). Using forEach gives more readable code and is good if you always want to go over the entire array, but is less flexible with conditionals. Using a for loop will also allow you to set a row as merged with a boolean variable in the last column.

The other thing I can suggest would be to use some kind of time-based test to stop execution before you time the script out, especially if you're merging hundreds of rows of data.

// Limit script execution to 4.5 minutes to avoid execution timeouts
// @param {Object} - Date object from loop
// return Boolean

function isTimeUp_(starttime) {
  var now = new Date();
  return now.getTime() - starttime.getTime() > 270000; // 4.5 minutes
}

Then, inside your function:

var starttime = new Date();

replace.forEach(...

// include this line somewhere before you begin writing data
if (isTimeUp_(starttime )) {
  Logger.log("Time up, finished on row " + i);
  break;
}

... // rest of your forEach()