Google Forms Confirmation Email with Information From Linked Sheet
<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>