Trigger function when a cell is changed by functions?

Question

The cell has a function =AVERAGE() or =SUM() and is changed, but onedit () does not work, only if the cell is manually changed.

How to create a trigger for this case?

Answer 1

There may be a better way to do this, but if you're using a formula, the user may have made an edit somewhere, that affected the result.

For example, if you have the formula: =SUM('A2:A50'), one way to achieve what you want is verifying if the edit event happened inside that range 'A2:A50'.

Here's an example code that might help:

function onEdit(e){
  var editedRange = e.range

  if (rangeContainsAnotherRange(yourRange,editedRange)){
    // Do whatever you want
  }

}

// Maybe not the most practical way to verify if one range is inside another
function rangeContainsAnotherRange(widerRange, editedRange){
  var editRow = editedRange.getRow();
  var editCol = editedRange.getColumn();

  var widerRangeRowStart = widerRange.getRow();
  var widerRangeRowEnd = widerRangeRowStart + widerRange.getHeight();
  var widerRangeColStart = widerRange.getColumn();
  var widerRangeColEnd = widerRangeColStart + widerRange.getWidth();

  return (editRow >= widerRangeRowStart && editRow <= widerRangeRowEnd 
      && editCol >= widerRangeColStart && editCol <= widerRangeColEnd)

}

Hope that helps.

For the example above, on the onEdit function, the variable yourRange would be a range 'A2:A50'.

Obs: Reference for the second function