Apps Script: Fill template with rows of data from Google Sheet. HOW to make it more dynamic?

Question

I have a script which replaces every {{Keyword}} in a Google Doc template with data from each row in a Google Sheet. (Example sheet below)

Name	Address	City	Document Link
Name 1	Address 1	City 1	the script writes the new doc URL here
Name 2	Address 2	City 2	the script writes the new doc URL here

Here is the code I'm currently running (successfully):

function onOpen() {
  const ui = SpreadsheetApp.getUi();
  ui.createMenu('Documents & Mail Merge')
      .addItem('Generate Documents', 'createNewGoogleDocs')
      .addSeparator()
      .addToUi();
}

function createNewGoogleDocs() {
      const documentLink_Col = ("Document Link");
      const template = DriveApp.getFileById('templateIdGoesHere');
      const destinationFolder = DriveApp.getFolderById('destinationFolderIdGoesHere');
      const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data');
      const data = sheet.getDataRange().getDisplayValues();
      const heads = data[0]; // Assumes row 1 contains the column headings
      const documentLink_ColIndex = heads.indexOf(documentLink_Col);

data.forEach(function(row, index){
      if(index === 0 || row[documentLink_ColIndex]) return;
      const templateCopy = template.makeCopy(`${row[0]} ${row[1]} Report`, destinationFolder); //create a copy of the template document
      const templateCopyId = DocumentApp.openById(templateCopy.getId());
      const templateCopyBody = templateCopyId.getBody();
          templateCopyBody.replaceText('{{Name}}', row[0]);
          templateCopyBody.replaceText('{{Address}}', row[1]);
          templateCopyBody.replaceText('{{City}}', row[2]);
          templateCopyId.saveAndClose();
      const url = templateCopyId.getUrl();
          sheet.getRange(index +1 , documentLink_ColIndex + 1).setValue(url);
})
}

What I want to achieve / change in the functionality: Replace the hard-coded {{Placeholders}}, like templateCopyBody.replaceText('{{Name}}', row[0]); with another method that considers any column name as a potential {{Placeholder}} in the template document. So basically I should be free to edit, add, move or remove columns in the sheet, without having to hard-code them anymore, but rather just adapting the template.

Maybe helpful, I have found a kind of similar script that uses a Gmail draft as template instead of a Google Doc, and here are the 2 functions that in my understanding achieve what I need:

  /**
   * Fill template string with data object
   * @see https://stackoverflow.com/a/378000/1027723
   * @param {string} template string containing {{}} markers which are replaced with data
   * @param {object} data object used to replace {{}} markers
   * @return {object} message replaced with data
  */
  function fillInTemplateFromObject_(template, data) {
    // We have two templates one for plain text and the html body
    // Stringifing the object means we can do a global replace
    let template_string = JSON.stringify(template);

    // Token replacement
    template_string = template_string.replace(/{{[^{}]+}}/g, key => {
      return escapeData_(data[key.replace(/[{}]+/g, "")] || "");
    });
    return  JSON.parse(template_string);
  }

  /**
   * Escape cell data to make JSON safe
   * @see https://stackoverflow.com/a/9204218/1027723
   * @param {string} str to escape JSON special characters from
   * @return {string} escaped string
  */
  function escapeData_(str) {
    return str
      .replace(/[\\]/g, '\\\\')
      .replace(/[\"]/g, '\\\"')
      .replace(/[\/]/g, '\\/')
      .replace(/[\b]/g, '\\b')
      .replace(/[\f]/g, '\\f')
      .replace(/[\n]/g, '\\n')
      .replace(/[\r]/g, '\\r')
      .replace(/[\t]/g, '\\t');
  };
}

Me being a complete noob though, I wasn't able to successfully call the fillInTemplateFromObject_ function inside my foreach loop inside the original createNewGoogleDocs function, which is what I SUPPOSE I should do?

Sorry in advance for any potential poor choice of words due to lack of experience, and thanks everyone in advance for your support.

Answer 1

In your situation, how about the following modification?

Modified script:

Please set the template Document ID and folder ID.

function createNewGoogleDocs() {
  const documentLink_Col = "Document Link";
  const template = DriveApp.getFileById('templateIdGoesHere');
  const destinationFolder = DriveApp.getFolderById('destinationFolderIdGoesHere');
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data');
  const [header, ...values] = sheet.getDataRange().getDisplayValues();
  const documentLink_ColIndex = header.indexOf(documentLink_Col);
  const data = values.map(r => {
    const temp = r.reduce((o, c, j) => {
      o[header[j]] = c;
      return o;
    }, {});
    return { filename: `${r[0]} ${r[1]} Report`, obj: temp };
  });
  const v = data.map(({ filename, obj }) => {
    if (obj[documentLink_Col]) return [obj[documentLink_Col]];
    const templateCopy = template.makeCopy(filename, destinationFolder); //create a copy of the template document
    const templateCopyId = DocumentApp.openById(templateCopy.getId());
    const templateCopyBody = templateCopyId.getBody();
    Object.entries(obj).forEach(([k, v]) => templateCopyBody.replaceText(`{{${k}}}`, v));
    templateCopyId.saveAndClose();
    const url = templateCopyId.getUrl();
    return [url];
  });
  sheet.getRange(2, documentLink_ColIndex + 1, v.length, 1).setValues(v);
}

• In this modification, an object including the header and the value is created. Using this object, the placeholder is dynamically used. And, after the documents were created, the URLs are put in the column of Document Link.

• For example, when you changed the header value of the Spreadsheet and the placeholder of the template Document, this script can be used.

References:

• map()
• reduce()