Google Sheets Hiding Rows using checkboxes Apps Script

Question

Sort of Newbie so please be kind.

I have been working on this code for hours and I have been making progress ... I think lol ... but I now seem to be stuck.

You will see in the photo here with the idea that I am trying to accomplish. I have a list of different areas of a house that would need to be renovated. Under each area would be all the items that would need to be selected. I would like for example if I deselect the Kitchen section that the entire section of rows under would be hidden and if re-selected they would open back up.

I have figured out how to hide the section if I go a count each row and indicate I would like x number of rows hidden. However multiple people will be using this and I would like the functionality of adding additional areas or items to each list without having to rewrite code.

I just can't figure out how to get it to count the number of rows to the next checkbox in column A so everything between will looks like This once the kitchen area is unchecked.

Any who, this is the code i have thus far:

If someone can please please help me wih this I would greatly appreciate it.

function onEdit(e) {
  var rs = e.range.rowStart + 1;
  var ccc = e.range.getCurrentCell();
  var nd = ccc.getNextDataCell(SpreadsheetApp.Direction.DOWN).getA1Notation().slice(1) - 1;
  if (e.range.columnStart != 1) return;
  if (e.value != "TRUE") {
    SpreadsheetApp.getActiveSheet().hideRows(rs, nd);
  }
  else if (e.value != "FALSE") {
    SpreadsheetApp.getActiveSheet().showRows(rs, nd);
  }
}

Answer 1

Hiding Rows in column B if they are checkboxes

The function assumes a one row and one column offset for the checkboxes in columnB from the checkboxes in columnA and the hides rows as long as it finds checkboxes in the rows. When there is not a checkbox it stops

function onMyEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'Sheet0' && e.range.columnStart == 1 && e.value == "TRUE") {
   if(e.range.offset(1, 1).getDataValidation().getCriteriaType() == "CHECKBOX") {
     let r = e.range.offset(1,1).getRow();
     let i = 0;
     do{
       sh.hideRows(r + i++);
     }while(e.range.offset(1 + i,1).getDataValidation().getCriteriaType() == "CHECKBOX")
   }
  }
}

Demo:

I thought about doing it this way as I went to sleep and it is much faster

function onMyEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'Sheet0' && e.range.columnStart == 1 && e.value == "TRUE") {
   if(e.range.offset(1, 1).getDataValidation().getCriteriaType() == "CHECKBOX") {
     let r = e.range.offset(1,1).getRow();
     let i = 0;
     do{
       i++;
     }while( e.range.offset(1 + i,1).getDataValidation() && e.range.offset(1 + i,1).getDataValidation().getCriteriaType() == "CHECKBOX")
     sh.hideRows(r,i)
   }
  }
}

Hiding and Showing

function onMyEdit(e) {
  const sh = e.range.getSheet();
  if (sh.getName() == 'Sheet0' && e.range.columnStart == 1 && e.value == "TRUE") {
   if(e.range.offset(1, 1).getDataValidation().getCriteriaType() == "CHECKBOX") {
     let r = e.range.offset(1,1).getRow();
     let i = 0;
     do{
       i++;
     }while( e.range.offset(1 + i,1).getDataValidation() && e.range.offset(1 + i,1).getDataValidation().getCriteriaType() == "CHECKBOX")
     sh.hideRows(r,i)
   }
  }
  if (sh.getName() == 'Sheet0' && e.range.columnStart == 1 && e.value == "FALSE") {
   if(e.range.offset(1, 1).getDataValidation().getCriteriaType() == "CHECKBOX") {
     let r = e.range.offset(1,1).getRow();
     let i = 0;
     do{
       i++;
     }while( e.range.offset(1 + i,1).getDataValidation() && e.range.offset(1 + i,1).getDataValidation().getCriteriaType() == "CHECKBOX")
     sh.showRows(r,i)
   }
  }
}

Demo:

Answer 2

You can achieve the same behavior without any script, by grouping the rows. It will be faster and simplier.