1

I've read through countless different posts that are similar to this topic, but have yet to find something that really solves the exact issue I'm looking to fix.

My script currently reads the value of a particular cell, and depending on it, hides X amount of rows beneath. I've seen scripts written where any time the text appears in the column, that specific row (with the matching text) hides -- but let me be clear this is NOT what I'm looking for.

The first piece of my code works flawlessly, including all of the else if statements. However, when trying to add in a separate If statement based on DIFFERENT cell, I can't seem to get that to work.

I need to keep this in one OnEdit I believe, otherwise it would be super simple?

I think I am close, but hoping someone can point me in the right direction.

function onEdit(e){

  if(e.range.getA1Notation() === 'B13' && e.range.getSheet().getName()=='Temp1'){
    if(e.value=='Base1') {
      e.range.getSheet().showRows(14,4);
    } else if(e.value=='Base2') {
      e.range.getSheet().hideRows(15,3);
    }
    else if(e.value=='Base3') {
      e.range.getSheet().hideRows(15,3);
    }
   else if(e.value=='Base4') {
      e.range.getSheet().showRows(14,3);
   }
   else if(e.value=='Base5') {
      e.range.getSheet().hideRows(17,1);
   }
    else if(e.value!== "") {
      e.range.getSheet().hideRows(14,4);
    }
  if(e.range.getA1Notation() === 'B5' && e.range.getSheet().getName()=='Temp1'){
    if(e.value=='Base10') {
      e.range.getSheet().showRows(10,2);
    }
  }
}
}

1 Answer 1

1

The following block "doesn't work" because it's inside of the first if block

if(e.range.getA1Notation() === 'B5' && e.range.getSheet().getName()=='Temp1'){
    if(e.value=='Base10') {
      e.range.getSheet().showRows(10,2);
    }

try

function onEdit(e){

  if(e.range.getA1Notation() === 'B13' && e.range.getSheet().getName()=='Temp1'){
    if(e.value=='Base1') {
      e.range.getSheet().showRows(14,4);
    } else if(e.value=='Base2') {
      e.range.getSheet().hideRows(15,3);
    }
    else if(e.value=='Base3') {
      e.range.getSheet().hideRows(15,3);
    }
   else if(e.value=='Base4') {
      e.range.getSheet().showRows(14,3);
   }
   else if(e.value=='Base5') {
      e.range.getSheet().hideRows(17,1);
   }
    else if(e.value!== "") {
      e.range.getSheet().hideRows(14,4);
    }

  } // end of first if code block

  if(e.range.getA1Notation() === 'B5' && e.range.getSheet().getName()=='Temp1'){
    if(e.value=='Base10') {
      e.range.getSheet().showRows(10,2);
    }
  }

}


Just add another if statement similar to if(e.range.getA1Notation() === 'B13' && e.range.getSheet().getName()=='Temp1'){} just replace 'B13' by the other cell reference.

NOTES 2: Bear in mind that a hide rows is a slow operation and if your onEdit function executes multiple hide rows operations it that might fail when it exceeds the 30 execution time limit for simple triggers.

5
  • Hmm thanks @Ruben - that's exactly what I tried in the bottom line of my code, but it wasn't working properly. I thought that would be the fix. Maybe it was timing out in that case? Does anything potentially look off to you there? May 20, 2020 at 14:05
  • Click on View > Executions to open the executions page of your project on script.google.com. There should be an entry for every time that the onEdit function was ran and if an error occurred it should be logged there.
    – Rubén
    May 20, 2020 at 14:30
  • Forget my previous comment, the bottom of your code is in the wrong place. I edited my answer to show you the right place.
    – Rubén
    May 20, 2020 at 14:36
  • OMG I hate myself! Thanks so much, trust me I spent a lot of time trying to figure out what could be the issue!! May 20, 2020 at 14:40
  • @AnthonyMadle Please checkout What should I do when someone answers my question?
    – Rubén
    May 20, 2020 at 14:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.