javascript - Sending Emails to separate email addresses based on cell content in a Google Form

Question

I'm new to writing any type of code, and I'm having trouble trying to figure out how to send emails based on the cell content in Google Sheets. I created a form that submits to a spreadsheet called Unusual Request Responses. I need to send emails to different email addresses based on what is submitted from the form in column N. Ex: if purchasing is entered, I need it to send to purchasing's email, but if information only is entered, I need it to send to marketing's email and not to purchasing. The only thing I've been able to figure out is how to get it to send to an email if a form is submitted in general, which I don't really need to have a code for in Google Sheets.

question from:https://stackoverflow.com/questions/65848630/sending-emails-to-separate-email-addresses-based-on-cell-content-in-a-google-for

Answer 1

This is possible by creating Installable Trigger with event type On form submit.

Installable triggers let Apps Script run a function automatically when a certain event such as submitting form.

To create Installable Trigger:

1. Open your Apps Script project.
2. At the left, click Triggers alarm.
3. At the bottom right, click Add Trigger.
4. Select and configure the type of trigger you want to create.
5. Click Save.

From there we fetch the data submitted by user by using Event Object.

When a trigger fires, Apps Script passes the function an event object as an argument, typically called e. The event object contains information about the context that caused the trigger to fire.

There are two ways to get the data from an event on form submit, e.values and e.namedValues.

• namedValues - An object containing the question names and values from the form submission.
• values - Array with values in the same order as they appear in the spreadsheet.

Example:

Code:

function onFormSubmit(e) {
  //get data from question 'Type'
  var type = e.namedValues['Type'][0];
  var email = '';
  var subject = '';
  var message = '';
  if(type == 'Information'){
    email = 'inputmarketingemailhere';
    subject = 'For Marketing';
    message = 'Message for Marketing';
  }else if(type == 'Purchasing'){
    email = 'inputpurchasingemailhere';
    subject = 'For Purchasing';
    message = 'Message for Purchasing';
  }

  MailApp.sendEmail(email, subject, message);
}

Submitted Answers:

Output:

Note: Run the script first to the Editor, This will prompt an authorization to gain access rights to the MailApp Service. See Authorization for Google Services

References:

• Event Objects
• Installable Triggers