Google Forms – Conditional Email Notification on Submit

google-apps-scriptgoogle-forms

I have a script container-bound to a Google form with which I'm trying to send email notifications to departments in certain towns within my organization when a new response is submitted. The town is determined by the first question in the form. Can someone help me get the following to work?

function informDepartment(e)
{
  var email = "department." + e.response.getResponseForItem("Town").toString()
                             .toLowerCase().replace(/\u00e4/g, "ae").replace(/\u00f6/g, "oe").replace(/\u00fc/g, "ue").replace(/\u00df/g, "ss")
                          + "@domain.com";

  var itemResponses = e.getItemResponses();
  var subject = "New submit";
  var message = "";

  for(var i in itemResponses) 
    message += itemResponses[i] + "\n\n";

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

I checked that the script is indeed executed upon submission. Unfortunately, I get an error message 'failed' without any further info on what might be going wrong. I'm unable to view the logs (permission denied for some reason).

Best Answer

You are trying to use Apps Script objects in a way inconsistent with documentation. In particular,

  • getResponseForItem("Town"). The method getResponseForItem does not take a string as an argument. It takes an Item as an argument.
  • Also, this method returns an object of class ItemResponse. And toString will only make it "ItemResponse" string. The object has several properties, one of which is getResponse, for getting the response. Other properties are meta-data.
  • e.getItemResponses() The method getItemResponses is a method of FormResponse object, not of Event object.
  • message += itemResponses[i]. Again, itemResponses[i] is not a string and coercing it to a string will not be useful.

Corrected version: 

  var items = e.source.getItems();
  var townItem = items.filter(function(i) {
    return i.getTitle() == 'Town';
  })[0];

  var email = "department." + e.response.getResponseForItem(townItem).getResponse()
                             .toLowerCase().replace(/\u00e4/g, "ae").replace(/\u00f6/g, "oe").replace(/\u00fc/g, "ue").replace(/\u00df/g, "ss")
                          + "@domain.com";

  var itemResponses = e.response.getItemResponses();
  var subject = "New submit";
  var message = "";

  for(var i in itemResponses) 
    message += itemResponses[i].getResponse() + "\n\n";
Related Topic