How to create a new document from a template with placeholders

Question

I'm trying to create a script that will create new documents from a template-document. Replace placeholders in the documents with data from the sheet based on a keyword search in a specific column. And then change the row's value in the specific column so that the row will not process when the script runs again.

I think I've got it right with the first keyword search, and the loop through the rows. But the last part to get the data to 'merge' to the placeholders I can't figure out how to. I just get the value "object Object" and other values in the document.

var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = ss.getActiveSheet();
var lastColumn = s.getLastColumn();
function createDocFromSheet() {
  var headers = getUpsertHeaders(s);//function is defined outside of this function

  var statusColNum = headers.indexOf('Status')+1;

  var row = getRowsData(s); //The function is defined outside this function.
  for (var i=0; i<row.length; i++) {
     var jobStatus = '';
     if (row[i]['Status'] === '') {

//New: write the status to the correct row and column - this will be moved to the end when I get the rest right
      var jobStatus = "Created";
      s.getRange(i+2, statusColNum).setValue(jobStatus);

//Find the template and make a copy. Activate the body of the new file.
      var templateFile = DriveApp.getFileById('1lkfmqsJMjjPujHqDqKtcDmL-5GMIxpOWTyCOaK29d2A');
      var copyFile = templateFile.makeCopy()      
      var copyId = copyFile.getId()
      var copyDoc = DocumentApp.openById(copyId)
      var copyBody = copyDoc.getActiveSection()

//Find the rows Values as an object.
      var rows = s.getRange(i+2,1,1,lastColumn)
      var rowsValues = rows.getValues();
Logger.log(rowsValues)
//Until here I think it's okay but the last part?

//HOW TO replace the text???
      for (var columnIndex = 0; columnIndex < lastColumn; columnIndex++) {
        var headerValue = headerRow[columnIndex]
        var rowValues = s.getActiveRange(i,columnIndex).getValues()
        var activeCell = rowsValues[columnIndex]
        //var activeCell = formatCell(activeCell);
Logger.log(columnIndex);

        copyBody.replaceText('<<' + headerValue + '>>', activeCell)
}

Template doc : Link Template sheet: Link

Answer 1

After some coding I ended up with this code to process everything automatic. Again thanks to @carlesgg97.

The only thing I simply can't figure out now is how to generate the emailbody from the template with dynamic placeholders like in the document. How to generate the var patternToFind - but for the emailbody?

I've tried a for(var.... like in the document but the output doesn't replace the placeholders.

var DESTINATION_FOLDER_ID = '1inwFQPmUu1ekGGSB5OnWLc_8Ac80igK0';
var TEMPLATE_FILE_ID = '1lkfmqsJMjjPujHqDqKtcDmL-5GMIxpOWTyCOaK29d2A';

function fillTemplates() {
//Sheet variables
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Data');
  var values = sheet.getDataRange().getDisplayValues();
//Header variables
  var headers = sheet.getDataRange().getValues().shift();
  var idIndex = headers.indexOf('ID');
  var nameIndex = headers.indexOf('Name');
  var emailIndex = headers.indexOf('Email');
  var subjectIndex = headers.indexOf('Subject');
  var statusIndex = headers.indexOf('Status');
  var fileNameIndex = headers.indexOf('File Name');
  var filerIndex = headers.indexOf('Filer');
  var birthIndex = headers.indexOf('Date of birth');
  //Logger.log(statusIndex)
//Document Templates ID
  var templateFile = DriveApp.getFileById(TEMPLATE_FILE_ID);
//Destination
  var destinationFolder = DriveApp.getFolderById(DESTINATION_FOLDER_ID);

var templateTextHtml = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Email').getRange('D11').getValue();

//Run through the variables
for (var i=1; i<values.length; i++) {
//If first column is empty then stop
  var index0 = values[i][0];
  if(index0 == "") continue;
    var rowElements = values[i].length;
    var fileStatus = values[i][statusIndex];
//If the row already processed then stop
  if (fileStatus == "Created") continue;
//If the row is not processed continue
//Define the new filename by the relevant Column
    var fileName = values[i][fileNameIndex];
    var newFile = templateFile.makeCopy(fileName, destinationFolder);
    var fileToEdit = DocumentApp.openById(newFile.getId());
//Replace placeholders in the new document
  for (var j=1; j<rowElements-1; j++) {
      var header = values[0][j];
      var docBody = fileToEdit.getBody();
      var patternToFind = Utilities.formatString('{{%s}}', header);
      docBody.replaceText(patternToFind, values[i][j]); 
     }

//Create the PDF file
      fileToEdit.saveAndClose();
      var newPdf = DriveApp.createFile(fileToEdit.getAs('application/pdf'));
      DriveApp.getFolderById(DESTINATION_FOLDER_ID).addFile(newPdf);
      DriveApp.getRootFolder().removeFile(newPdf);
      newFile.setTrashed(true);
      var newPdfUrl = newPdf.getUrl();

//Create the emailbody  
  var textBodyHtml = templateTextHtml.replace("{{Name}}",values[i][nameIndex]).replace("{{Date of birth}}",values[i][birthIndex]);
  var textBodyPlain = textBodyHtml.replace(/\<br>/mg,"");

//Will send email to email Column
    var email = values[i][emailIndex];
    var emailSubject = values[i][idIndex]+" - "+values[i][fileNameIndex]+" - "+values[i][nameIndex];
    MailApp.sendEmail(email,emailSubject,textBodyPlain,     
    {
    htmlBody: textBodyHtml+
              "<p>Automatic generated email</p>",
    attachments: [newPdf],
    });    

      sheet.getRange(i+1, filerIndex+1).setValue(newPdfUrl);
      sheet.getRange(i+1, statusIndex+1).setValue('Created');

  }//Close for (var i=1...

}

Answer 2

You can use the following GAS code to accomplish your goals:

var DESTINATION_FOLDER_ID = 'YOUR_DESTINATION_FOLDER_ID';
var TEMPLATE_FILE_ID = 'YOUR_TEMPLATE_FILE_ID';

function fillTemplates() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var templateFile = DriveApp.getFileById(TEMPLATE_FILE_ID);
  var values = sheet.getDataRange().getDisplayValues();
  var destinationFolder = DriveApp.getFolderById(DESTINATION_FOLDER_ID);
  for (var i=1; i<values.length; i++) {
    var rowElements = values[i].length;
    var fileStatus = values[i][rowElements-1];
    if (fileStatus == 'Created') continue;
    var fileName = values[i][0];
    var newFile = templateFile.makeCopy(fileName, destinationFolder);
    var fileToEdit = DocumentApp.openById(newFile.getId());
    for (var j=1; j<rowElements-1; j++) {
      var header = values[0][j];
      var docBody = fileToEdit.getBody();
      var patternToFind = Utilities.formatString('<<%s>>', header);
      docBody.replaceText(patternToFind, values[i][j]); 
    }
    sheet.getRange(i+1, rowElements).setValue('Created');
  }
}

You only have to replace the 1st and 2nd lines as appropriate. Please do consider as well that the code will assume that the first column is the file name, and the last one the status. You can insert as many columns as you wish in between.