2

I am making a merge code to take data from my spreadsheet and populate merge tags in a Google Doc. The part of the code I am unable to write correctly is the part that writes the tags back to the Google Doc. I have been able to locate the tags but the code doesn't replace them.

Here is what I've written so far.

function mergeApplication() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Merge Data");
  var range = sheet.getActiveRange();
  var formSheet = ss.getSheetByName("Form Responses");
  var lastRow = formSheet.getLastRow();
  var lastColumn = sheet.getMaxColumns();

  function checkAndComplete() {
    var urlColumn = lastColumn;
    var checkColumn = (urlColumn - 1);
    var checkRange = sheet.getRange(2, checkColumn, (lastRow - 1), 1);
    var check = checkRange.getBackgrounds();
    var red = "#ff0404";
    var yellow = "#ffec0a";
    var green = "#3bec3b";
    for (var 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, (lastColumn - 2));

        function mergeTasks() {
          function docCreator() {
//            var templateConditionRange = sheet.getRange((i+2), column);
//            var templateConditionCheck = templateConditionRange.getValues();
            var docTemplate1 = DriveApp.getFileById(id);
            //          var docTemplate2 = DriveApp.getFileById(id);
            //          var docTemplate3 = DriveApp.getFileById(id);
            var folderDestination = DriveApp.getFolderById(id);
            var clientName = sheet.getRange((i+2), 3).getValue();
            var date = sheet.getRange((i+2), 1).getValue();
            //          if (templateConditionCheck[i] == "") {
            var docToUse = docTemplate1;
            //          }
            //          if (templateConditionCheck[i] == "") {
            //            var docToUse = docTemplate2;
            //          }
            //          if (templateConditionCheck[i] == "") {
            //            var docToUse = docTemplate3;
            //          }
            var docName = "Merge Tester Doc for " + clientName + " [" + date + "]";
            var docCopy = docToUse.makeCopy(docName, folderDestination);
            var docId = docCopy.getId();
            var docURL = DriveApp.getFileById(docId).getUrl();
            var docToSend = DriveApp.getFileById(docId);
            var docBody = DocumentApp.openById(docId).getBody().getText();

            function tagReplace() {
              var taggedArray = [docBody.match(/\<{2}[\w\d\S]+\>{2}/g)];
              var headerArray = [sheet.getRange(1, 1, 1, (lastColumn - 2)).getValues()];
              var dataArray = [dataRow.getValues()];
              var strippedArray = [];
              Logger.log("The preliminary length of taggedArray is " + taggedArray.length);
              Logger.log(taggedArray);

              function tagStrip() {
                for (var t = 0; t < taggedArray.length; t++) {
                  var strippedString = taggedArray[t].slice(2, -3).toString();
                  strippedArray.push(strippedString);
                  Logger.log("The current strippedArray length is " + strippedArray.length);
                }
                Logger.log("The final strippedArray length is " + strippedArray.length);
                Logger.log("The final taggedArray length is " + taggedArray.length);
                Logger.log("The final, completed strippedArray is " + strippedArray);
              }

              function dataMatch() {
                for (var s = 0; s < strippedArray.length;) { 
                  for (var h = 0; h < headerArray.length;) {
                    if (strippedArray[s] == headerArray[h]) {
                      docBody.replaceText(taggedArray[s].String(), dataArray[h].String());
                      h=0;
                      s++;
                    } else {
                      h++;
                    }
                  }
                }
              }
              tagStrip;
              dataMatch;
            }

            function emailCreator() {
              var emailTag = sheet.getRange((i+2), (urlColumn - 2)).getValue();
              var emailBody = HtmlService.createHtmlOutputFromFile("Email Template").getContent();
              var personalizers = clientName + " [" + date + "]";
              var subject = "Merge Tester Email for " + personalizers;
              MailApp.sendEmail(emailTag, subject, emailBody, {
                name: "Christopher Anderson",
                attachments: [docToSend],
                html: emailBody,
              });
            }
            tagReplace();
            statusCell.setBackground(yellow);
            emailCreator();
            urlCell.setValue(docURL)
          }
          statusCell.setBackground(red);
          docCreator();
          statusCell.setBackground(green);
        }
        mergeTasks();
      }
    }
  }
  checkAndComplete();
}

The problem section is here:

 function tagReplace() {
              var taggedArray = [docBody.match(/\<{2}[\w\d\S]+\>{2}/g)];
              var headerArray = [sheet.getRange(1, 1, 1, (lastColumn - 2)).getValues()];
              var dataArray = [dataRow.getValues()];
              var strippedArray = new Array();
              Logger.log("The preliminary length of taggedArray is " + taggedArray.length);
              Logger.log(taggedArray);
              function tagStrip() {
                for (var t = 0; t < taggedArray.length; t++) {
                  var strippedString = taggedArray[t].slice(2, -3).toString();
                  strippedArray.push(strippedString);
                  Logger.log("The current strippedArray length is " + strippedArray.length);
                }
                Logger.log("The final strippedArray length is " + strippedArray.length);
                Logger.log("The final taggedArray length is " + taggedArray.length);
                Logger.log("The final, completed strippedArray is " + strippedArray);
              }
              function dataMatch() {
                for (var s = 0; s < strippedArray.length;) { 
                  for (var h = 0; h < headerArray.length;) {
                    if (strippedArray[s] == headerArray[h]) {
                      docBody.replaceText(taggedArray[s].String(), dataArray[h].String());
                      h=0;
                      s++;
                    } else {
                      h++;
                    }
                  }
                }
              }
              tagStrip;
              dataMatch;
            } 

It doesn't even log anything having to do with strippedArray. It seems to be skipping over that section entirely. Am I using the correct method of completing this task and/or is there a simpler way of doing it?

It's worth mentioning that my tags in the doc have 2 "<>" around them. That's the reason for my RegEx looking how it does. Also, when logging the .length of taggedArray, it returns a value of 1.

4
  • Did you run the code through the debugger? could it be that taggedArray.length is 0? Commented Dec 21, 2015 at 22:59
  • The debugger comes back clean. When I log taggedArray, it comes back with .length of 1. It doesn't log anything having to do with strippedArray. Commented Dec 21, 2015 at 23:07
  • Out of curiosity could you use the body.replaceText() function? It might be more reliable. developers.google.com/apps-script/reference/document/… Commented Dec 22, 2015 at 0:54
  • The syntax is a little more difficult to figure out because I have to take off the <<>> and match it to data in my spreadsheet. I originally was using it but each iteration is different so this seemed like the simplest way. If you can envision using that one in an easier way, please include some sample code so I can visualize how it would work. Commented Dec 22, 2015 at 1:24

2 Answers 2

1

You never actually call tagStrip which is supposed to work on strippedArray.
You declare it with function tagStrip(){} and later you reference the function with tagStrip; but you never actually call it. The same is happening with dataMatch.

Try calling the two functions by writing

tagStrip();
dataMatch();

If you don't include the parentheses you don't call it you just run the function Objects as statements.

Sign up to request clarification or add additional context in comments.

1 Comment

Oh...wow...I feel pretty silly now. Thank you. I'm going to try this right away. I'll close out this question and ask a different one if a new issue arises. Good catch.
0

Here is a portion of code I use in my add-on Simply Send, I use the same <<>> merge tags.

//copy template
  var mDocDrive = doc.makeCopy(title, folder);
  var mDoc = DocumentApp.openById(mDocDrive.getId());
  var mDocDriveApp = DriveApp.getFileById(mDoc.getId());
  var docToAttach = mDoc.getUrl();
  var driveToShare = mDocDriveApp;

  // replace text inside template
  var body = mDoc.getBody();
  body.replaceText("<<SS Title>>", title);
  body.replaceText("<<timestamp>>", lastR.timestamp);
  body.replaceText("<<username>>", lastR.email);

  for (i in lastR.theResponses){
    var cur = lastR.theResponses[i];
    var name = cur.title;
    name = name.replace(/[\-\[\]\/\{\}\(\)\*\+\?\.\\\^\$\|]/g, "\\$&"); // this will allow fields that include special characters.
    var response = cur.response;
    var searchPattern = "<<"+name+">>";
    body.replaceText(searchPattern, response);
  }

  // this will replace any unused tags with nothing.
  var ques = getQuestionList();
  for (j in ques){
    var curq = ques[j].name;
    curq = curq.replace(/[\-\[\]\/\{\}\(\)\*\+\?\.\\\^\$\|]/g, "\\$&");
    var searchPattern2 = "<<"+curq+">>";
    body.replaceText(searchPattern2, "");
  }

  mDoc.saveAndClose();



  //create pdf  
  if (mmDefaults.shareAs == "pdf"){
    // uncomment if you want to make the pdf in the merge folder
    var asPdf = mDoc.getAs('application/pdf');
    asPdf.setName(mDoc.getName()+ ".pdf");
    var pdf = DriveApp.createFile(asPdf);
    folder.addFile(pdf);
    DriveApp.removeFile(pdf);
    mDocDriveApp.setTrashed(true);
    var docToAttach = pdf;
    driveToShare = pdf;
  }

2 Comments

This is pretty good. There seems to be redundancies in what you're looking for. You have some specific describers and then two loops to catch situations that could happen. Why not just use one loop to catch all the tags? Even if your template has tags that the sheet doesn't have data for, wouldn't it still get filled with ""? Since it would read as an empty cell and default to ""?
It is because I am using the responses from the form, not the sheet. If a user does't answer a question, the form response doesn't exist, vs it being just blank when pulling from the sheet. So I added the extra loop to remove any form merge tags of questions that the person didn't answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.