After trying a few mail merge scripts, I decided t write my own. My merge script runs as a separate fiIt reads a template from a GDoc, reads data from a GSpreadsheet, and merges it either into Gmails or into a new GDoc - one page / email per SS row.
The problem is that it doesn't copy text formatting, margins or images into the Gmail or new GDoc ... only the plain text.
I am using DocumentApp.openById > getActiveSection > getText() to capture the text.
Here is the code:
/**********************************************************************************/
/**********************************************************************************/
/**********************************************************************************/
/******** ********/
/******** Document and Email Data Merge ********/
/******** ********/
/******** ver 1.9.2013b ********/
/******** ********/
/**********************************************************************************/
/**********************************************************************************/
/**********************************************************************************/
function docAndEmailMerge() {
/**********************************************************************************/
/**********************************************************************************/
/******** User Settings ********/
/**********************************************************************************/
/**********************************************************************************/
// Identify the Google Spreadsheet document by its ID - data to be merged
var SSID = "0AgJjDgtUl5KddFdQRHRnRjVmMVlvOWlRa3Z2MlFLdGc";
// Identify the Google Document ID - document to merge
var docID = "1fxcn9pW1XySfrhMQlFyi9Zvhf54cTz96N8Vdm0McZD4";
// Settings
var mergeToEmail = true;
var mergeToSameDoc = true;
var mergeToIndividualDoc = false; // Not working yet
/**********************************************************************************/
/**********************************************************************************/
/******** Main Program ********/
/**********************************************************************************/
/**********************************************************************************/
if (mergeToSameDoc == true) {
var mergedDoc = DocumentApp.create('Data Merge');
} // end if
// Sheet within SS that has the data to be proessed
var sheet = "sheet1";
// Row 1 = labels, row 2 = first row with good data
var startRow = 2;
// Create spreadsheetApp object, open Spreadsheet
var ss = SpreadsheetApp.openById(SSID);
// Create sheet object, open active sheet within spreadsheet
var activeSheet = ss.getSheetByName(sheet);
// Grab the data from the spreadsheet, put into array object
var array = ss.getDataRange().getValues();
// Get number of rows and columns
numRows = array.length - 1;
var numCol = activeSheet.getLastColumn();
// Set the data range of the active spreadsheet
var dataRange = activeSheet.getRange(1, 1, 1, numCol);
// Get the first row labels into an array
var firstRow = getFirstRowLabels(dataRange);
var dataRange = activeSheet.getRange(startRow, 1, numRows, numCol)
// Fetch values for each row in the Range and put in multidimensional array
var data = dataRange.getValues();
// Working with the merge document
var doc = DocumentApp.openById(docID);
var body = doc.getActiveSection();
var text = body.getText();
// Get text from body of document that is to be merged
var text = body.getText();
// This is for logger output
var loggerOut = "";
// Repeat this loop for each row in "data" array
for (i in data) {
var row = data[i];
var temp = text;
for (var j = 2; j < numCol; j++) {
var search = "%" + firstRow[j] + "%";
var temp = replaceAll(temp, search, row[j]);
} // end for j
loggerOut += temp;
// Write results to Gmail or Docs
writeData(mergeToEmail, mergeToSameDoc, row, mergedDoc, temp);
} // end for i
Logger.log(loggerOut);
} // End main program docAndEmailMerge
/**********************************************************************************/
/**********************************************************************************/
/******** Sub Programs ********/
/**********************************************************************************/
/**********************************************************************************/
// Replace all occurances in a string
function replaceAll(Source,stringToFind,stringToReplace){
var temp = Source;
var index = temp.indexOf(stringToFind);
while(index != -1){
temp = temp.replace(stringToFind,stringToReplace);
index = temp.indexOf(stringToFind);
}
return temp;
} // end functio replaceAll
// Get first row labels of a ss
function getFirstRowLabels(dataRange) {
// Fetch values for each row in the Range and put in multidimensional array
var data = dataRange.getValues();
// Repeat this loop for each row in "data" array
for (i in data) {
var firstRow = data[i];
} // end for
return firstRow;
} // end getFirstRowLabels
function writeData(mergeToEmail, mergeToSameDoc, row, mergedDoc, temp) {
if (mergeToEmail == true) {
MailApp.sendEmail(row[0], row[1], temp);
} // end if
if (mergeToSameDoc == true) {
mergedDoc.appendParagraph(temp);
mergedDoc.appendPageBreak();
} // end if
}
You should copy the template first using DocsList so you start with a "complete" initial document.
var template = DocsList.getFileById(docIDs[0]);// get the template model, in this sample I had an array of possible templates, I took the first one
var newmodelName=template.substr(0,11)+'multipage'+template.substring(18);// define a new name, do what you need here...
var baseDocId = DocsList.copy(template,newmodelName).getId();// make a copy of firstelement and give it new basedocname build from the serie(to keep margins etc...)
var baseDoc = DocumentApp.openById(baseDocId);// this is the new doc to modify
then use the document class that has a direct replaceText method
EDIT : about your secondary question, here is a suggestion on how you could do. It works nicely except for inlineImage, I'll keep looking at this. You could also make the script more universal by adding other element types...
function myFunction() {
var template = DocsList.getFileById(key);// get the template model
var newmodelName='testcopy';// define a new name, do what you need here...
var baseDocId = DocsList.copy(template,newmodelName).getId();// make a copy of firstelement and give it new basedocname build from the serie(to keep margins etc...)
var baseDoc = DocumentApp.openById(baseDocId);// this is the new doc to modify
var body = baseDoc.getActiveSection();
body.appendPageBreak();
var totalElements = body.getNumChildren();
for( var j = 0; j < totalElements; ++j ) {
var element = body.getChild(j).copy();
var type = element.getType();
if( type == DocumentApp.ElementType.PARAGRAPH )
body.appendParagraph(element);
else if( type == DocumentApp.ElementType.TABLE )
body.appendTable(element);
else if( type == DocumentApp.ElementType.LIST_ITEM )
body.appendListItem(element);
else if( type == DocumentApp.ElementType.INLINE_IMAGE )
{ var blob = body.getChild(j).asInlineImage().getBlob();
body.appendImage(blob); }
}
}
Edit 2 Thanks to @Fausto, here is a fully working version. Inline images are included in a paragraph so we had to dig one level more to get the blob...
function myFunction() {
var template = DocsList.getFileById(key);// get the template model
var newmodelName='testcopy';// define a new name, do what you need here...
var baseDocId = DocsList.copy(template,newmodelName).getId();// make a copy of firstelement and give it new basedocname build from the serie(to keep margins etc...)
var baseDoc = DocumentApp.openById(baseDocId);// this is the new doc to modify
var body = baseDoc.getActiveSection();
body.appendPageBreak();
var totalElements = body.getNumChildren();
for( var j = 0; j < totalElements; ++j ) {
var element = body.getChild(j).copy();
var type = element.getType();
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();
body.appendImage(blob);
}
else body.appendParagraph(element.asParagraph());
}
else if( type == DocumentApp.ElementType.TABLE )
body.appendTable(element);
else if( type == DocumentApp.ElementType.LIST_ITEM )
body.appendListItem(element);
}
}