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!<br><br>";
  message += "Your form responses were:<br><br>";

  // 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() + "<br>";
    // 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 += "<br><a href=\"" + response.getEditResponseUrl() + "\">Please click here</a> if you wish to edit your data or include additional details at a later date.<br>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.<br>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:<br>" + response.getEditResponseUrl() + "<br><br><br>Sincerely,<br>John Smith";
  message += "<br><br>";
  textbody = message.replace("<br>", "\n");

  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!<br><br>";
  message += "Your form responses were:<br><br>";

  // 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() + "<br>";
    // 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 + "<br><br><a href=\"" + response.getEditResponseUrl() + "\">Please click here</a> if you wish to edit your data or include additional details at a later date.<br>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.<br>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:<br>" + response.getEditResponseUrl() + "<br><br><br>Sincerely,<br>John Smith";
  message += "<br><br>";
  textbody = message.replace("<br>", "\n");

  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 · 2016-12-16T23:08:22.593

0

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!<br><br>";
  message += "Your form responses were:<br><br>";

  // 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() + "<br>";
    // 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 + "<br><br><a href=\"" +  editURL + "\">Please click here </a> if you wish to edit your data or include additional details at a later date.<br>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.<br>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:<br>" + editURL + "<br><br><br>Sincerely,<br>John Smith";
  message += "<br><br>";
  textbody = message.replace("<br>", "\n");

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

edited Dec 16 '16 at 23:08

answered Dec 15 '16 at 04:45

utphx

1,287
1
8
19

Thank You for the answer, utphx. Sadly, Your answer does not make the script work. I changed it, renamed the response sheet title, but no email was sent. Just as with my previous attempts, this seems to break the functionality. – mozzribo Dec 15 '16 at 12:26
And to clarify Your assumption: yes, the unique ID is in the same row as the response. – mozzribo Dec 15 '16 at 12:33
can you check execution transcript for errors? Or try Logger.log(activitID) to see if it is getting the ids – utphx Dec 15 '16 at 12:45
Since I cannot run the script manually in a proper manner as a submission is required to get the freshly submitted row of answers, there are are no transcripts or logs shown (as strange as this is)... I did enforce a run, however, but it only showed an error for the `var response = e.response;` and the script probably stopped executing there. – mozzribo Dec 15 '16 at 13:07
That is strange. Can you leave the script open while submitting and see if logs show up – utphx Dec 15 '16 at 13:14
Yes, I tried that. I tested now with the working code (so without any additions), also with the script open. There is now a transcript (no log). It seems that the addition prevents it from running, or at least it never arrives to the point of creating anything in the transcript/logs. – mozzribo Dec 15 '16 at 13:22
Can you put the code in the spreadsheet instead of the form and create an on form submit trigger? – utphx Dec 15 '16 at 16:33
Not really. This code is for Forms. A different code might work, but then I couldn't send the recipient the 'Edit Form' link, which is the whole purpose of it. I have set up a live Form with linked a Sheet for testing, all with the working code, [click here](https://docs.google.com/forms/d/1lBkYf3eRnDzeXJnvawkxvWb5WGYgK14HzApwDmDyWSY/edit?usp=sharing). – mozzribo Dec 15 '16 at 18:08
The code will work on a spreadsheet, if you share the spreadsheet I can take a look. Also I edited my answer because it was missing a var. – utphx Dec 16 '16 at 01:40
I noticed the var but it didn't help, so I forgot to tell. [Here](https://docs.google.com/spreadsheets/d/1u2YKuJgjoqrx6yKyad6Q9BpETrHKSMO0uIyd_viXH34/edit?usp=sharing) is the Sheet. – mozzribo Dec 16 '16 at 09:04
everything should be working on the sheets you shared – utphx Dec 16 '16 at 15:07
It does work perfectly, thank You. I can see that it deletes all other triggers. Will this mess with my other scripts in Sheets (in the one I'm setting up for my purposes, there are other Scripts with Triggers)? – mozzribo Dec 16 '16 at 22:08
You don't need to delete triggers I just left that because it was part of your setup code. – utphx Dec 16 '16 at 22:56
I updated my answer to reflect the triggers. I hope this solves your issue an the answer is acceptable. – utphx Dec 16 '16 at 23:09
Sorry for the late reply. I had experimented with Your solution and it works. Technically, it does not answer the question, but even without Your latest edits, it perfectly does what I need. I am deeply thankful for the time and will You invested into my challenge. – mozzribo Dec 19 '16 at 13:13
While I was experimenting, I managed to get a transcript to our first attempt (the one trying to add a few lines of code into the script while it is running in Forms). I will paste the last line here just for intrigue's sake if anyone will ever need it: `Execution failed: TypeError: Cannot call method "getSheetByName" of null. (line 39, file "Send Edit Link Confirmation Email") [0.003 seconds total runtime]`. – mozzribo Dec 19 '16 at 13:13

Google Forms Confirmation Email with Information From Linked Sheet

1 Answers1