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 = "firstaddress@example.com, anotheraddress@example.com";
  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.

automatically adding column data to google form submissions with a script

I've often come across the issue of wanting to manipulate data that has been submitted with a Google Spreadsheets Form. For example, automatically marking and totaling formative quizzes where students submit their answers in a Google Form.

Unfortunately when a user submits a form, a new row with those data is inserted on the spreadsheet. This means any formulas that you've manually added to the Spreadsheet will be above or below that row.

To solve this issue, I wrote a Script that copies (to that inserted row) the contents of the columns you've added in the first row.


function addFormula() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;
  var startColumn = 8;
  var numberRows = 1;
  var numberColumns = 15;
  var lastRow = sheet.getLastRow();
  var sourceRange = sheet.getRange(startRow, startColumn, numberRows, numberColumns);
  var destinationRange = sheet.getRange(lastRow, startColumn, numberRows, numberColumns);
  sourceRange.copyTo(destinationRange);
};


The meanings of the variables are:
sheet is a shortcut so we don't have to keep typing SpreadsheetApp.getActiveSheet()
startRow is the row number that's the source of your formula that you want to copy
startColumn is the column number where your source formula starts
numberRows should usually be 1, it's the number of rows that you would like to copy each time
numberColumns is the number of columns that contain your source formula
lastRow is a shortcut so we don't have to type sheet.getLastRow() when we want to use it
sourceRange collects together the information to tell copyTo where to get the data
destinationRange collects together the the information to tell copyTo where to put it


If you prefer, this could also be done in a single line without all of the variable declarations:


function addFormula() {SpreadsheetApp.getActiveSheet().getRange(2, 8, 1, 15).copyTo(SpreadsheetApp.getActiveSheet().getRange(SpreadsheetApp.getActiveSheet().getLastRow(), 8, 1, 15))};


To add this script to your spreadsheet of data from a Google Form

1. open the spreadsheet and under the Tools menu choose Script editor...
2. under "Create script for" click Spreadsheet
3. delete everything in the Code.gs pane and replace it with the script from this blog post
4. if necessary, change the numbers for the variables to what they should be for your spreadsheet
5. under the File menu click Save
6. under the Resources menu click Current script's triggers...
7. if you haven't already named your project, do so now in the box that comes up
8. click No triggers set up. Click here to add one now.
9. in the third drop-down list, select On form submit
10. click the Save button

And you're done. The script will run whenever a user submits the form, and it will copy the formulas that you've set up on the first line of submitted data. Leave a comment below if this works for you or if you have any questions.