CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
SagRU
SagRU

Reputation: 448

Is it possible to trigger onEdit function on formula

I have a complicated formula in column P that checks about 6 conditions and returns 1 or 0. I want to hide every row with 1 in column P. I use this script:

function onEdit(e){
  var sheet = e.source.getActiveSheet();
  var r = sheet.getActiveRange();
  if (r.getColumnIndex() == 16 && r.getValue() == "1")
    sheet.hideRows(r.getRowIndex(),1);
}

For reasons unknown, the script doesn't work. It seems that onEdit can't be triggered on formula... Is it really so? How can I make the script work?

Upvotes: 0

Views: 1432

Answers (2)

Mogsdad
Mogsdad

Reputation: 45710

In May 2013, a "Change" trigger was added for spreadsheets, and it delivers an event for formula changes.

Upvotes: 2

Jason Nichols
Jason Nichols

Reputation: 3760

It won't work because the cell with the formula itself isn't being edited. It's not the active range, so r.getValue() != 1, it equals whatever you just typed in, and r.getColumnIndex() should never be 16.

set:

var r=sheet.getActiveRange();
var row=r.getRowIndex();
var p=sheet.getRange("P"+row);

And 

if(p.getValue()==1)
    sheet.hideRows(row,1);

That should work, assuming your conditions are on the same row as your p that you want to test against.

Upvotes: 2

Related Questions