Auto entering functions in sheets managed by forms

Question

I want to fill a single column of my sheet with a function, the sheet is being managed by forms. So, whenever a new entry is added it adds a new row and that row will not have the function that I want, meaning that I'll have to go into the data and manage it on my own(which is inconvenient, and would work against what I would want this sheet to do). My data looks something like this:

    A      B       C      D      E      F
1: time, data1, =D1/E1, data2, data3, data4
2: time, data1, =D2/E2, data2, data3, data4
3: time, data1, =D3/E3, data2, data3, data4
4: time, data1, =D4/E4, data2, data3, data4

I want to insert my equation into column C, but every time new data is added through the form the equation will not be in the new row that is added.

Answer 1

I have a sheet-bound script to generate and email templated documents populated with form response data. What you'll see below is definitely NOT pretty/efficient/possibly even advisable..? However, it does accomplish what you are attempting in this post.

It's more or less a sledgehammer grade function to pin a percentage on each submission based on yes/no/blank values for a variety of checksheet-style questions.

The trick - I think - to getting it in the appropriate row and not overwriting data is the placement. The following lines:

var calc = '=round(if(iserror(countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")/(countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")+countifs(INDIRECT("N"&row()&":BA"&row()),"=NO"))),"",countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")/(countifs(INDIRECT("N"&row()&":BA"&row()),"=YES")+countifs(INDIRECT("N"&row()&":BA"&row()),"=NO")))*100,1) & "%"';

var calcc = calc.toString();

var sheet = SpreadsheetApp.getActiveSheet();

var numRo = sheet.getLastRow()
sheet.getRange(numRo, 1).setValue(calcc);

precede the main function:

function onEdit() {... etc

which is triggered on a form submit.

Answer 2

Unfortunately there isn't a way around this because the form inserts a new row into the sheet instead of inserting the data into the current row that contains the function.

However, you can get around this relatively easy by using a Query function on a separate sheet. The query function pulls the submissions as they come while maintaining any functions on the sheet.

Create a new sheet and enter =Query( into a cell and then switch to the submission sheet and click+drag across the columns you would like to capture, switch back to the new sheet and hit enter. This will import the data into the new sheet. Then you can add additional columns/rows with your functions.