google apps script - Email from spreadsheet Javascript: How to make message equal more than one column? -
a colleague , using google forms collect short essays students. info written spreadsheet. want type feedback spreadsheet , have script email each pupil re-create of essay , feedback.
google provides next code (source: email code):
we have figured out how increment number of rows script reads spreadsheet business relationship our class sizes.
what can't figure out how create message equal both essay (in 1 column) , feedback (in column). need alter in code create happen?
here's link demo spreadsheet , script. create re-create of spreadsheet , test out code.
test script highlighting 1 or more rows of info in spreadsheet , running emailfeedback function script editor. or, 1 or more rows selected can execute script "custom tools" menu in spreadsheet.
i split 2 functions. first function emailfeedback gets rows selected in spreadsheet , loops through each row of info , calls sec function sendemail each row, provided there feedback , text (more validation , error handling added).
function emailfeedback() { var ss = spreadsheetapp.getactivesheet(); var range = ss.getactiverange(); var numrows = range.getnumrows(); var values = range.getvalues(); (var = 0; <= numrows - 1; i++) { var row = values[i]; var = row[1]; var story = row[2]; var feedback = row[3]; if (feedback.length > 0 && story.length > 0) { // create sure valid story , feedback before sending sendemail(to, story, feedback); } } }; gmailapp used send email html formatting suggested igor in comments above. plaintxtbody can omitted, perhaps useful if recipient can't parse html formatted email. storyhtml , feedbackhtml replace newline characters user form submissions <p> tags proper spacing in email.
function sendemail(to, story, feedback) { var sendtoname = to.split('@')[0]; // email content var emailsubject = "feedback on story submission"; // plain text body - in case receiver can't parse html formatted email var plaintxtbody = "hi " + sendtoname + ",\n" + "you wrote:\n" + story + "\n" + "our feedback:\n" + feedback; // html formatting isn't necessary, nice reading :) var htmlbody = '<html><body>'; var htmlfooter = '</body></html>'; // replacing newline characters paragraph breaks create more readable var storyhtml = story.replace(/\n/g, "</p><p>"); var feedbackhtml = feedback.replace(/\n/g, "</p><p>"); var emailmessage = "<p>hi " + sendtoname + ",</p>" + "<p><strong>you wrote:</strong><p>" + "<p>" + storyhtml + "</p>" + "<hr>" + "<p><strong>here our feedback:</strong></p>" + "<p>" + feedbackhtml + "</p>"; htmlbody += emailmessage + htmlfooter; // gmailapp must have default recipient, subject, body attributes followed jsobject options {} // differs mailapp syntax has more options -- see documentation on gmailapp gmailapp.sendemail(to, emailsubject, plaintxtbody, { htmlbody: htmlbody, }); }; hope helpful :)
javascript google-apps-script google-spreadsheet
No comments:
Post a Comment