Google Forms Script Help

August 11, 2014 4:27 AM

I work at a school, and I'm trying to create a Google form to help get information out to parents. We have MULTIPLE means of doing that, though, and the problem is that EACH of the communication modes is handled by someone different. For example, one person is in charge of the website, another is in charge of the electronic sign out front, while another takes care of making afternoon announcements. I've created a form that allows teachers to 1. Type the verbiage they'd like to share, and 2. choose from a list of available media (website, sign, etc.). I'm not smart enough to do the next step. Ideally, I'd like Google Docs to automatically send an e-mail to the appropriate person with the verbiage from each submission.

So, if someone creates a message and then checks that they'd like it to be on the website AND the afternoon announcements, I'd like for the webmaster and the afternoon announcer to get an e-mail with the text to be included when the form is submitted (JUST them, though, - - the sign person doesn't need to get copied since they don't need to do anything). My Googling tells me that it should possible to write a script to handle this, but I'm not smart enough to take the examples I've found online and change them to turn them into something that works. Any INCREDIBLY patient mefites out there who would be willing to teach a teacher?

posted by richmondparker to Technology (7 answers total)

I'm a school IT technician.

At my school, all I'd do to handle this requirement is create a handful of well-known internal email addresses ([e-mail], [e-mail], [e-mail]), make sure that those appeared in the global address book and that mails sent to them got redirected to the appropriate people, then announce their availability via the staff mailing list. Fifteen minutes, tops.

If your colleagues are actually not capable of sending a single email to multiple recipients, I'd be dubious about your success rate in teaching them to find and use your new web form.
posted by flabdablet at 5:18 AM on August 11, 2014

Response by poster: You're correct, but the size of my district (~14,000 employees) means that the tech infrastructure is sacrosanct. IT simply won't create five or six "extra" email addresses, and honestly the sheer numbers involved would make it difficult to manage even if they were willing.
posted by richmondparker at 5:41 AM on August 11, 2014

Is there any possibility of using a different tool to do this? It would be extremely easy using wufoo.com & their "rule builder". They also have an educational discount.

My college uses wufoo extensively. I've set up a few google forms to send email, but it was such a hassle that we gave up.
posted by belladonna at 7:04 AM on August 11, 2014

Best answer: Thanks for the opportunity to play with Google Forms, about which I was previously unaware and which may well be useful at school. I've got something working that ought to be pretty close to what you're after. Here's the script part:

/* Send verbiage from form via email */
/* Based on the tutorial at http://www.labnol.org/?p=20884 */
 
function Initialize() {
  var triggers = ScriptApp.getScriptTriggers();
  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  ScriptApp.newTrigger("SendVerbiage")
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onFormSubmit()
  .create();
}
 
function SendVerbiage(row) {
  var targets = {
    'Up': '[e-mail]',
    'Down': '[e-mail]',
    'Around': '[e-mail]',
    'Albuquerque': '[e-mail]'
  }
  try {      
    var subject = 'Verbiage from ' + row.namedValues['Timestamp'];
    var verbiage = row.namedValues['Enter your verbiage here'];
    var selected = row.namedValues['Choose where to send it'][0].split(', ');
    for (i in selected) {
      MailApp.sendEmail(targets[selected[i]], subject, verbiage);
    }
  } catch (ex) {
    Logger.log(ex.toString());
  }
}

The matching form has a textbox labelled Enter your verbiage here and a set of four checkboxes labelled Choose where to send it; the individual checkbox options are Up, Down, Around and Albuquerque.

I have successfully used this form and script to send arbitrary text to several combinations of test email addresses. Let me know if you need more help adapting the script for your own use.
posted by flabdablet at 11:43 AM on August 11, 2014 [1 favorite]

Response by poster: WOW! This looks like it's going to do exactly what I need. I'm going to start messing around with it tonight.
posted by richmondparker at 11:49 AM on August 11, 2014

With regard to this:

"IT simply won't create five or six "extra" email addresses, and honestly the sheer numbers involved would make it difficult to manage even if they were willing."

There's a very strong argument for creating groups for this purpose. Basically as staff come and go and responsibilities are reassigned, the group/contact address remains unchanged and the membership of the group changes. It's also much easier to have backup on tasks for when people are out of the office for any reason. If individual emails have a licensing cost, groups should not.

You can do lots of fun things with Google Forms. Pretty sure you don't need a loop to send multiple emails, but can use a comma-delimited string instead. Also, I've used FormEmailer before now to help someone with this kind of task.
posted by idb at 11:58 AM on August 11, 2014

Pretty sure you don't need a loop to send multiple emails, but can use a comma-delimited string instead.

Oh, good call!

/* Send verbiage from form via email */
/* Based on information found at http://www.labnol.org/?p=20884 */
 
function Initialize() {
  var triggers = ScriptApp.getScriptTriggers();
  for(var i in triggers) {
    ScriptApp.deleteTrigger(triggers[i]);
  }
  ScriptApp.newTrigger('SendVerbiage')
  .forSpreadsheet(SpreadsheetApp.getActiveSpreadsheet())
  .onFormSubmit()
  .create();
}
 
function SendVerbiage(event) {
  function EmailAddress(target) {
    return {
      'Up': '[e-mail]',
      'Down': '[e-mail]',
      'Around': '[e-mail]',
      'Albuquerque': '[e-mail]'
    }[target];
  }
  try {      
    var subject = 'Verbiage from ' + event.namedValues['Timestamp'][0];
    var verbiage = event.namedValues['Enter your verbiage here'][0];
    var addresses = event.namedValues['Choose where to send it'][0]
    .split(', ')
    .map(EmailAddress)
    .join(', ');
    MailApp.sendEmail(addresses, subject, verbiage);
  } catch (ex) {
    Logger.log(ex.toString());
  }
}

posted by flabdablet at 10:55 PM on August 11, 2014