AutoFill Google Docs From Spreadsheet

Question

I'm studding the solution proposed in this site: https://jeffreyeverhart.com/2020/09/29/auto-fill-a-google-doc-template-from-google-sheet-data/ Especially I'm interesting in the replacement of tokens with data from a spreadsheet.

But I would like a more flexible solution. In this examples the tokens are defined in the code:

//In these lines, we replace our replacement tokens with values from our spreadsheet row
body.replaceText('{{First Name}}', row[0]);
body.replaceText('{{Last Name}}', row[1]);
body.replaceText('{{Position}}', row[2]);

I would like that all tokens available in the spreadsheet will replaced. For example as made in this solution applied for gmail templates: https://developers.google.com/gsuite/solutions/mail-merge

 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');
  };

Thank you

Answer 1

The replace() you are trying to use in the document body is a Javascript method used for replacing string. Google Docs has replaceText method which you can use to replace string within the document without changing the document format.

If you want to use the replace(). You need to get the content of body (string) by using method:getText() and append the replace method.

Example:

Code:

function myFunction() {
  var data = { 'name': 'John Doe', 'sport': 'Basketball', 'nba_player': 'Kobe Bryant'} 
  var doc = DocumentApp.getActiveDocument();
  var body = doc.getBody();
  var text = body.getText();
  text = text.replace(/{{[^{}]+}}/g, key => {
      return escapeData_(data[key.replace(/[{}]+/g, "")] || "");
    });
  body.clear();
  body.setText(text);
}

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');
  };

Output:

Reference:

Javascript String:replace()