i stuck @ point in code , need expert help. want reference column "e" on sheet "form responses", return name of person. same name can found in column "a" of sheet "email". in column "b" of sheet "email" email address want send data to. stuck @ how produce email address. here have far...
function emaildata(){ 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 recipient = responses.getrange("e"+(lastrow)).getvalues(); var emailsheet = ss.getsheetbyname("email"); var names = emailsheet.getrange("a2:a20").getvalues(); var emails = emailsheet.getrange("b2:b20").getvalues(); var subject = "capacity campaign form"; var message = composemessage(headers,values);// call function 2 arrays arguments logger.log(message);// check result , send email message text body mailapp.sendemail(recipient,subject,message); } function composemessage(headers,values){ var message = 'here data form submission:' for(var c=0;c<values[0].length;++c){ message+='\n'+headers[0][c]+' : '+values[0][c] } return message; } i must give props @serge helping me array. alas, provide awesome!
you've got names , email addresses need search, in 2 2-dimensional arrays. each of arrays array of rows. simple search this:
var email = ''; // if don't find match, we'll fail send (var row=0; row < names.length; row++) { if (names[row][0] == recipient) { email = emails[row][0]; break; // end search, we're done } } ... mailapp.sendemail(email,subject,message); there more elegant ways this, i'm sure, should work you.
Comments
Post a Comment