1

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).

1

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";
  • Thanks so much for your help! Works great now. May I ask you two more things? I've tried to prefix all responses with their corresponding item titles like so for(var i in itemResponses) message += "\n\n" + items[i].getTitle() + ": " + itemResponses[i].getResponse();. However, this doesn't work too well with optional items. The itemResponses array appears to be shorter by one entry for every omitted optional item. This breaks the grouping of items to responses and makes the loop terminate before the last item is reached. Also, is there a way to make the repsonses bold in the email? – Casimir Jan 15 '18 at 1:02
  • 1
    Yes, getItemResponses is fragile like that since it just lists responses in a row. It's better to use the array items = e.source.getItems(); of all items in the Form, and then get, for each of them the corresponding response with e.response.getResponseForItem(item). Then you'll know what responses were given for what items. For bold, use HTML <b>text</b> in htmlBody of the message: see sendMail docs. – user135384 Jan 15 '18 at 2:00
  • When I use for(var i in items) message += "\n\n" + items[i].getTitle() + ": " + e.response.getResponseForItem(items[i]).getResponse(); the script fails. Is that because getResponse() sometimes returns String[] or String[][] and + doesn't know how to concatenate strings with arrays of strings? – Casimir Jan 15 '18 at 11:53
  • 1
    As documentation says, getResponse() can return different types. Use Logger.log() to find out exactly what it returns. (If you can't access the log, which is strange, just have them be mailed to you in JSON notation, with JSON.stringify(... response) – user135384 Jan 15 '18 at 18:11

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.