SOF friends,
I have a form which has a bunch of numeric fields among some date and string fields.
How to add up the numeric values and include the total as a column in the spreadsheet where it saves the responses? Where do I write the script for this? At the form level or at the spreadsheet level?
Here's what I have at this time, as a trigger on form submit which doesn't work:
function myFunction() {
Logger.log("Spreadsheet: %s", SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName());
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
//sheet.setActiveSelection("M1:M").clear();
var range = sheet.getDataRange();
var values = range.getValues();
var lastrow = range.getLastRow();
var lastcolumn = range.getLastColumn();
Logger.log(lastrow);
Logger.log(lastcolumn);
Logger.log(values[lastrow][10]);
var coll = values[lastrow][6];
var drv = values[lastrow][7];
var clnr = ((values[lastrow][8] != "")? values[lastrow][8]:0);
var dsl = values[lastrow][10];
var gen = values[lastrow][11];
var tot = coll + drv + clnr + dsl + gen;
var singlecell = sheet.getRange(lastrow, 13);
singlecell.setValue(tot);
Logger.log(tot);
}
Example of code to be added to a project bounded to a spreadsheet.
function sumFormResponseItems(e) {
var firstSummand = parseInt(e.namedValues['First summand'][0],10);
var secondSummand = parseInt(e.namedValues['Second summand'][0],10);
var range = e.range;
var row = range.getRow();
var lastColumn = range.getLastColumn();
range.getSheet().getRange(row,lastColumn+1).setValue(firstSummand + secondSummand )
}
After you add the code, add an on form submit trigger to make it work.
The above code sums only the values of the submitted response instead of doing the same for the whole sheet data range that potentially could return you an exceeded execution time limit error because your codes uese an open ended reference.
Regarding if adding the script to a spreadsheet or to a form, without more details any answer will be opinion based.