Send a Google Forms response to specific email address based on form response

Question

I need to send Google Forms responses to specific clinicians at my practice depending on whether they have been selected on a drop-down menu in the form. At the moment, Google Forms sends an email to all "collaborators" when a form has been filled out.
My goal is that clinicians will only receive an email when their own client fills out the form.

Is this something I can do within the google spreadsheet for the Google Form? Or as an add-on?

Answer 1

(Similar questions have been asked before but I believe you will get confused trying to modify the code.)

What you want to achieve is possible by writing some code (a script) in the spreadsheet linked to the form.

Step 1

The first thing to do is to create your form. For the script to work you have to remember just one thing. The first question MUST be the drop-down list with your clinicians. Following that you can have as many questions as you like.

Step 2

After you finish your form and link it to your spreadsheet, you must create an extra sheet named ClinEmails (for clinician Email).

Then or cell A1 write the word Clinician followed by the names of the clinicians EXACTLY as they appear in the form.
Next to them on column B you must fill-in their corresponding email addresses, as shown on the attached image.

Step 3

Next, go to the Tools menu on your spreadsheet and click on the Script editor. A new tab will open. Delete everything on the page and paste the code from below.

// Send a Google Forms response to specific email address based on form response
// As from: https://webapps.stackexchange.com/a/132665/

function wa132657(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 clinician = response[0][1];
  Logger.log("DEBUG: Clinician = "+clinician);// DEBUG

  // get the emails list
  var emailSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("ClinEmails");
  // 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 clinician is equal to MyClinEmail
    if (clinician == emaildata[i][0]){
      // there is a match
      //Next, get the email address
      var emailClinician = emaildata[i][1];
     Logger.log("DEBUG: clinician = "+emaildata[i][0]+", email address: "+emailClinician);// DEBUG

      // Finally, send the Email.
      var theirName = e.values[2];
      var theirEmail = e.values[3];
      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);

    }
  }
}

Click the save button.
On the pop-up window give your script a unique name. You will be asked to authorize the script. Click on Advanced and authorize it.

Step 4

Finally create a trigger to run on form submit by clicking on the clock icon on the toolbar as explained here. If you're asked to authorize it again, please do.

To summarize:

Create and link form, create new sheet with clinicians names/emails, copy code, create trigger, test, enjoy.