1

This is a follow up of How to send email to a person selected from a dropdown menuwith all answers of a response of the form submission?

I want to notify mentors if they're selected by a user on a Google Form. The names of the mentors are in a drop-down menu in the Google Form. This is the form: https://forms.gle/iNuA5nB2J9koKhXt6.

I'm storing responses in a spreadsheet, I've created a different worksheet in the same spreadsheet named "namenemail" in which I've stored the names and email IDs of the mentors. This is the spreadsheet:
https://docs.google.com/spreadsheets/d/15xS_-I6Jw3cqx-00Ink4FFTcfmI3Zi-hBGlMU2x0Cb4/edit?usp=sharing.

I want to send an email to the mentor whose name is selected from the dropdown menu with all the answers from the response of Google Form submission on submit. I can't seem to get it right.

The Script that I'm using is working if I run it from the script editor (fixed range) but won't work for a new form submission.

The script is as follows:

function AutoConfirmation(e){
var e = {};
var afar = "15xS_-I6Jw3cqx-00Ink4FFTcfmI3Zi-hBGlMU2x0Cb4";
var Data = SpreadsheetApp.openById(afar).getSheetByName("Form responses 1");
numRows= Data.getLastRow();
e.range = Data.getRange('2:2');
e.values = e.range.getValues()[0];

//setup the spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();

//get the range from OnFormSubmit
var range = e.range;
Logger.log("DEBUG: the range is "+range.getA1Notation());//DEBUG

// get the data for the range
var response = range.getValues();

// get the clinician name from the form submission
var mentor = response[0][3]; 
Logger.log("DEBUG: Mentor name = "+mentor);// DEBUG

// get the emails list
var emailSheet =    SpreadsheetApp.getActiveSpreadsheet().getSheetByName("namenemail");
// get ALL the data from this sheet
var emaildata = emailSheet.getDataRange().getValues();

// check how many rows of data
var emailLastRow = emailSheet.getLastRow();
// start the loop through the emails data
for (var i=1; i<emailLastRow; i++){

// if the mentor is equal to Email
if (mentor == emaildata[i][0]){
// there is a match
//Next, get the email address
var emailmentor = emaildata[i][1];
Logger.log("DEBUG: clinician = "+emaildata[i][0]+", email address: "+emailmentor);// DEBUG

// Finally, send the Email.
var theirName = e.values[1];
var theirEmail = e.values[2];
var theEnquiry = e.values[5];
var subject = "New Form Submitted";
var message = "New Enquiry by: \n\n EMAIL: " + theirEmail + " \n Name: " + theirName + " \n\n     Regarding Enquiry: \n" + theEnquiry; 

MailApp.sendEmail(emailmentor, subject, message);
}
}
}

The trigger that I'm using is as follows:

0

If you are calling the function by using an on form submit installable trigger you should not include on the declaration of AutoConfirmation(e):

var e = {};

and either

e.range = Data.getRange('2:2');
e.values = e.range.getValues()[0];

as the above lines will overwrite the event object and it's properties that were sent by the trigger.

To run your AutoConfirmation function from the script editor, you should create another function to pass to it an object with the required properties, i.e.:

function debug_AutoConfirmation(){

  var e = {};
  var afar = "15xS_-I6Jw3cqx-00Ink4FFTcfmI3Zi-hBGlMU2x0Cb4";
  var Data = SpreadsheetApp.openById(afar).getSheetByName("Form responses 1");
  var numRows = Data.getLastRow();
  e.range = Data.getRange('2:2');
  e.values = e.range.getValues()[0];

  AutoConfirmation(e)

}

For a broader explanation see the answer from Mogsdad to How can I test a trigger function in GAS?

  • thank you so much, this works great! – Prerit Shah Jun 11 '20 at 19:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.