i totally new coding , need on bit of code. here problem: want take many cells of data in google spreadsheet , send in email. figured out how pull data email , email sends, however, data separated commas , hard distinguish. how can make each cell's data appear on separate line in email? believe can arrays, said, new , cannot figure out how have read far. if provide explanation, appreciated - will, hopefully, keep me asking same question again , teach me. thank patience , help!
function emaildata() { //gather data "responses" sheet var ss = spreadsheetapp.getactivespreadsheet(); var responses = ss.getsheetbyname("form responses"); var lastrow = responses.getlastrow(); var data = responses.getrange("a"+(lastrow)+":ak"+(lastrow)); var values = data.getvalues(); //gather email addresses "email" sheet (i have not gotten far yet) var email = ss.getsheetbyname("email"); var startrow = 2; var emailadress = "email@email.com"; var subject = "capacity campaign contact form"; mailapp.sendemail(emailadress,subject,values); }
your array (called values) 2 dimension array corresponding row in sheet, means have iterate through first element. wrote small script function composes message headers , values :
function test(){ var ss = spreadsheetapp.getactivespreadsheet(); var responses = ss.getsheetbyname("form responses"); var lastrow = responses.getlastrow(); var values = responses.getrange("a"+(lastrow)+":ak"+(lastrow)).getvalues();// range , values in 1 step var headers = responses.getrange("a1:ak1").getvalues();// same headers var message = composemessage(headers,values);// call function 2 arrays arguments logger.log(message);// check result , send email message text body } function composemessage(headers,values){ var message = 'here data :' for(var c=0;c<values[0].length;++c){ message+='\n'+headers[0][c]+' : '+values[0][c] } return message; } note use same structure build better looking email in html format using html table (see reference here)
edit2
i wrote little piece of code generates both in text , html in table, feel free improve html formating colors etc...
function testmail(){ var ss = spreadsheetapp.getactivespreadsheet(); var responses = ss.getsheetbyname("sheet1"); var lastrow = responses.getlastrow(); var values = responses.getrange("a"+(lastrow)+":m"+(lastrow)).getvalues(); var headers = responses.getrange("a1:ak1").getvalues(); var message = composemessage(headers,values); var messagehtml = composehtmlmsg(headers,values); logger.log(messagehtml); mailapp.sendemail(session.geteffectiveuser().getemail(),'test html', message,{'htmlbody':messagehtml}); } function composemessage(headers,values){ var message = 'here data submitted :\n' for(var c=0;c<values[0].length;++c){ message+='\n'+headers[0][c]+' : '+values[0][c] } return message; } function composehtmlmsg(headers,values){ var message = 'here data submitted :<br><br><table style="background-color:lightblue;border-collapse:collapse;" border = 1 cellpadding = 5><th>data</th><th>values</th><tr>' for(var c=0;c<values[0].length;++c){ message+='<tr><td>'+headers[0][c]+'</td><td>'+values[0][c]+'</td></tr>' } return message+'</table>'; } edit 3
following comment : here log of test sheet + screen capture. check log see if similar structure data.
[13-07-16 14:29:40:920 cest] here data submitted :<br><br><table style="background-color:lightblue;border-collapse:collapse;" border = 1 cellpadding = 5><th>data</th><th>values</th><tr><tr><td>titre</td><td>mr</td></tr><tr><td>nom</td><td>wales</td></tr><tr><td>prénom</td><td>xavier</td></tr><tr><td>adresse</td><td>sunset bld, 45678</td></tr><tr><td>code</td><td>5000</td></tr><tr><td>ville</td><td>los angeles</td></tr><tr><td>pays</td><td>usa</td></tr><tr><td>email</td><td>john.smith@gmail.com</td></tr><tr><td>tél1</td><td>1212345654345</td></tr><tr><td>tél2</td><td></td></tr><tr><td>commun</td><td>théâtre</td></tr><tr><td>groupe</td><td>festival</td></tr><tr><td>organisme</td><td>xxx</td></tr></table> 
- "th" header tag (between <>, cannot write here because browser doesn't show it)
- "td" cell tag (between <>, cannot write here because browser doesn't show it)
- "tr" "row" tag (between <>, cannot write here because browser doesn't show it)
each of these tags must terminated /t... closing tag (with surrounding <>)to valid. loop structure in script takes care of automatically can see in log data.
note added table end tag @ end of loop... forgot in first code worked in tests.
Comments
Post a Comment