0

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);

}
}
}
  • "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" What is the case in your form? Note: you are using a Google Sheet on form submit installable trigger the order of the questions is the order of the columns (sometimes the order of the questions doesn't match the order of the columns). – Rubén Jun 11 at 15:51
  • In my case the dropdown menu is on question 3 @Rubén, can you please view the links attached to the questions? It has the links to the form and to the spreadheet which contains both of the in use worksheets – Prerit Shah Jun 11 at 15:58
  • While the links could be helpful, all the relevant details should be included in the question. – Rubén Jun 11 at 16:02
  • @Rubén, I'm so sorry, this is my first question on StackExchange and I really can't seem to get it right. Apologies for poor question building but can you help me solve this problem? – Prerit Shah Jun 11 at 16:08
  • 1
    @Rubén, I've drafted a new question, please review it here : webapps.stackexchange.com/questions/143110/… – Prerit Shah Jun 11 at 16:43
0

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

| improve this answer | |
  • That worked, however It flagged an error that Name on line 21 was undefinied. I changed the following: Line 20 : 'var mentor = response[0][3];' Line 21: 'Logger.log("DEBUG: Mentor name = "+ Mentor names);// DEBUG' it flags the error "SyntaxError: missing ) after argument list (line 21, file "Code.gs")" @Rubén any suggestions for this? – Prerit Shah Jun 11 at 14:03
  • The code doesn't include a variable declararation for Name but there is one for Mentor. Replace Name by Mentor. i.e. Logger.log("DEBUG: Mentor name = "+Mentor);// DEBUG, By the way, please read What should I do when someone answer my question – Rubén Jun 11 at 14:14
  • The code only works in the script editor and doesn't work when the form is actually submitted. Hence, I feel the query isn't answered just yet. Any suggestions on how to get it up and running? – Prerit Shah Jun 11 at 15:14
  • Please edit the question to include "The code only works in the script editor and doesn't work when the form is actually submitted." if that occurs with the original code, if you modified the code and you are getting no error or a different error, then you should post a new question. Also you should include the steps followed to create the corresponding installable trigger. – Rubén Jun 11 at 15:19

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.