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
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.
Thank you for the help!
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 theif..else if
statement conditions are met.Then, create test functions for each scenario, i.e.
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.