Need help with Show/Hide Range of Rows dependent on cell input

[Brittany C.]

I'm a Script beginner and need help with the following:

I am trying to show/hide ranges of rows in one google sheet dependent upon the input in various cells. I am currently using below code for one of the sections of the sheet, but is not working correctly. How would I fix this so that depending on if the user selects "Yes" or "No" in a dropdown cell, a specific range of rows would either be shown or hidden?

Thanks in advance for the help!

var SHEET = "Questionnaire";

var VALUE = "Yes";

var HIDEVALUE = "No";

var COLUMN_NUMBER = 6

var ROW_NUMBER = 42

 

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var activeSheet = ss.getActiveSheet();

  

  if(SHEET == activeSheet.getName()){

    var cell = ss.getActiveCell()

    var cellValue = cell.getValue();

    

    if(cell.getColumn() == COLUMN_NUMBER && cell.getRow() == ROW_NUMBER){

      if(cellValue == VALUE){

        activeSheet.showRows(46,52)

      };

    };

  };

}

function onEdit(e) {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var activeSheet = ss.getActiveSheet();

  

  if(SHEET == activeSheet.getName()){

    var cell = ss.getActiveCell()

    var cellValue = cell.getValue();

    

    if(cell.getColumn() == COLUMN_NUMBER && cell.getRow() == ROW_NUMBER){

      if(cellValue == HIDEVALUE){

        activeSheet.hideRows(46,52)

      };

    };

  };

}

[Chanel Greco]

Hi Brittany

Could you specify what is not working with this code?

I noticed some potential issues with the script. 

• You are using two separate onEdit methods to check the value and then show certain rows. What you actually need is an if/else statement:
  if(cellValue == VALUE)   { show rows xyz}
  else(cellValue == HIDEVALUE) { show rows abc}
• To check for the value (that is "YES" or "NO") you are using the method getActiveCell. That's going to get any cell the person has active when the onEdit trigger fires. Are you sure that specific cell is going to contain the value you're looking for? 
  And it is advised to use getCurrentCell instead of getActiveCell. Check the documentation here: https://developers.google.com/apps-script/reference/spreadsheet/sheet?hl=en#getActiveCell()

[Brittany C.]

Thanks Chanel - that was super helpful!