Using a script to process form input every time a form is submitted

Question

I'm trying to have it that every time I submit a form, it adds automatically some data. This is the script I have:

function onFormSubmit(Test) {    
  var sheet = SpreadsheetApp.getActiveSheet(); 
  var row =  SpreadsheetApp.getActiveSheet().getLastRow();
  sheet.getRange(row,3).setValue(1);
}

Basically, I want to submit the script and the third row will automatically show 1 for every line. The end goal is the add a function in place of 1. So let say the form is a number, I want the ability to add a function to add 4 to that and display the results.

Answer 1

If the data you want to use is in, say, column E (5th column), then instead of

sheet.getRange(row,3).setValue(1);

you would have

sheet.getRange(row,3).setValue(sheet.getRange(row,5).getValue() + 4);

---

Or, if the computation involves several values in that row, say in the first ten columns, then get the row all in once:

var values =  sheet.getRange(row, 1, 1, 10).getValues(); 
var output = values[0][4] + 2*values[0][7] - 3*values[0][9];
sheet.getRange(row, 3).setValue(output);

In the latter version, JavaScript array indexing is used, where indices begin at 0, unlike in Sheets. So, values[0][4] means the zeroth row, fourth column in the fetched range; in practice, column E of the row that was fetched.

---

Also, the approach with getLastRow() may skip some entry if two arrive simultaneously. If you want to guard against that, get all the data and process any rows that aren't yet. Like this:

var values =  sheet.getDataRange().getValues(); 
for (var i=0; i<values.length; i++) {
  if (values[0][2] === "") { 
    var output = values[0][4] + 2*values[0][7] - 3*values[0][9];
    sheet.getRange(i+1, 3).setValue(output);
  }
}