Mail Merge Script not copying format

Question

I'm trying to execute a mailmerge script that reads fields from a spreadsheet and the template from a google doc. The script runs on the google doc.

The script works fine except it doesn't copy the formatting or table that is in the document. This is crucial.

Below is the code I am using.

function mmerge() {
  var mdoc=DocumentApp.getActiveDocument();
  var mdat=SpreadsheetApp.openById("1Nc2xu--").getSheetByName("MailMerge List);
  var numcols=mdat.getDataRange().getNumColumns();
  var numrows=mdat.getDataRange().getNumRows();
  var mtxt=mdoc.getBody().getText();
  var flds=[];
  for (var j=0;j<numcols;j++) {flds.push(mdat.getRange(1, j+1).getValue());}
  var rtxt;
  for (var i=2; i<=numrows; i++) {
    rtxt=mtxt
    for (var j=1; j<=numcols; j++) {
      rtxt=rtxt.replace("{"+flds[j-1]+"}",mdat.getRange(i, j).getValue());
    }
    mdoc.getBody().appendParagraph(rtxt);
  }  
}

I suspect that it has to do with the method used here: mtxt=mdoc.getBody().getText() but I don't know how to change it. My attempts to change it to copy() results in an error.

Does anyone know what I am doing wrong? Any help would be greatly appreciated.

Update: This is not a straight forward copy problem: the process involves having to execute the mail merge and then apply the formatting to the mailmerged doc.

Thanks

Answer 1

getText() returns a String, which has no character formatting.

The greater issue is that G Suite Services in Google Apps Script does not provide the document as HTML. (The getAs() method on the Document object only allows you to retrieve it as a PDF.)

You can adapt the "Google Doc to clean HTML converter" script, although it won't be trivial. It currently does not convert table content into HTML tables, but that would be a great contribution to the script that you could make! The script is written in a very straight-forward way, so should be quite simple to modify.

Specifically, you'll need to check for ElementTypes related to tables (TABLE, TABLE_ROW, TABLE_CELL) and then wrap those elements in the appropriate HTML tags. You can see how oazabir did it in this example:

if (item.getType() == DocumentApp.ElementType.PARAGRAPH) {
    switch (item.getHeading()) {
        // Add a # for each heading level. No break, so we accumulate the right number.
      case DocumentApp.ParagraphHeading.HEADING6: 
        prefix = "<h6>", suffix = "</h6>"; break;
      case DocumentApp.ParagraphHeading.HEADING5: 
        prefix = "<h5>", suffix = "</h5>"; break;
      case DocumentApp.ParagraphHeading.HEADING4:
        prefix = "<h4>", suffix = "</h4>"; break;
      case DocumentApp.ParagraphHeading.HEADING3:
        prefix = "<h3>", suffix = "</h3>"; break;
      case DocumentApp.ParagraphHeading.HEADING2:
        prefix = "<h2>", suffix = "</h2>"; break;
      case DocumentApp.ParagraphHeading.HEADING1:
        prefix = "<h1>", suffix = "</h1>"; break;
      default: 
        prefix = "<p>", suffix = "</p>";
    }

    if (item.getNumChildren() == 0)
      return "";
  }

Once you've converted your template into HTML, then you can manipulate it using your existing code. (For example, have ConvertGoogleDocToCleanHtml() return the HTML text and save that into your mtxt variable.)

Lastly, in your current script, you make multiple calls to get the same range. These calls are really slow. Instead, try to get the range once, get the values, and then operate on the returned Array.

var mailMergeRange = SpreadsheetApp.openById("1Nc2xu--").getSheetByName("MailMerge List").getDataRange().getValues();
var numcols = mailMergeRange.length;
var numrows = mailMergeRange[0].length;

You could also try this approach using Advanced Drive Services and the MailChimp API.