Toggle rows hide/show between top down checkbox

user_1582596 0
Viewed 63

trying to make a semi/manual tree structure. if check box = true. in a topdown manner. it will look for another check box in the downside direction and unhide rows. i.e. show A2 to A9. if box = false then, Hide: A11 till A19. if there is no check box until the last row it will hide/show all the rows. i.e. B23/B2000. it's a top-down approach so the only action only is limited to a particular column and between the boxes cells are blanks.

function onEdit(e){
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = e.range;
  const editedValue = e.value;
  if (range.getA1Notation() === "A1" && editedValue === "TRUE") {
    sheet.unhideRow(sheet.getRange("A:A9"));
  } else if (range.getA1Notation() === "A1" && editedValue === "TRUE") {
    const firstRow = 1;
    const lastRow = sheet.getLastRow();
      if (pValue === true) {
        sheet.hideRows(i + firstRow);
      }
    });
  } else if (e.range.columnStart === 9 && editedValue === "TRUE") {
    sheet.hideRows(range.rowStart); 
  }
}

enter image description here

enter image description here

google-apps-script google-sheets
1 Answers

Solution:

You can use this script to check the rows below the edited checkbox and hide/show rows accordingly:

function onEdit(e){
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = e.range;
  const editedValue = e.value;
  const row = range.getRow();
  const col = range.getColumn();
  const lastRow = sheet.getMaxRows();
  var data = sheet.getRange(row+1,col,lastRow-row+1,1).getValues();
  for (var i = 0; i < data.length; i++) {
    if (data[i][0] === true || data[i][0] === false) {
      ++i; 
      break;
    }
  }
  if (i > 1) {
    if (editedValue === "FALSE")
      sheet.hideRows(row+1,i-1);
    else if (editedValue === "TRUE")
      sheet.showRows(row+1,i-1);
  }
}

Sample Output:

enter image description here

user_146060490
Related