1

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?

  • How many clinicians do you have? 3, 10, 50, 200 ? How many questions are there? 3, 10, 50, 200 ? Could you also please share a sample form? – marikamitsos Aug 11 at 10:07
  • Which feature are you using to send the email to all "collaborators"? Are you using a script or the Google Sheets built-in notification feature? Also please add a brief description of you search/research efforts as is suggested on How to Ask. – Rubén Aug 11 at 17:30
  • 1
    Should you have issues applying the code or wish to modify the sent message, please let us know and will help out. – marikamitsos Aug 11 at 17:31
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.

clinician emails

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.

  • thank you so much for this! I tried using the code here and it seemed to work but stopped working after I added more than two emails stackoverflow.com/questions/57382059/… – sophie23 Aug 17 at 6:13
  • "I tried using the code here...???" Here where? Here or there? I am confused. Also. Where did you add the emails? As mentioned before- please do share a sample form as well as its response sheet. – marikamitsos Aug 18 at 0:39
  • "...I tried the code from the link I gave not the code that you wrote on this page". Please go ahead and try the provided code on this page. Also, as per site guidelines when an answer addresses your question, you should accept it so others can benefit as well -. (Why vote?) – marikamitsos Aug 25 at 16:21

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.