javascript - How to get the active value from a cell in a Google spreadsheet on form submission? -

i have made google form saves data in spreadsheet, writing code such spreadsheet sends me email data it, struggling 1 of values. column 4 (d) gets email address sender, wanna recover value put "replyto" variable , able directly reply whoever enquiring.

this code far:

function initialize() {    try {      var triggers = scriptapp.getprojecttriggers();      (var in triggers)       scriptapp.deletetrigger(triggers[i]);      scriptapp.newtrigger("emailgoogleformdata")       .forspreadsheet(spreadsheetapp.getactivespreadsheet())       .onformsubmit().create();    } catch (error) {     throw new error("please add code in google spreadsheet");   } }  function emailgoogleformdata(e) {    try {      if (mailapp.getremainingdailyquota() > 0) {        // may replace email address       var email = "nico.pinera@filmmusiclive.com";        /*********** sender email address || have problems here ***********/        var replyto, sendermail,ss,r_max;       ss = spreadsheetapp.getactivesheet();       //r_max = ss.getmaxrows();         // returns active cell       var range = ss.getactiverange();       sendermail = range.getvalues()[3];         // enter subject google form email notifications       var subject = "parte de nntt recibido"         var key, entry,         message = "<b>hola pepito</b><br/>aa</br><br><br><br><br>",         ss = spreadsheetapp.getactivesheet(),         cols = ss.getrange(1, 1, 1, ss.getlastcolumn()).getvalues()[0];        // iterate through form fields       (var keys in cols) {          key = cols[keys];         entry = e.namedvalues[key] ? e.namedvalues[key].tostring() : "";                      // include form fields not blank         if ((entry !== "sí") && (entry !== "") && (entry !== ",") && (entry.replace(/,/g, "") !== "sí"))           message += '<img src="" /><br/><br/>' + ' <b><u> ' + key + '</u></b>' + ' => ' + entry ;                    }        mailapp.sendemail(email, replyto, subject, message);     }   } catch (error) {     logger.log(error.tostring());   } } 

does have idea of how can active value cell? whatever row in column d. also, did finding int of maxrows() , finding value of cell of maxrows() , column d, if manually edit spreadsheet (say manually add value in last row), new form requests stored above 1 , shifting down. (if edit row 12 , new request comes in, row moves down 13 , new 1 stored in 12 , on , forth).

short answer

if required data email included in form response, instead of using spreadsheet bounded script, use form bounded script.

explanation

google forms have bounded scripts. scripts triggered on form submission , form submission responses included in event object form response object. see google form events , form response.

benefits

• to have responses data in 1 place, form file, instead of two, form file , spreadsheet.
• the responses in response object. there no need call value cell , save several lines of code , calls google apps script services
• the response object include edit response url not added spreadsheet default.

brief code example

simplified version of code posted @ sending confirmation emails google apps forms les bell , adapted add email address submitted response reply-to parameter

function sendconfirmationemail(e) {   // e form event object     // edit set subject line sent email   var subject = "registration successful";    // show sender's name   var sendername = "your name goes here";    // body of registration confirmation message   var message = "thank registering.<br>we in                    touch.<br><br>";    // response formresponse    var response = e.response;    var textbody, sendto, bcc, replyto;    // script owner's email address, in order bcc: them   bcc = session.getactiveuser().getemail();    // loop around, getting item responses , writing them    // email message   var itemresponses = response.getitemresponses();   (var = 0; < itemresponses.length; i++) {     var itemresponse = itemresponses[i];     // if field email address, use fill in      // sendto variable     // check form item named "email address" or edit match     if (itemresponse.getitem().gettitle() == "email address") {       sendto = itemresponse.getresponse();       replyto = sendto;     }   }    gmailapp.sendemail(sendto, subject, message, {                        bcc: bcc,                          name: sendername,                          htmlbody: message,                          replyto: replyto                      }); } 

complete code

the below code taken sending confirmation emails google apps forms les bell. urls included comments removed , breaklines added avoid appearance of horizontal scroll bar.

its purpose send submitted responses , edit response url email address captured in form submission.

function setup() {    /* first, delete previous triggers */   var triggers = scriptapp.getprojecttriggers();    (var in triggers) {     scriptapp.deletetrigger(triggers[i]);   }    /* add trigger send email on form submit */   scriptapp.newtrigger("sendconfirmationemail")   .forform(formapp.getactiveform())   .onformsubmit()   .create(); }  function sendconfirmationemail(e) {   // e form event object     // edit set subject line sent email   var subject = "registration successful";    // show sender's name   var sendername = "your name goes here";    // body of registration confirmation message   var message = "thank registering.<br>we in touch.                  <br><br>";   message += "your form responses were:<br><br>";    // response formresponse    var response = e.response;    var textbody, sendto, bcc;    // script owner's email address, in order bcc: them   bcc = session.getactiveuser().getemail();    // loop around, getting item responses , writing them    // email message   var itemresponses = response.getitemresponses();   (var = 0; < itemresponses.length; i++) {     var itemresponse = itemresponses[i];     message += itemresponse.getitem().gettitle() +": "              + itemresponse.getresponse() + "<br>";     // if field email address, use fill in      // sendto variable     // check form item named "email address" or edit      // match     if (itemresponse.getitem().gettitle() == "email address") {       sendto = itemresponse.getresponse();     }   }    message += "<br>if wish edit response, please click on                <a href=\""            + response.geteditresponseurl()            + "\">this link</a>.";   message += "<br><br>";   textbody = message.replace("<br>", "\n");    gmailapp.sendemail(sendto, subject, textbody,                        {bcc: bcc, name: sendername, htmlbody: message}); }