skip to Main Content

I have a form which will generate a letter once submitted based on the Google form submission.

One of the questions has three outcomes and depending on the entry, it will delete either one line, or both lines. I have success with the first two responses deleting their respective lines, but the final response requires both lines to be deleted and when I try this, neither is deleted.

Ideally, the script will eventually identify if the balance is positive, negative or nil based on the numeric value that has been submitted. Once the text has been applied, it will need to enter the number into the new text.

This is the current code, it deletes the text if ‘Positive’ or ‘Negative’ is selected, but when ‘Nil’ is, nothing happens.

EDIT:
I tested the first two responses, successful.
I tested ‘Null’ with only one deletion command deleteTextIfFound(body, searchText2) but this was unsuccessful
The spelling and formatting of ‘Null’ is the same as the form, I’m not sure why this command leads to no action. No errors when running the script.

function autoFillGoogleDocFromForm(e) {
  if (!e || !e.response) {
    return;
  }
  var formResponse = e.response;
  var itemResponses = formResponse.getItemResponses();
  var personFirstName = itemResponses[0].getResponse();
  var personSurname = itemResponses[1].getResponse();
  var date = itemResponses[2].getResponse();
  var whatIsTheClosingBalance = itemResponses[3].getResponse();
  var templateFile = DriveApp.getFileById("FILEID");
  var templateFolder = DriveApp.getFolderById("FOLDERID");
  var copy = templateFile.makeCopy(customerFirstName, templateFolder);
  var doc = DocumentApp.openById(copy.getId());
  var body = doc.getBody();
  body.replaceText("{{FIRST NAME}}", personFirstName);
  body.replaceText("{{SURNAME}}", personSurname);
  body.replaceText("{{DATE}}", date);
  var searchText1 = "BODY OF TEXT 1.";
  var searchText2 = "BODY OF TEXT 2";
  
  // Delete text based on whatIsTheClosingBalance
  if (whatIsTheClosingBalance == "Positive") {
    deleteTextIfFound(body, searchText1);
  } else if (whatIsTheClosingBalance == "Negative") {
    deleteTextIfFound(body, searchText2);
  } else if (whatIsTheClosingBalance == "Nil") {
    deleteTextIfFound(body, searchText1);
    deleteTextIfFound(body, searchText2);
  }
  doc.saveAndClose();
}
function deleteTextIfFound(body, searchText) {
  var searchResult = body.findText(searchText);
  while (searchResult) {
    var startOffset = searchResult.getStartOffset();
    var endOffset = searchResult.getEndOffsetInclusive();
    searchResult.getElement().asText().deleteText(startOffset, endOffset);
    // Find next occurrence
    searchResult = body.findText(searchText, searchResult);
  }
}

2

Answers


  1. Chosen as BEST ANSWER

    I apologise for the ask being too vague. I changed the conditions from "Positive/Negative" to a numerical value and while this had the same problem for many attempts, adding a space before the bracket of this line "else if (whatIsTheClosingBalance === 0 )" solved the issue.

      if (whatIsTheClosingBalance < 0) {
        deleteTextInstances(body, searchText2);
      } else if (whatIsTheClosingBalance > 0) {
        deleteTextInstances(body, searchText1);
      } else if (whatIsTheClosingBalance === 0 ) {
        deleteTextInstances(body, searchText1);
        deleteTextInstances(body, searchText2);
    

    Thank you for the help!


  2. It’s unclear what the Google Doc content is and how the tests are run. Still, I think you might need to improve your script to make it easier to debug.

    Start by splitting the responsibilities of the trigger’s handler function. I suggest you create a function to create a copy of the Google Document and the other related tasks.

    Also, add an else clause to make it easier to notice when none of the if..else if statement conditions are met.

    function documentHandler(params){
      const [
              customerFirstName, 
              templateFolder, 
              personFirstName, 
              personSurname,
              date,
              whatIsTheClosingBalance
            ] = params;
      var templateFile = DriveApp.getFileById("FILEID");
      var templateFolder = DriveApp.getFolderById("FOLDERID");
      var copy = templateFile.makeCopy(customerFirstName, templateFolder);
      var doc = DocumentApp.openById(copy.getId());
      var body = doc.getBody();
      body.replaceText("{{FIRST NAME}}", personFirstName);
      body.replaceText("{{SURNAME}}", personSurname);
      body.replaceText("{{DATE}}", date);
      var searchText1 = "BODY OF TEXT 1.";
      var searchText2 = "BODY OF TEXT 2";
      
      // Delete text based on whatIsTheClosingBalance
      if (whatIsTheClosingBalance == "Positive") {
        deleteTextIfFound(body, searchText1);
      } else if (whatIsTheClosingBalance == "Negative") {
        deleteTextIfFound(body, searchText2);
      } else if (whatIsTheClosingBalance == "Nil") {
        deleteTextIfFound(body, searchText1);
        deleteTextIfFound(body, searchText2);
      } else {
        Logger.log('Do nothing');
      }
    
      doc.saveAndClose();
    
    }
    

    Then, create test functions for each scenario, i.e.

    function test_1(){
       const personFirstName = "value 1";
       const personSurname = "value 2";
       const date = "value 3";
       const customerFirstName = "value 4"; 
       const templateFolder = "value 5"; 
       const whatIsTheClosingBalance = "value 6";
       const params = [
              customerFirstName, 
              templateFolder, 
              personFirstName, 
              personSurname,
              date,
              whatIsTheClosingBalance
            ] = params;
       documentHandler(params);
    }
    

    You can use the Google Apps Script debugger to troubleshoot the control flow and tasks using the Documents and Drive Services (DocumentApp and DriveApp) for each scenario.

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search