Google Sheets – How to Send Email to Selected Person from Dropdown

google sheetsgoogle-apps-scriptgoogle-apps-script-triggersgoogle-forms

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.

I found this one script which only works when the question with the dropdown menu is the first question which is not the case in my form. I tried modifying it but I can't seem to get it right. The code also flags an error

TypeError: Cannot read property 'range' of undefined (line 6, file "Code").

Here is the script that I'm using:

function onSubmit(e) {
//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][2]; 
Logger.log("DEBUG: Mentor name = "+Name);// 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[0];
  var theirEmail = e.values[1];
  var theEnquiry = e.values[4];
  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(emailClinician, subject, message);

}
}
}

Best Answer

Regarding the error

It looks that you are running the script from the script editor but this doesn't pass an event object, in this case e ( from onSubmit(e)) is undefined.

If you want to run the script from the script editor, a custom menu, on clicking a image, by calling it throughgoogle.script.run or the Google Apps Script API instead of using the on form submit installable trigger, instead of calling the function directly, you should call a function that creates an object with the required properties, in this case

var e = {};
e.range = SpreasheetApp.getSheetByName('Form Responses').getRange('2:2');
e.values = e.range.getValues()[0];

NOTE: In the above example the range that is assigned to e.range is the whole row 2. You could use other forms of getRange i.e. to get the last row with data.

Regarding getting values from answers

When a spreadsheet on form submit installable trigger is used, e is the event object, e.values includes the values on the answer in the same order than the columns that receive those values. By the default the first value (e.values[0]) is the form submission timestamp.

Related

Related Topic