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

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.