-1

complete noob, and my first ever post,so sorry in advance for the eventual poor choice of words.

I am working on a mail merge script, that will fill a GDoc template with data from a GSheet, creating a separate GDoc for each row in GSheet.

Script is working well, I'm using the .replacetext method on the template's body, like below:

function createNewGoogleDocs() {
      const documentLink_Col = ("Document Link");
      const template = DriveApp.getFileById('1gZG-NR8CcOpnBTZfTy8gEsGDOLXa9Ba9Ks5zXJbujY4');
      const destinationFolder = DriveApp.getFolderById('1DcpZGeyoCJxAQu1vMbSj31amzpwfr_JB');
      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) 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 change:

Have freedom to add/remove columns in the sheet without having to hard code every header column with a .replacetext method

I have found a kind of similar script that achieves that for sending emails based on GmailApp, and I extracted 2 functions that do a token replacement, but I don't know how to call the function fillInTemplateFromObject_ in my function createNewGoogleDocs

here is the code for the functions I found in the other script:

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

Thanks everyone in advance for your support.

Cristian M
  • 23
  • 4
  • You can assign name to each column, then you can simply enumerate these columns, and use its name for your template tag. – vanowm Apr 24 '22 at 23:52

1 Answers1

0

Using column headers to make programmatic assignments

function myfunction() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("Sheet0");
  const [hA, ...vs] = sh.getDataRange();// hA is Name Address City
  const idx = {};
  const body = DocumentApp.getActiveDocument().getBody();
  hA.forEach((h, i) => { idx[h] = i; })
  vs.forEach(row => {
    body.replaceText("{{Name}}", row[idx["Name"]]);
    body.replaceText("{{Address}}", row[idx["Address"]]);
    body.replaceText("{{City}}", row[idx["City"]]);
  });
}

As long as you keep the column titles the same you can move them around anywhere you wish

Cooper
  • 59,616
  • 6
  • 23
  • 54
  • Thanks for sharing, but that solves just half of I'm trying to achieve. I want to also be able to rename, delete, and add new headers. – Cristian M Apr 25 '22 at 10:17
  • I've edited my post, added my full code that is working, and a suggestion for what might work. Can you please let me know if it's the right approach? – Cristian M Apr 25 '22 at 12:07
  • Yeah but your initial question was how to utilize the headers and you only get one question. You can ask another question. – Cooper Apr 25 '22 at 14:21
  • This is untouched in my original post: "What I want to change: Have freedom to add/remove columns in the sheet without having to hard code every header column with a .replacetext method" – Cristian M Apr 25 '22 at 14:27
  • Perhaps you can be more clear next time – Cooper Apr 25 '22 at 14:30
  • Keep the questions well focused – Cooper Apr 25 '22 at 14:31
  • I will, thanks for the suggestion. What can I do now, so you can help me, please? Is it recommended reposting a VERY similar question? Or can you answer directly on this one? – Cristian M Apr 25 '22 at 14:37
  • I guess what actually happened is that you haven't read the question through, because, as I said, it is unchanged with my edit. But thank you nevertheless for your support. What I don't understand is why did my question got a downvote. – Cristian M Apr 25 '22 at 15:28