How to calculate a range of cells on editing the rage in the Google Spreadsheets using Script-CodePudding

What I am trying to do is, whenever any cell in the range (B1:B10) is edited I want to sum up the values within the range (B1:B10) and add this to cell B11. I used the following code but it adds the formula into the cell. I want to put only the summed value, not the formula itself.

function onEdit(e) {
  var ss = e.source;
  var cell = e.range;

  if(cell.getA1Notation() === "B1:B10" && ss.getActiveSheet().getName() == "TEST"){
    ss.getActiveSheet().getRange("B11").setFormula("=SUM(B1:B10)");
  }    

}

I tried to find an answer on Google, but I couldn't find it.

CodePudding user response：

Maybe you could just get rid of the code and "hide" the formula in another cell. Let's say you need to have in A11 the text "TOTAL", then you can delete first what is in B11 and then put in A11:

={"TOTAL",SUM(B1:B10)}

This way it will expand unto B11 but if you click on that cell you'll only see the value, not the formula

PS: if you try this disable or delete the code

CodePudding user response：

Use Array.filter() and Array.reduce(), like this:

/**
* Simple trigger that runs each time the user hand edits the spreadsheet.
*
* @param {Object} e The onEdit() event object.
*/
function onEdit(e) {
  let sheet;
  if (e.range.columnStart !== 2
    || e.range.rowStart > 10
    || (sheet = e.range.getSheet()).getName() !== 'TEST') {
    return;
  }
  const rangeToWatch = sheet.getRange('B1:B10');
  const targetCell = sheet.getRange('B11');
  const total = rangeToWatch.getValues()
    .flat()
    .filter(value => typeof value === 'number')
    .reduce((sum, current) => sum   current, 0);
  targetCell.setValue(total);
}