Show or Hide Multiple Rows Based on Cell Value

Question

I'm trying to create a spreadsheet that hides rows based off of a certain cell (B2) value.

I'm having issues where sometimes my code doesn't on certain if statements but works on others. For instance, the, "Kombucha" and, "Koji" if statements don't work but the, "Sweet Fermentation" does.

Spreadsheet is here and below is my code:

function onEdit2(e) {
var sh=e.range.getSheet();
var acid=[4, 6, 8];
if(sh.getName()=='Master' && e.range.columnStart==2 && e.range.rowStart==2) {
e.source.toast(e.value);
if(e.value=="Acid") {
  sh.hideRows(12,73);
  sh.hideRows(33,52);
  sh.hideRows(53,72);
  sh.hideRows(73,84);
  sh.showRows (9,11);
}
if(e.value=="Kombucha") {
  sh.hideRows(53,72);
}
 if(e.value=="Koji") {
  sh.hideRows(12,32);
}
  if(e.value=="Sweet Fermentation") {
  sh.hideRows(12,61);
}
else{
  sh.showRows(1,32);
  sh.showRows(33,52);
  sh.showRows(53,72);
  sh.showRows(73,84);
}
if(e.value=="All"){
  sh.showRows (1,100)
   }
 }  
}

Thank you so much for your help. I'm very green to Javascript and would like to learn to make better spreadsheets for my company.

Your hide and show ranges appears to be overlapping so I don't understand what you trying to accomplish it doesn't make much sense to me. Perhaps you could explain what you hope to accomplish. The first parameter in hideRows() is row number and the second is the number of rows that follow that you want to hide and same with show rows. — Cooper
I'd like to show or hide rows based on the dropdown in B2. So if the dropdown is, "Kombucha", I'd like to hide rows 11 and 41-84. If it's, "Sweet Fermentation", I'd like to hide 11-72. Each row represents a different process flow for that category so they need to uniquely show and hide different rows. Heard on the first parameter being the row number and second is the number of rows that follow. I'll go in and edit right now. — Dustin Nguyen
I think you need to reread the documentation for hideRows() and showRows() I don't think you understand what the parameters mean — Cooper

Cooper Cooper · Accepted Answer · 2021-08-09T18:24:00

Try this:

function onMyEdit(e) {
  e.source.toast(e.value);
  var sh = e.range.getSheet();
  
  if (sh.getName() == 'Sheet0' && e.range.columnStart == 2 && e.range.rowStart == 2) {
    e.source.toast('cond')
    if (e.value == "Acid") {
      sh.hideRows(3, 8);
    }
    if (e.value == "Kombucha") {
      sh.hideRows(12, 10);
    }
    if (e.value == "Koji") {
      sh.hideRows(23, 10);
    }
    if (e.value == "All") {
      sh.showRows(1, 100)
    }
  }
}

None of these are overlapping so it's easy to see how it work. The problem you have was that the else clause on sweet fermentation was always showing rows that you were trying to hide from above.

function onMyEdit(e) {
  e.source.toast(e.value);
  var sh = e.range.getSheet();
  
  if (sh.getName() == 'Sheet0' && e.range.columnStart == 2 && e.range.rowStart == 2) {
    e.source.toast('cond')
    if (e.value == "Acid") {
      sh.hideRows(3, 8);
    }
    if (e.value == "Kombucha") {
      sh.hideRows(12, 10);
    }
    if (e.value == "Koji") {
      sh.hideRows(23, 52);
    }
    if(e.value == "Sweet Fermentation") {
      sh.hideRows(75,25)
    } else {
      sh.showRows(75,25);
    }
    if (e.value == "All") {
      sh.showRows(1, 100)
    }
  }
}

Show or Hide Multiple Rows Based on Cell Value

1 Answers