Google Sheets – Hide Row When Specific Cell is Filled

google sheets

I am trying to hide a row of cell when a specific cell is filled in on Google Sheets

For example:

On row 2 cell E2 is filled in with "1", I want to be able to hide the entire second row.

Is there a possible way of doing this?

Best Answer

You need a script for this. This script runs on every edit (onEdit), checks whether the edit is in the 5th column (E), and if the new value is 1, then it hides the row of that cell.

function onEdit(e) {
  if (e.range.getColumn() == 5 && e.value == 1) {
    e.range.getSheet().hideRows(e.range.getRow());
  }
}

Code

function myFind() {
  var ss = SpreadsheetApp.getActive(), output = [];
  var findData = ss.getSheetByName('fr').getDataRange().getValues();
  var searchData = ss.getSheetByName('cql').getDataRange().getValues();

  for(var i=1, iLen=findData.length; i<iLen; i++) {  
    for(var j=0, jLen=searchData.length; j<jLen; j++) {
      for(var k=0, kLen=searchData[0].length; k<kLen; k++) {
        var find = findData[i][0];
        if(find == searchData[j][k]) {
          output.push([find, "row "+(j+1)+"; "+"col "+(k+1)]);
        }
      }
    }
  }
  return output;
}

Explained

Both data ranges are "captured" at once via the .getDataRange.getValues() method. The 2d array, that's being returned, includes empty rows and columns. This means that all we need to do is correct for the zero based array and we have a row and column index. Through the iterations (note the var i=1 to skip the header), the result is being pushed into an output array. Finally, the output is returned.