Mail Merge Help - Grabbing and Placing Entire Body

[Mark Bailey]

Hello - I am officially stuck! Hopefully a fresh set of eyes can help...

I can't figure out out to grab the entire body of my source template and place it in one shot on the target document for reception of the data. As you can see from my code below, my workaround (and literally only thing I stumbled upon that worked) was to grab each line of the template document, and then place each line one-by-one on the target document. However, I don't consider this the appropriate solution for a few reasons: it's not pretty, it's a more resource-expensive run, and it absolutely would not work if I was creating a letter. 

Thankfully, since this was envelopes, I got through the job, but I'd like to discover the correct solution before my next mailing. I poured through the documentation, and there were a few functions that were potential candidates (such as 'getBody') but seemed not to be available (I would get 'not a function' errors. So, I'm at a loss.

I would greatly appreciate any help and/or insight!

Thanks!

------------------

function envelopeMailMerge() {

var sourceID = "[id of data sheet]";

var rangeData = 'OnePerFamily!A2:E251'; 

var values = Sheets.Spreadsheets.Values.get(sourceID,rangeData).values;

var templateID = "[id of template document]";

var targetID = "[id of target document]";

var templateBody = DocumentApp.openById(templateID).getBody();

var targetBody = DocumentApp.openById(targetID).getBody();

var theContent = templateBody.getChild(0).copy();

var theContent2 = templateBody.getChild(1).copy();

var theContent3 = templateBody.getChild(2).copy();

var theContent4 = templateBody.getChild(3).copy();

var theContent5 = templateBody.getChild(4).copy();

var theContent6 = templateBody.getChild(5).copy();

var theContent7 = templateBody.getChild(6).copy();

var theContent8 = templateBody.getChild(7).copy();

var theContent9 = templateBody.getChild(8).copy();

var theContent10 = templateBody.getChild(9).copy();

var theContent11 = templateBody.getChild(10).copy();

var theContent12 = templateBody.getChild(11).copy();

var theContent13 = templateBody.getChild(12).copy();

var theContent14 = templateBody.getChild(13).copy();

var theContent15 = templateBody.getChild(14).copy();

var theContent16 = templateBody.getChild(15).copy();

var theContent17 = templateBody.getChild(16).copy();

targetBody.clear();

if (!values) {

Logger.log('No data found...');

} else {

for (var row=0; row < values.length; row++) {

var name = values[row][0];

var address = values[row][1];

var city = values[row][2];

var state = values[row][3];

var zip = values[row][4];

targetBody.appendParagraph(theContent.copy());

targetBody.appendParagraph(theContent2.copy());

targetBody.appendParagraph(theContent3.copy());

targetBody.appendParagraph(theContent4.copy());

targetBody.appendParagraph(theContent5.copy());

targetBody.appendParagraph(theContent6.copy());

targetBody.appendParagraph(theContent7.copy());

targetBody.appendParagraph(theContent8.copy());

targetBody.appendParagraph(theContent9.copy());

targetBody.appendParagraph(theContent10.copy());

targetBody.appendParagraph(theContent11.copy());

targetBody.appendParagraph(theContent12.copy());

targetBody.appendParagraph(theContent13.copy());

targetBody.appendParagraph(theContent14.copy());

targetBody.appendParagraph(theContent15.copy());

targetBody.appendParagraph(theContent16.copy());

targetBody.appendParagraph(theContent17.copy());

targetBody.replaceText('{{Name}}',name);

targetBody.replaceText('{{Address}}',address);

targetBody.replaceText('{{City}}',city);

targetBody.replaceText('{{ST}}',state);

targetBody.replaceText('{{ZIP}}',zip);

targetBody.appendPageBreak();

}

}

}

------------------------

[Clark Lind]

It looks like the template and target docs are the same other than the variable fields you are replacing (name, address, etc)?  If that is true, the easier way to do this is use the Driveapp instead of the DocumentApp
Because your template file is already set up the way you want it, all you need to do is make a copy, give the copy a new name, and do the find/replace to personalize it.
The difference between these approaches is in your approach, you use a single target file. In the below method, you will have as many files as you have rows of data.
Is that an issue?

function envelopeMailMerge() {

    var sourceID = "[id of data sheet]";

    var rangeData = 'OnePerFamily!A2:E251'; 

    var values = Sheets.Spreadsheets.Values.get(sourceID,rangeData).values;

    var template = DriveApp.getFileById(' "[id of template document]"  );

    if (!values) {

      Logger.log('No data found...');

        } else {

     for (var row=0; row < values.length; row++) {

        var name = values[row][0];

        var address = values[row][1];

        var city = values[row][2];

        var state = values[row][3];

        var zip = values[row][4];

   

    var newDocTitle = "The name you want for the new document";  //maybe the name variable? 

    var newDoc = template.makeCopy(docTitle);

     newDoc .replaceText('{{Name}}',name);

     newDoc .replaceText('{{Address}}',address);

     newDoc .replaceText('{{City}}',city);

     newDoc.replaceText('{{ST}}',state);

     newDoc .replaceText('{{ZIP}}',zip);

[Clark Lind]

Of course, I was curious, so I wanted to see if what you were trying to do was indeed possible.  I figure the best way to do it is to open the template document,  get all the paragraphs, 
get the target document, clear whatever is there, then iterate over the paragraphs adding each one to the target doc.
This worked for me, including keeping the formatting and everything:

function myFunction() {

  var templateID = " [id of template document]  ";

  var targetID = " [id of target document]  ";

  var templateBody = DocumentApp.openById(templateID).getBody();

  var paras = templateBody.getParagraphs();

  var targetBody = DocumentApp.openById(targetID).getBody();

  targetBody.clear();  //just because you can

//using the insertParagraph method requires an index somewhere in the body to know where to insert the paragraph. 

//Because 'paras' is an array, we can loop through it and grab the array index of each paragraph and use that as the place to insert.

  paras.forEach( (para, idx ) => {       //idx is the index within the paras array where 'para' resides. 1st paragraph=0, 2nd=1, etc.

     let pCopy = para.copy();    //you can't just insert the paragraph because it is an element inside the template document, so you have to make a copy. Otherwise you will get an error. You can't just 'move' a paragraph between documents.

     targetBody.insertParagraph(idx, pCopy);

    })

}

[Clark Lind]

Mine (second version) is effectively identical to yours with just less verbosity. ;)

[Mark Bailey]

Man you nailed it! The element populated by getParagraphs being an array and looping through the paragraph elements to place them was what I was missing. Your approach cracked this open for me and I really appreciate it! 

There is one last thing I'm fine-tuning...your suspicion was correct that creating a new document with each run wasn't what I'm after. I'm needing to generate one document with each row having it's own page (since I'm processing about 250 data rows per run). So I've adjusted your code a bit to account for this, and everything is working expect that with your 'forEach',  "insertParagraph" adds the info to the top. So essentially, the new page is being created before the previous page, and when I open the target document the first page is the last row (for example, if the data sheet is sorted alphabetically, then the target document ends up sorted alphabetically backwards). 

So, I've adjusted "insertParagraph" to "appendParagraph" and that works, however it complains if I include the index as a parameter. Come to think of it, why do need to place the index on the target document - isn't that only useful in the array?

So basically, just sending 'pCopy' works, except for some reason it's adding a few lines to the top of each page. Do you have any thoughts on that?

[Mark Bailey]

And in follow up to that...I have found a solution to the reverse-order issue: I just run a '.reverse()' on the data array. That way the final merged document matches the order of the data set. 

However, I'd love to figure out a way to have each new page added after the last page, not before (if that is truly what is happening).

[Clark Lind]

Sorry if this breaks your code! lol  I figured the simplest way would be to get the last paragraph in the target doc and check if it was a page-break. If not, insert one. To get the last para, I counted the existing target doc paras. Then used this number for where to insert the new information:  NEW CODE

function myFunction() {

  var templateID = "yadayadaYourID";

  var targetID = "andTheOtherID :)";

  var templateBody = DocumentApp.openById(templateID).getBody();

  var paras = templateBody.getParagraphs();

  var targetBody = DocumentApp.openById(targetID).getBody();

  var targetBodyParas = targetBody.getParagraphs();

  var tParaLen = targetBodyParas.length;

  if (targetBodyParas[tParaLen - 1].getType() != DocumentApp.ElementType.PAGE_BREAK) {  //if last para in doc is not a page-break

    targetBody.appendPageBreak();  //insert one

  }

  paras.forEach((para, idx) => {

    let pCopy = para.copy()

    targetBody.insertParagraph(idx + tParaLen, pCopy)

  })

}

So each time this is called with new data, it should insert a page break at the current end of file, and start pasting in new data. This still inserts a blank line at the very beginning (which I'm trying to figure out), but this should be usable.