onEdit doesn't trigger for cell changes via spreadsheet functions(vlookup)

Question

I want to trigger a function when a cell changes from a vlookup function.

When I manually change a cell(column 2) value, below code works fine.

function onEdit(e)
{
  var range = e.range;
  var newValue = e.value;
  var oldValue = e.oldValue;
  var range = e.range;
  if (range.getColumn() == 2){
    Browser.msgBox('Old value : '+ e.oldValue +' - new value : '+ e.value);
  }
}

But when I change it with vlookup cell function it doesn't trigger.

How can I achieve this?

Answer 1

A VLOOKUP formula will be recalculated when any of its paramenters is changed, so instead of range.getColumn() == 2 look for the edits done on the VLOOKUP precedents.

Let say that your vlookup formula is =VLOOKUP(D1,E:F,2,0), instead of range.getColumn() == 2 use range.rowStart === 1 && range.columnStart === 1 || range.columnStart === 5 || range.columnStart === 6.

Also you will have to change the Browser.msgBox parameter, instead of e.value you might will have to someway grab the VLOOKUP formula result. (there aren't enough details for being more specific). If you really need to the the previous result of VLOOKUP you will have to set a way to keep the old value, i.e., you could use the PropertiesService to keep the result of VLOOKUP, and on each edit first, grab the value, then update the corresponding property.