Automatically Email Based on Dropdown

[Jarrid Looney]

Here's what I'm trying to do:

Google Form

• Page 1:  Select Grade Level (this condition will send me to the next necessary page)
• Page 2:  Select Student from Dropdown
• Remaining Pages:  Pretty irrelevant to the help needed
• Referral To:  Dean of Students, Upper School Director, Lower School Director
  • Based on this one, I have used a var emailMap to send to the relevant person if selected; this one works; it sends an email to the school personnel with the details
• Communicate to Parents
  • I have created a "Contacts" sheet for communication information;
    • Column A:  Student Name (as it appears in dropdown referenced as Page 2)
    • Column B:  Student Email
    • Column C:  Parent Email 1
    • Column D:  Parent Email 2
  • I want to email the relevant parents (Column C and Column D) of "Contacts" sheet automatically when the student is recorded in the guidance log

function onFormSubmit(e) {

  var responses = e.namedValues; // All form responses as named values

  var dropdownSelection = responses['Referral To'][0]; // Adjust this to your actual form field name

  var emailSubject = 'Guidance Log Referral';

 

  // Change ‘School Building’ to the dropdown question that will determine the email recipient.

  // Change ‘New Form Submission’ to your preferred email subject.

 

  var questionOrder = [

    'Student Grade Level',

    'Student Name', // Adjust this to your actual form field name for the student name dropdown

    'Type of Behavior Logged',

    'Severity of Incident',

    'Infraction',

    'Please provide a brief description of the disciplinary incident.',

    'Consequence',

    'Please provide a brief description of the consequence. ',

    // Modify and add the rest of your questions here in the order you want them to appear in the email.

  ];

 

  var emailBody = 'A new form has been submitted. Details:\n\n';

 

  for (var i = 0; i < questionOrder.length; i++) {

    var question = questionOrder[i];

    var answer = responses[question] ? responses[question][0] : 'N/A';

    emailBody += question + ': ' + answer + '\n\n';

  }

 

  // Replace ‘A new form has been submitted. Details:’ with your preferred introductory text for the email.

  // The text /n/n creates a line break between this text and the question responses, so you can keep or delete as preferred.

 

  var emailMap = {

    'Lower School Director': 'youdontneedthis',

    'Upper School Director': 'youdontneedthis',

    'Dean of Students': 'youdontneedthis',

    // Replace the sample dropdown options and email addresses with your own options and corresponding email addresses. Add more mappings as needed.

  };

 

  if (emailMap.hasOwnProperty(dropdownSelection)) {

    MailApp.sendEmail(emailMap[dropdownSelection], emailSubject, emailBody);

  } else {

    Logger.log('Warning: dropdownSelection ' + dropdownSelection + ' not found in emailMap');

  }

  // Extracting form responses

  var studentName = responses['Student Name'][0]; // Adjust this to your actual form field name for the student name dropdown

  var typeOfBehavior = responses['Type of Behavior Logged'][0];

  var severityOfIncident = responses['Severity of Incident'][0];

  var infraction = responses['Infraction'][0];

  var incidentDescription = responses['Please provide a brief description of the disciplinary incident.'][0];

  var consequence = responses['Consequence'][0];

  var consequenceDescription = responses['Please provide a brief description of the consequence.'][0];

  // Accessing the Contacts sheet to find parent emails

  var contactsSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Contacts');

  var studentData = getStudentData(contactsSheet, studentName);

  // If student data is found, send an email to the parents

  if (studentData) {

    sendEmailToParent(

      studentData.parentEmail1,

      studentData.parentEmail2,

      studentData.studentName,

      typeOfBehavior,

      severityOfIncident,

      infraction,

      incidentDescription,

      consequence,

      consequenceDescription

    );

  } else {

    Logger.log('No contact data found for student: ' + studentName);

  }

}

function getStudentData(sheet, studentName) {

  var data = sheet.getDataRange().getValues();

  for (var i = 1; i < data.length; i++) { // Assuming the first row is headers

    if (data[i][0].trim() === studentName.trim()) {

      Logger.log('Match found: ' + data[i][0]);

      return {

        studentName: data[i][0],

        parentEmail1: data[i][2],

        parentEmail2: data[i][3]

      };

    }

  }

  Logger.log('No match found for: ' + studentName);

  return null;

}

function sendEmailToParent(parentEmail1, parentEmail2, studentName, typeOfBehavior, severityOfIncident, infraction, incidentDescription, consequence, consequenceDescription) {

  var subject = "Notice of Discipline Incident for " + studentName;

  var message = "Dear Parent,\n\n" +

                "This is to inform you that your child, " + studentName + ", has been involved in a discipline incident.\n\n" +

                "Details of the incident:\n" +

                "Type of Behavior: " + typeOfBehavior + "\n" +

                "Severity of Incident: " + severityOfIncident + "\n" +

                "Infraction: " + infraction + "\n" +

                "Incident Description: " + incidentDescription + "\n\n" +

                "Consequence: " + consequence + "\n" +

                "Consequence Description: " + consequenceDescription + "\n\n" +

                "Please contact the school for more details.\n\n" +

                "Thank you,\n" +

                "School Administration";

  if (parentEmail1) {

    MailApp.sendEmail(parentEmail1, subject, message);

  }

  if (parentEmail2) {

    MailApp.sendEmail(parentEmail2, subject, message);

  }

}

// Create an installable trigger

function createTrigger() {

  var form = FormApp.openById('1FAIpQLSeC6M8XsmlG1be-TVxQLhZqPJL4bld7y21L5v-JXIucbbL74Q'); // Replace with your Form ID

  ScriptApp.newTrigger('onFormSubmit')

           .forForm(form)

           .onFormSubmit()

           .create();

}

[George Ghanem]

Hi Jared,

 Your code looks good. Only thing I would add is to remove caps on name check so that the student name match finds the right student even if his name was entered without the proper capitalization.

 Did you have any issues with getting this working?

 

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/55755593-fe52-4690-9d80-0263660ee552n%40googlegroups.com.

[Jarrid Looney]

Hey, George:

I get the email sent to the "Upper School Director" when the referral is made but I'm not getting anything as a parent.  

So, the issue must be something down here:

Any ideas?

[Jonathan Butler]

You should try changing the line  if (data[i][0].trim() === studentName.trim()) to if (data[i][0].trim().toLowerCase() === studentName.trim().toLowerCase())

My hunch is the names aren't matching because of a capitalization error somewhere.

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/edb80c33-d53e-40b4-a93c-731b9f29b9b4n%40googlegroups.com.

[George Ghanem]

You have Logger.log calls in the right place. Can you share the logs as well so we can see how far the student name match is processing?

To view this discussion on the web visit https://groups.google.com/d/msgid/google-apps-script-community/edb80c33-d53e-40b4-a93c-731b9f29b9b4n%40googlegroups.com.