JavaScript Use RegEx to replace tags in document with column data from spreadsheet regex replace tag html,regex replace tags,regex replace html

I've been searching for the answer to this question but have so far been unable to piece together the answer. Please explain any answer you have in really simple terms as I'm fairly new to GAS and RegEx. I've got most of the syntax down but the execution of it in GAS is giving me a hard time.

Basically, I want to write a script that, when the spreadsheet is edited, checks which rows have yet to be merged. Then, on those rows, creates a copy of a template Google Doc and names the document based on the spreadsheet data. From there (this is the hard part), I need it to replace merge tags in the template with the data from the spreadsheet.

The tags in the templates I'll be using look like this: <<mergeTag>>

My idea was to match the whole tag, and replace it with data from the spreadsheet that exists in the column with the same name as what's inside the "<<>>". Ex: <<FooBar>> would be replaced with the data from the column named FooBar. It would obviously be from the current row that needs the merging.

After that, all that's left is to send an email (a few more row-specific personalization) with that document attached (sometimes as a PDF) with the body of the message coming from an HTML file elsewhere in the project.

This is the whole thing I have so far (notice the placeholders here and there that I can personalize for each spreadsheet I use this for):

function onEdit() {
  //SPREADSHEET GLOBAL VARIABLES

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  //get only the merge sheet
  var sheet = ss.getSheetByName("Merge Data");
  //get all values for later reference
  var range = sheet.getActiveRange();
  var values = range.getValues();
  var lastRow = range.getLastRow();
  var lastColumn = range.getLastColumn();

  //get merge checker ranges
  var urlColumn = range.getLastColumn();
  var checkColumn = (urlColumn - 1);
  var checkRow = range.getLastRow();
  var checkRange = sheet.getRange(2, checkColumn, checkRow);
  var check = checkRange.getBackgrounds();

  //get template determination range (unique to each project)
  var tempConditionRange = sheet.getRange(row, column);
  var tempConditionCheck = tempConditionRange.getValues();

  //set color variables for status cell
  var red = "#FF0000";
  var yellow = "#FFCC00";
  var green = "#33CC33";

  //////////////////////////////////////////////////////////
  //DOC GLOBAL VARIABLES

  var docTemplate1 = DriveApp.getFileById(id);
  var docTemplate2 = DriveApp.getFileById(id);
  var docTemplate3 = DriveApp.getFileById(id);
  var folderDestination = DriveApp.getFolderById(id);

  //////////////////////////////////////////////////////////
  //EMAIL GLOBAL VARIABLES
  var emailTag = ss.getRangeByName("Merge Data!EmailTag");
  var personalizers = "";
  var subject = "" + personalizers;
  var emailBody = HtmlService.createHtmlOutputFromFile("Email Template");

  //////////////////////////////////////////////////////////
  // MERGE CODE

  for (i = 0; i < check.length; i++) {
    //for rows with data, check if they have already been merged
    if (check[i] == green) {
       continue;
    } else {
      var statusCell = sheet.getRange((i+2), checkColumn, 1, 1);
      var urlCell = sheet.getRange((i+2), urlColumn, 1, 1);
      var dataRow = sheet.getRange((i+2), 1, lastRow, (lastColumn - 2))
      statusCell.setBackground(red);
      //for rows with data, but not yet merged, perform the merge code

  //////////////////////////////////////////////////////////
  //DOC CREATION

  //Determine which template to use
  if (tempConditionCheck[i] == "") {
    var docToUse = docTemplate1;
  }
  if (tempConditionCheck[i] == "") {
    var docToUse = docTemplate2;
  }
  if (tempConditionCheck[i] == "") {
    var docToUse = docTemplate3;
  }

  //Create a copy of the template
  //Rename the document using data from specific columns, at specific rows
  //Move the doc to the correct folder
  var docName = "";
  var docCopy = docToUse.makeCopy(docName, folderDestination);
  var docId = docCopy.getId();
  var docURL = docCopy.getUrl();
  var docToSend = DriveApp.getFileById(docId);
  var docBody = DocumentApp.openById(docId).getBody();

Here's where I need the help

  //Locate the Merge Tags
  //Match Merge Tags to the column headers of the same name
  //Replace the Merge Tags with the data from the matched column, from the correct row
  function tagReplace() {
    var tagMatch = "/(<{2}(\w+)>{2})/g";
  }


  statusCell.setBackground(yellow);
  urlCell.setValue(docURL);

The rest is just finishing up the process

      //////////////////////////////////////////////////////////
      //EMAIL CREATION

      //Create an email using an HTML template
      //Use Merge Tags to personalize email
      //Attach the doc we created to the email
      //Send email to recipients based on data in the sheet
      MailApp.sendEmail(emailTag, subject, emailBody, {
        name: "Person McPerson",
        attachments: [docToSend], //[docToSend.getAs(MIME.PDF)],
        html: emailBody,
      });

      //////////////////////////////////////////////////////////
      //CHECK ROW UPDATE
      statusCell.setBackground(green);

    }
  }
}

My sheets all have a frozen first row that acts as the header row. All my columns will be consistently named the exact same thing as the tags (minus the <<>>).

How do I match the tags to the data?

EDIT
```````````````````
The solution did not work as described when I inserted it into my code as follows:

function formMerge() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Merge Data");
  var urlColumn = sheet.getMaxColumns();
  var checkColumn = urlColumn - 1;
  var lastRow = ss.getSheetByName("Form Responses").getLastRow();
  var values = sheet.getDataRange().getValues();
  var headers = values[0];

  var urlRange = sheet.getRange(2, urlColumn, lastRow);
  var checkRange = sheet.getRange(2, checkColumn, lastRow);
  var check = checkRange.getBackgrounds();

  var red = "#ff0404";
  var yellow = "#ffec0a";
  var green = "#3bec3b";

  var docTemplate = DriveApp.getFileById(id);
  var folderDestination = DriveApp.getFolderById(id);

  // MERGE CODE
  for (i = 0; i < check.length; i++) {
    if (check[i] == green) {
      continue;
    } else {
      var statusCell = sheet.getRange((i+2), checkColumn, 1, 1);
      var urlCell = sheet.getRange((i+2), urlColumn, 1, 1);
      var dataRow = sheet.getRange((i+2), 1, 1, (urlColumn - 2)).getValues();
      var clientNameRange = sheet.getRange((i+2), 3);
      var clientName = clientNameRange.getValue();
      var dateRange = sheet.getRange((i+2), 2);
      var datePreFormat = dateRange.getValue();
      var timeZone = CalendarApp.getTimeZone();
      var date = Utilities.formatDate(new Date(datePreFormat), timeZone, "MM/dd/yyyy");
      statusCell.setBackground(red);

      //EMAIL VARIABLES
      var personalizers = clientName;
      var subject = "Post Intake Report for " + personalizers;
      var emailBody = "Please see the attached Google Doc for the Post Intake Report for " + clientName + ". The intake was performed on " + date + ".";
      var emailTagRange = sheet.getRange((i+2), 24);
      var emailTagValue = emailTagRange.getValue();
      var emailTag = emailTagValue.split(", ");

      //DOC CREATION
      var docToUse = docTemplate;
      var docName = "Post Intake Report - " + clientName + " [" + date + "]";
      var docCopy = docToUse.makeCopy(docName, folderDestination);
      var docId = docCopy.getId();
      var docURL = docCopy.getUrl();
      var docBody = DocumentApp.openById(docId).getBody().editAsText();

      for (var j=0; j<headers.length; j++) {
        var re = new RegExp("(<<"+headers[j]+">>)","g");
        docBody.replaceText(re, dataRow[j]);
      }
      statusCell.setBackground(yellow);
      urlCell.setValue(docURL);

      //EMAIL CREATION
      MailApp.sendEmail(emailTag, subject, emailBody, {
        name: "Christopher Anderson",
        attachments: [docCopy],
        html: emailBody
      });
      statusCell.setBackground(green);
    }
  }
}
Answer:1

My first time posting on here so hopefully I do this correctly! Here's sample code: $(document).ready(function() { $('[data-toggle=offcanvas]').click(function() { $('.row-...

My first time posting on here so hopefully I do this correctly! Here's sample code: $(document).ready(function() { $('[data-toggle=offcanvas]').click(function() { $('.row-...

OK, so I'm trying to take an image on my machine and manipulate it using a canvas and some JS... However, I keep getting the error: "Image from origin 'file://' has been blocked from loading by ...

OK, so I'm trying to take an image on my machine and manipulate it using a canvas and some JS... However, I keep getting the error: "Image from origin 'file://' has been blocked from loading by ...

I am trying to convert textbox value on onblur event to a $ currency using jquery.formatCurrency-1.4.0.js and my javascript function looks as follows : $(document).ready(function() { $('.currency'...

I am trying to convert textbox value on onblur event to a $ currency using jquery.formatCurrency-1.4.0.js and my javascript function looks as follows : $(document).ready(function() { $('.currency'...

  1. formatcurrency function in vb.net
  2. formatcurrency function access
  3. formatcurrency function r
  4. formatcurrency function in ssrs
  5. formatcurrency function in vbscript
  6. format currency function in sql server
  7. angular formatcurrency function example
  8. access formatcurrency function example
  9. angular formatcurrency function
  10. vba format currency function
  11. ms access formatcurrency function

I am trying to implement a function f so that when called like the following: (((f 1) 2) 3) () It returns the sum of 1, 2, and 3, which is 6. Only when it's invoked with a Unit argument () that the ...

I am trying to implement a function f so that when called like the following: (((f 1) 2) 3) () It returns the sum of 1, 2, and 3, which is 6. Only when it's invoked with a Unit argument () that the ...

  1. function with invocation
  2. function invocation in javascript
  3. function invocation in c
  4. function invocation in python
  5. function invocation in js
  6. invoke function with parameters unity
  7. invoke function with parameters c#
  8. invoke function with parameters
  9. invoke function with parameters powershell
  10. function invoke in javascript
  11. function invoke in c++
  12. function invoke in php
  13. function invoke on java
  14. invoke function in unity
  15. invoke function in power bi
  16. invoke function in python
  17. invoke function in shell script
  18. invoke function in powershell script
  19. invoke function in kotlin
  20. invoke function by name javascript