How do you show/hide rows based on a particular cell value?

Question

I am attempting to put some show/hide row logic into a google sheet based on a response from a data validation list.

If the user selects the value 'General Account Call' from the data validation list in cell C42 I would like to show rows 43 through to 53 and rows 78 through to 101. Also if a user ticks a check box in cell C40 I would like to show row C41.

I don't know if I can 'monitor' multiple cells with the onEdit function.

I have found a couple of similar(ish) scripts which hide a row based on a value in a cell in the same row and have tried amending them to no avail. I have then tried going back to basics and putting something to monitor a cell and hide a different row, again to no avail

function onEdit(e) {
  var ss = SpreadsheetApp.getActive()
  var sheet = SpreadsheetApp.getActiveSheet()
  var cell = sheet.getRange('D39')
  var cellContent = cell.getValue()

  if(cellContent === Y) {
    Sheet.hideRow(40)
  }
}

Jescanellas Jescanellas · Accepted Answer · 2019-07-19T11:33:34

Your code should be:

function onEdit() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getActiveSheet();
  var cell = sheet.getRange('D39');
  var cellContent = cell.getValue();

  if(cellContent == 'Y') {
    sheet.hideRows(11); 

  }
}

There were a few things wrong:

Even if the ss and sheet declaration work, you were getting the Sheet from the SpreadsheetApp and not from ss, so ss was not being used.
cellContent == 'Y' is the correct way to compare the cell to a String, if you remove the quotes, it will interpret it as a variable Y that doesn't exist. Also, === is not necessary as it checks the type and the value, and we only want the value. In fact, it will return false even if the values are the same but are a different type
You wrote Sheet.hideRow instead of sheet.hiderow. Javascript is case-sensitive, so be careful with that!
The method hideRow hides the rows in a given Range, so we have to use hideRows, were you can use the integer (11 in this case) to hide the entire row.
You didn't put any semicolons ;

You didn't get any of these errors as I suppose you were testing the code changing the value of D39. If you execute the code manually, all of these will show up.

So, in order to hide several rows depending of a few cells, you have to get the event information (e), and get the cell that is being edited.

Do an if like this:

  var cell = e.range.getA1Notation(); //Gets the Cell you edited
  var cell_value = e.range.getValue(); //Gets the value of the cell

  if(cell == 'C42' && cell_value == 'General Account Call'){
    sheet.showRows(43, 10);
    sheet.showRows(78, 23);

  }

  if (cell == 'C40' && cell_value){ //The checkbox value returns true or false
    sheet.showRows(41);
}

The second number in showRows is the number of rows that will be shown.

How do you show/hide rows based on a particular cell value?

1 Answers