Google Forms Confirmation Email with Information From Linked Sheet

Question

I have set up a Google Form confirmation email trigger that I found here. 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 here.

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.

I have experimetned with e.values[1], but I can't seem to make it work.

Here is the script whithout any reference to Sheets (this one functions flawlessly):

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!

";
  message += "Your form responses were:

";

  // 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 < itemResponses.length; i++) {
    var itemResponse = itemResponses[i];
    message += itemResponse.getItem().getTitle() +": " + itemResponse.getResponse() + "
";
    // 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 += "
Please click here if you wish to edit your data or include additional details at a later date.
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.
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:
" + response.getEditResponseUrl() + "


Sincerely,
John Smith";
  message += "

";
  textbody = message.replace("
", "
");

  GmailApp.sendEmail(sendTo, subject, textbody,
                       {bcc: bcc, name: sendername, htmlBody: message});
}

And this is my attempt at achieving my goal, however it doesn't work:

    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!

";
  message += "Your form responses were:

";

  // 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 < itemResponses.length; i++) {
    var itemResponse = itemResponses[i];
    message += itemResponse.getItem().getTitle() +": " + itemResponse.getResponse() + "
";
    // 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 + "

Please click here if you wish to edit your data or include additional details at a later date.
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.
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:
" + response.getEditResponseUrl() + "


Sincerely,
John Smith";
  message += "

";
  textbody = message.replace("
", "
");

  GmailApp.sendEmail(sendTo, subject, textbody,
                       {bcc: bcc, name: sendername, htmlBody: message});
}

I added the two activitID parts, one in the code and the other in the message to be sent to the recipient.

Any ideas on how I could make this work?

utphx · Accepted Answer

Assuming the unique id is in the same row as the response, you can try to replace:

// Get the sheet-generated ID of the submission
  var activitID = e.values[1]; //ID number from column B

with:

// Get the sheet-generated ID of the submission
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1'); //rename to your sheet name
var row = e.range.getRow();
var activitID =  sheet.getRange("B" + row).getValue(); //ID number from column B

EDIT

As per comments:

This was part of your original code but I would just go to resources > current project triggers in the code editor and do it from there.

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 */
  var sheet = SpreadsheetApp.getActive();
 ScriptApp.newTrigger("sendConfirmationEmail")
   .forSpreadsheet(sheet)
   .onFormSubmit()
   .create();
}

Just use this part:

function sendConfirmationEmail(e) {

  var form = FormApp.openById("1lBkYf3eRnDzeXJnvawkxvWb5WGYgK14HzApwDmDyWSY");
  var formResponses = form.getResponses();
  //var response = form.getResponses();

  // 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!

";
  message += "Your form responses were:

";

  // response is a FormResponse - see https://developers.google.com/apps-script/reference/forms/form-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 sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Form Responses 1'); //rename to your sheet name
  var row = e.range.getRow();
  var activitID =  sheet.getRange("B" + row).getValue(); //ID number from column B

  // Now loop around, getting the item responses and writing them into the email message

  var r = formResponses.length-1;
  var editURL = formResponses[r].getEditResponseUrl();

  var formResponse = formResponses[r];
  var itemResponses = formResponse.getItemResponses();
  for (var j = 0; j < itemResponses.length; j++) {
    var itemResponse = itemResponses[j];
    message += itemResponse.getItem().getTitle() +": " + itemResponse.getResponse() + "
";
    // 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() == "Enter in Your email to receive a confirmation.") {
      sendTo = itemResponse.getResponse();
    }
  }

  message += "The ID of the submitted activity is: " + activitID + "

Please click here  if you wish to edit your data or include additional details at a later date.
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.
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:
" + editURL + "


Sincerely,
John Smith";
  message += "

";
  textbody = message.replace("
", "
");

  GmailApp.sendEmail(sendTo, subject, textbody,
                     {bcc: bcc, name: sendername, htmlBody: message});
}

Google Forms Confirmation Email with Information From Linked Sheet

Answers (1)

Related Questions