Thursday, January 17, 2013

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.

11 comments:

  1. This worked great for me! I just wanted to copy the formula from a single cell, so I changed numberColumns to be 1 instead of 15.

    ReplyDelete
  2. This worked perfectly for me as well! Thank you for making the steps so easy to understand (especially for someone who's never written a script for either Google Drive or Excel before)!!

    ReplyDelete
  3. After adding this script, all subsequent submissions are replicating the very first submission.

    ReplyDelete
  4. Nevermind...I went back and reread a little more carefully! It's working perfectly now. Thanks!

    ReplyDelete
  5. you can also have some additional columns that would automaticly calculate things by using an arrayformula()

    ReplyDelete
  6. I keep getting: "The coordinates of the source range are outside the dimensions of the sheet." My 1st formula begins in col 147. Counting col 157, there are 452 cols to the right of that column. What am I missing? Thank you.

    ReplyDelete
    Replies
    1. Check your values for the variables (startRow, startColumn, numberRows, numberColumns) and make sure that they are referencing the correct places that your data can be found.

      Delete
  7. I was sure that I had counted correctly. I clearly had not. "All's well that ends well." Thank you for the reminder!

    ReplyDelete
  8. Old thread, hopefully there's help at the end of the tunnel though :P
    So, theoretically, if I have a formula in H2 (master formula) based off information in C3, C4.. C100000, will this function copy and paste the formula from H2 to calculate the next row?
    Ex:
    (H2 cell) =getLng(D3)
    (D3 cell) 60415 (zipcode)

    Will this function put =getLng(D4..D5...D6) at each new row?

    If not, could you #help with this? :-)

    ReplyDelete
    Replies
    1. If I'm reading that right, it should work. But it doesn't hurt to #try :)

      Delete
  9. I will have to try this when I get home (overnight worker). If it does? Sir, you've made an HUGE impact to poverty :)

    ReplyDelete