0

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?

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
SagRU
  • 448
  • 4
  • 17

2 Answers2

2

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.

Jason Nichols
  • 3,739
  • 3
  • 29
  • 49
  • Thanks for your reply! Your solution is quite close to what I need. I have a subquestion, though: is it possible to hide target row and 2 rows above it? I've tried `if(p.getValue()==1) sheet.hideRows(row,-2);` but with no luck. – SagRU Jun 20 '13 at 10:35
  • try: `if(p.getValue()==1) sheet.hideRows(row-2,3)` – Jason Nichols Jul 19 '13 at 18:05
1

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

Mogsdad
  • 44,709
  • 21
  • 151
  • 275
  • "onChange" doesn't fire on formula result change: https://stackoverflow.com/a/41591675 – Kos Dec 13 '17 at 20:36