CODEWITHSUNDEEP
google-apps-scriptgoogle-forms
wedge22
wedge22

Reputation: 33

Google Forms Automated Emails

I am using a Google Form and collecting the responses on a Sheet. I require notification emails be sent out to different teams depending on one of the responses on the Form. For example the form has the question, which Province are you in, if its AB then I need the AB team to get an email notification, if its the BC team I need only that team to be notified.

I have tried to use the Sheet (Responses) and build scripts off there by using Importrange function to create seperate sheets for each Province but that has not worked out so far. I am currently trying to see if I can just create the scripts in the Form itself and not have to worry about the sheets and just use one sheet.

var form = FormApp.getActiveForm();
var formResponses = form.getResponses();
for (var i = 0; i < formResponses.length; i++) {
  var formResponse = formResponses[i];
  var itemResponses = formResponse.getItemResponses();
  for (var j = 0; j < itemResponses.length; j++) {
    var itemResponse = itemResponses[j];
    Logger.log('Response #%s to the question "%s" was "%s"',
        (i + 1).toString(),
        itemResponse.getItem().getTitle(),
        itemResponse.getResponse());
  }
}
/**
 * Sends emails when form updated by end user, split responses by Province, send emails to team specified for each Province only.
 */
function sendEmails() {
    var emailAddress = "[email protected]"; // First column
    var message =  itemResponse; // Response 
    var subject = 'Survey Updated';
    MailApp.sendEmail(emailAddress, subject, message);
  }

Currently the code above will send me an email once the form is submitted, I cannot figure out how to filter by Province or email a specific team based on the Province response. I also would like the details from each response to be in the body of the email so the teams do not have to open the spreadsheet.

Upvotes: 1

Views: 515

Answers (3)

Rafa Guillermo
Rafa Guillermo

Reputation: 15357

Edit your sendEmails() function to grab the latest form response and search the item responses for the province:

function onSubmit(e) {
  var responses = FormApp.getActiveForm().getResponses();  
  var response = responses[responses.length - 1].getItemResponses();
  var provinceQuestionNumber = 4 // which question is the province question?
  var province = response[provinceQuestionNumber - 1].getResponse()

  if (province == "Province1"){
    MailApp.sendEmail('[email protected]', 'New form response!', "The form has a new response for someone in province " + province + ".");  
  }
  else if (province == "Province2"){
    MailApp.sendEmail('[email protected]', 'New form response!', "The form has a new response for someone in province " + province + ".");  
  }
  else if (province == "Province3"){
    MailApp.sendEmail('[email protected]', 'New form response!', "The form has a new response for someone in province " + province + ".");  
  }
  else if (province == "Province4"){
    MailApp.sendEmail('[email protected]', 'New form response!', "The form has a new response for someone in province " + province + ".");  
  }
   /*
   ...
   */
}

Then go to Edit -> Current Project's Triggers and set up a new installable trigger with the following settings:

  • Choose which function to run: sendEmails
  • Choose which deployment should run: Head
  • Select event source: From form
  • Select event type On form submit

Also don't forget to change the names of the provinces and extend the conditional statement if you need, and set which question number the province question is by changing the value of provinceQuestionNumber.

Upvotes: 1

Rander Gabriel
Rander Gabriel

Reputation: 647

The information you need (the response's province name) should be available in a field of the itemResponse object. So, what you nerd to do is access that information and make a switch case with it's value. There is a link for the documentation of the itemResponse.getResponse() method: https://developers.google.com/apps-script/reference/forms/item-response#getResponse()

Upvotes: 0

Cooper
Cooper

Reputation: 64042

Try using the onFormSubmit trigger and write your script in the Spreadsheet with the Linked Sheet. You will be able to get the information you need from the event object on every onFormSubmit() and you should be able to direct your emails as needed with that information.

If you have any problems please return with additional questions.

Upvotes: 0

Related Questions