javascript - How to return a value in the only non-blank column in a range for sendEmail function in Google App Scripts -
the issue is: can't find way return value 'course' because each form submission generates new row name of course spread on columns e m (column 4 through 12).
in each row, there 1 'course' name in 1 of columns e m (e.g in f) , other columns blank. (users can select 1 course , other columns blank. have categorize courses 9 columns because of page breaks in order split sheer number of options users select course from.) how return value of non blank cell e m entered in email ?
i advised insert entire findcourse function inside of sendemail function before of other code. did have still been receiving failure notifications of google app scripts: typeerror: cannot read property "values" undefined. (line 14, file "code") (referring var value = e.values[i])
the full code below:
function sendemail(e) { function findcourse (e){ var coursetotake; //loop through values ( var = 4; <=12; ++){ //pull value variable var value = e.values[i]; if (value != undefined){ //if find actual string value, set course take variable coursetotake = value; } } return coursetotake; } var name = e.namedvalues["full name appear in nric"]; var course = findcourse(); var start = e.values[14]; var end = e.values[15]; var starttime = e.values[24]; var endtime = e.values[25]; var details = e.values[13]; var cost = e.values[17]; var email = e.values[18]; var roname = e.values[19]; var roemail = e.values[20]; var location = e.values[23]; var subject = "training approval request " + course; var message = "<p >" + "dear " + roname + "<p />" + name + " seeks approval attend " + course + ". details follow:" + "<p >" + "<b>date:</b> " + start + " - " + end + " <br />" + "<b>time:</b> " + starttime + " - " + endtime + " <br />" + "<b>location:</b> " + location + " <br />" + "<b>course objectives , benefits:</b> " + details + " <br />" + "<b>course fees:</b> " + "$" + cost + " <br />" + "<p />" + "please reply directly email approval or if have questions/comments. thank you. " mailapp.sendemail(roemail, email, subject, message); } after rearranging findcourse own function: sorry if made mistakes here i'll try best follow suggestions. if i've added in logger.log(e) correctly, both functions seem undefined
function sendemail(e) { logger.log(e); var name = e.values[2]; var course = findcourse(); var start = e.values[14]; var end = e.values[15]; var starttime = e.values[24]; var endtime = e.values[25]; var details = e.values[13]; var cost = e.values[17]; var email = e.values[18]; var roname = e.values[19]; var roemail = e.values[20]; var location = e.values[23]; var subject = "training approval request " + course; var message = "<p >" + "dear " + roname + "<p />" + name + " seeks approval attend " + course + ". details follow:" + "<p >" + "<b>date:</b> " + start + " - " + end + " <br />" + "<b>time:</b> " + starttime + " - " + endtime + " <br />" + "<b>location:</b> " + location + " <br />" + "<b>course objectives , benefits:</b> " + details + " <br />" + "<b>course fees:</b> " + "$" + cost + " <br />" + "<p />" + "please reply directly email approval or if have questions/comments. thank you. " mailapp.sendemail(roemail, email, subject, message); } function findcourse (e){ var coursetotake; //loop through values ( var = 4; <=12; ++){ //pull value variable var value = e.values[i]; if (value != undefined){ //if find actual string value, set course take variable coursetotake = value; } } return coursetotake; var coursetotake = findcourse(e); logger.log(e); } i appreciate or alternative solutions here.
thank you!
what changed in code address question:
- i assigned
onformsubmittriggersendemailfunction event object no longer undefined - i added call
findcourse()coursevariable no longer undefined - i fixed undefined check changing
if(value != undefined)if(typeof value !== 'undefined') - i added check blank value (this important bit in logic after faulty undefined check)
if(value != '')
explanation:
to trigger event, installable trigger needs setup on form submit event points sendemail function. can found in resources -> current project triggers
to retrieve course, need call function findcourse() , pass in e event object. example: var course = findcourse(e);. assign return value findcourse(e); course variable. can use variable normal within rest of statements.
when checking undefined, need use typeof , check string of 'undefined', or check ironically throw undefined exception.
the values of form submit should not undefined, blank values should blank strings. checking non-blank strings necessary course name values array.
fixed code:
function sendemail(e) { logger.log(e) var course = findcourse(e); var name = e.values[19]; var start = e.values[12]; var end = e.values[14]; var starttime = e.values[13]; var endtime = e.values[15]; var details = e.values[11]; var cost = e.values[17]; var email = e.values[20]; var roname = e.values[21]; var roemail = e.values[22]; var location = e.values[16]; var subject = "training approval request " + course; var message = "<p >" + "dear " + roname + "<p />" + name + " seeks approval attend " + course + ". details follow:" + "<p >" + "<b>date:</b> " + start + " - " + end + " <br />" + "<b>time:</b> " + starttime + " - " + endtime + " <br />" + "<b>location:</b> " + location + " <br />" + "<b>course objectives , benefits:</b> " + details + " <br />" + "<b>course fees:</b> " + "$" + cost + " <br />" + "<p />" + "please reply directly email approval or if have questions/comments. thank you. " mailapp.sendemail(roemail, email+";" + "redactedemail", subject, message); } function findcourse (e){ var coursetotake; //loop through values ( var = 2; <=10; ++){ //pull value variable var value = e.values[i]; if (typeof value !== 'undefined'){ //if value defined if(value != ''){ //if value not blank //if find actual non-blank string value, set course take variable coursetotake = value; } } } return coursetotake; }
Comments
Post a Comment