Google-sheets – The script(onSubmmit Trigger) only works in the script editor and doesn’t work when the form is actually submitted

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

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:

Best Answer

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?

Related Topic