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

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

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.

ReplyDeleteThis 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)!!

ReplyDeleteAfter adding this script, all subsequent submissions are replicating the very first submission.

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

ReplyDeleteyou can also have some additional columns that would automaticly calculate things by using an arrayformula()

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

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

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

ReplyDeleteOld thread, hopefully there's help at the end of the tunnel though :P

ReplyDeleteSo, 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? :-)

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

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

ReplyDelete