title: Google Forms Confirmation Email with Information From Linked Sheet
body: <p>I have set up a Google Form confirmation email trigger that I found <a href="https://securitasdato.blogspot.com/2014/11/sending-confirmation-emails-from-google.html" rel="nofollow noreferrer">here</a>. Meanwhile, the connected answer Sheet calculates a unique ID in a separate column (column B in my case) for every submission - via the formula I found <a href="https://productforums.google.com/forum/#!topic/docs/WihZbhsrhOk" rel="nofollow noreferrer">here</a>.</p>
  
  <p>What I would like to achieve is to insert this unique ID into the confirmation email. The problem is that I don't know how to reference the appropriate Sheets field in a Forms script.</p>
  
  <p>I have experimetned with <code>e.values[1]</code>, but I can't seem to make it work.</p>
  
  <p>Here is the script whithout any reference to Sheets (this one functions flawlessly):</p>
  
  <pre><code>function setup() {
  
    /* First, delete all previous triggers */
    var triggers = ScriptApp.getProjectTriggers();
  
    for (var i in triggers) {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  
    /* Then add a trigger to send an email on form submit */
    ScriptApp.newTrigger("sendConfirmationEmail")
    .forForm(FormApp.getActiveForm())
    .onFormSubmit()
    .create();
  }
  
  function sendConfirmationEmail(e) {
    // e is a Form Event object - see https://developers.google.com/apps-script/guides/triggers/events#google_forms_events
  
    // Edit this to set the subject line for the sent email
    var subject = "Data Entry Successful";
  
    // This will show up as the sender's name
    var sendername = "John Smith";
  
    // This is the body of the registration confirmation message
    var message = "Thank you for submitting the details of your project!&lt;br&gt;&lt;br&gt;";
    message += "Your form responses were:&lt;br&gt;&lt;br&gt;";
  
    // response is a FormResponse - see https://developers.google.com/apps-script/reference/forms/form-response
    var response = e.response;
  
    var textbody, sendTo, bcc;
  
    // Get the script owner's email address, in order to bcc: them
    bcc = Session.getActiveUser().getEmail();
  
    // Now loop around, getting the item responses and writing them into the email message
    var itemResponses = response.getItemResponses();
    for (var i = 0; i &lt; itemResponses.length; i++) {
      var itemResponse = itemResponses[i];
      message += itemResponse.getItem().getTitle() +": " + itemResponse.getResponse() + "&lt;br&gt;";
      // If this field is the email address, then use it to fill in the sendTo variable
      // Check that your form item is named "Please enter your email address" or edit to match
      if (itemResponse.getItem().getTitle() == "Please enter your email address") {
        sendTo = itemResponse.getResponse();
      }
    }
  
    message += "&lt;br&gt;&lt;a href=\"" + response.getEditResponseUrl() + "\"&gt;Please click here&lt;/a&gt; if you wish to edit your data or include additional details at a later date.&lt;br&gt;It is essential that you submit any editing through this provided link, since your response data is exclusive only to you. Please do not share your unique edit link with others.&lt;br&gt;If the link doesn't work properly, please copy the following link address manually and then paste it directly into your browser's URL bar:&lt;br&gt;" + response.getEditResponseUrl() + "&lt;br&gt;&lt;br&gt;&lt;br&gt;Sincerely,&lt;br&gt;John Smith";
    message += "&lt;br&gt;&lt;br&gt;";
    textbody = message.replace("&lt;br&gt;", "\n");
  
    GmailApp.sendEmail(sendTo, subject, textbody,
                         {bcc: bcc, name: sendername, htmlBody: message});
  }
  </code></pre>
  
  <p>And this is my attempt at achieving my goal, however it doesn't work:</p>
  
  <pre><code>    function setup() {
  
    /* First, delete all previous triggers */
    var triggers = ScriptApp.getProjectTriggers();
  
    for (var i in triggers) {
      ScriptApp.deleteTrigger(triggers[i]);
    }
  
    /* Then add a trigger to send an email on form submit */
    ScriptApp.newTrigger("sendConfirmationEmail")
    .forForm(FormApp.getActiveForm())
    .onFormSubmit()
    .create();
  }
  
  function sendConfirmationEmail(e) {
    // e is a Form Event object - see https://developers.google.com/apps-script/guides/triggers/events#google_forms_events
  
    // Edit this to set the subject line for the sent email
    var subject = "Data Entry Successful";
  
    // This will show up as the sender's name
    var sendername = "John Smith";
  
    // This is the body of the registration confirmation message
    var message = "Thank you for submitting the details of your project!&lt;br&gt;&lt;br&gt;";
    message += "Your form responses were:&lt;br&gt;&lt;br&gt;";
  
    // response is a FormResponse - see https://developers.google.com/apps-script/reference/forms/form-response
    var response = e.response;
  
    var textbody, sendTo, bcc;
  
    // Get the script owner's email address, in order to bcc: them
    bcc = Session.getActiveUser().getEmail();
  
    // Get the sheet-generated ID of the submission
    var activitID = e.values[1]; //ID number from column B
  
    // Now loop around, getting the item responses and writing them into the email message
    var itemResponses = response.getItemResponses();
    for (var i = 0; i &lt; itemResponses.length; i++) {
      var itemResponse = itemResponses[i];
      message += itemResponse.getItem().getTitle() +": " + itemResponse.getResponse() + "&lt;br&gt;";
      // If this field is the email address, then use it to fill in the sendTo variable
      // Check that your form item is named "Please enter your email address" or edit to match
      if (itemResponse.getItem().getTitle() == "Please enter your email address") {
        sendTo = itemResponse.getResponse();
      }
    }
  
    message += "The ID of the submitted activity is: " + activitID + "&lt;br&gt;&lt;br&gt;&lt;a href=\"" + response.getEditResponseUrl() + "\"&gt;Please click here&lt;/a&gt; if you wish to edit your data or include additional details at a later date.&lt;br&gt;It is essential that you submit any editing through this provided link, since your response data is exclusive only to you. Please do not share your unique edit link with others.&lt;br&gt;If the link doesn't work properly, please copy the following link address manually and then paste it directly into your browser's URL bar:&lt;br&gt;" + response.getEditResponseUrl() + "&lt;br&gt;&lt;br&gt;&lt;br&gt;Sincerely,&lt;br&gt;John Smith";
    message += "&lt;br&gt;&lt;br&gt;";
    textbody = message.replace("&lt;br&gt;", "\n");
  
    GmailApp.sendEmail(sendTo, subject, textbody,
                         {bcc: bcc, name: sendername, htmlBody: message});
  }
  </code></pre>
  
  <p>I added the two <code>activitID</code> parts, one in the code and the other in the message to be sent to the recipient.</p>
  
  <p>Any ideas on how I could make this work?</p>