Monday, January 28, 2013

Google script to email form data

I helped someone set up a Google Form with email notifications the other day. This morning they asked if they could be emailed the submitted data, rather than just a link to the spreadsheet. Since there's only one question on the form, that's an easy script:

function onSubmit(e) {
  var timestamp = e.values[0];
  var question1 = e.values[1];
  var address = ",";
  var subject = "Feedback form submitted";
  var body = "Someone submitted the form at " + timestamp + " and said " + question1;
  MailApp.sendEmail(address, subject, body);

This script will send an email to the address(es) in quotes after the var address = . The body of the email will contain the string that is in quotes after var body =  , which includes the timestamp (from column A of the spreadsheet) and what the user submitted in the form (the contents of column B).

To get this to work for you, paste the above code into a new blank script that's associated with a form you've created, and then set up a trigger that runs the function onSubmit(e) when the form is submitted.

To set up the trigger, follow the instructions from this page:

  1. Open or a create a new form, then go to the results spreadsheet of that form.
  2. Click the Unsaved Spreadsheet dialog box and change the name.
  3. Choose Tools > Script Editor and write the function you want to run.
  4. Choose Resources > Current script's triggers. You see a panel with the message No triggers set up. Click here to add one now.
  5. Click the link.
  6. Under Run, select the function you want executed by the trigger.
  7. Under Events, select From Spreadsheet.
  8. From the next drop-down list, select On form submit.
  9. Click Save.
Of course if you have more than one question in your form, you'll need a variable for each of them (e.g. var question2 = e.values[2];) and you'll need to call those variables in the var body =  statement.

Let me know if this works for you.

No comments: