title: AutoFill Google Docs From Spreadsheet
body: <p>I'm studding the solution proposed in this site:
  <a href="https://jeffreyeverhart.com/2020/09/29/auto-fill-a-google-doc-template-from-google-sheet-data/" rel="nofollow noreferrer">https://jeffreyeverhart.com/2020/09/29/auto-fill-a-google-doc-template-from-google-sheet-data/</a>
  Especially I'm interesting in the replacement of tokens with data from a spreadsheet.</p>
  <p>But I would like a more flexible solution. In this examples the tokens are defined in the code:</p>
  <pre><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]);
  </code></pre>
  <p>I would like that all tokens available in the spreadsheet will replaced. For example as made in this solution applied for gmail templates:
  <a href="https://developers.google.com/gsuite/solutions/mail-merge" rel="nofollow noreferrer">https://developers.google.com/gsuite/solutions/mail-merge</a></p>
  <pre><code> 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 =&gt; {
        return escapeData_(data[key.replace(/[{}]+/g, &quot;&quot;)]) || &quot;&quot;;
      });
      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(/[\&quot;]/g, '\\\&quot;')
        .replace(/[\/]/g, '\\/')
        .replace(/[\b]/g, '\\b')
        .replace(/[\f]/g, '\\f')
        .replace(/[\n]/g, '\\n')
        .replace(/[\r]/g, '\\r')
        .replace(/[\t]/g, '\\t');
    };
  </code></pre>
  <p>Thank you</p>