Google Apps Script Mail Merge - Grabbing Entire Body

Question

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.

Another issue with getBody(): it seems to only send plain-text forward. It does not retain any formatting or fonts I arranged in my template.

So my objectives are:

1. Grab the rich-text content of my template document

2. With each loop iteration, apply the content to the next page of target document in one-shot (not line by line).

3. Have this content maintain the formatting (font sizes, fonts, tabbing, spacing, etc.) of my template.

4. Update the dynamic fields with the row of information it's on for that iteration and move on.

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

//obviously what follows is a ridiculous way to do this, hence my issue
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();

//Clear the target document before creating the new merge
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];

//Again, what follows is ridiculous and not an ideal solution
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());

//Update the dynamic fields with this row's data
targetBody.replaceText('{{Name}}',name);
targetBody.replaceText('{{Address}}',address);
targetBody.replaceText('{{City}}',city);
targetBody.replaceText('{{ST}}',state);
targetBody.replaceText('{{ZIP}}',zip);

//Insert page break so next iteration begins on new page
targetBody.appendPageBreak();
}
}
}

score 0 · Answer 1 · answered Apr 27 '22 at 14:08

In the following example I am using a more Javascript approach using String.prototype.replace() to replace the text. I consider the following:

You have a template DOC where you have some strings like these {{Name}}:
You have a spreadsheet where the data to replace the template lives
You want to create a Google Doc for every of the rows

Considering this as true, the example shows this approach:

Grab all the text from the template doc
Replace the text using String.prototype.replace()
Setting the text of the new doc with the replaced one

`Code.gs`

const templateDocID = "<Template_DOC_ID>"
const dataSsId = "<Data_SS_ID>"

const doC = DocumentApp.openById(templateDocID)
const sS = SpreadsheetApp.openById(dataSsId).getSheets()[0]

function createDocFromTemplate() {
  /* Grab the data from the sheets */
  const dataToReplace = sS.getRange('A2:E').getValues().filter(n => n[0] !== "")
  dataToReplace.forEach((data) => {
    let body = doC.getBody().getText()
    /* Create a new doc for each row */
    const newDocument = DocumentApp.create('New Document')
    /* A quick approach to extract the data */
    const [name, address, city, state, zip] = data
    /* Using string.replace() */
    body = body.replace("{{Name}}", name)
    body = body.replace('{{Address}}', address)
    body = body.replace("{{City}}", city)
    body = body.replace("{{ST}}", state)
    body = body.replace("{{ZIP}}", zip)
    /* Setting the text  */
    newDocument.getBody().setText(body)
    /* Or sending it as an email */
    GmailApp.sendEmail('email@gmail.com', 'From Template', body)
    Logger.log(newDocument.getUrl())
  })
}

This is an example that can help you, but you can adapt it to meet your needs.

Google Apps Script Mail Merge - Grabbing Entire Body

1 Answers1

`Code.gs`

Documentation