CODEWITHSUNDEEP
google-sheetsgoogle-apps-script
scjwolin57
scjwolin57

Reputation: 3

Google Sheets Script, Hide Row(s) Based on Multiple Cell Values Triggered By Checkbox

I need to find a working script to trigger when a checkbox is ticked. I will need it to be able to function for multiple checkboxes (columns) and have the script check cells for a value of "X" in that column. If that value is found in the column then I want to hide that corresponding row

Please refer to the screenshot below of my sheet (Allergen Chart) so that it makes a bit more sense

I need the checkboxes in row 3 [columns C-P] to be the triggers for the script to run, and hide any rows that contain an X in that column

I need to unhide the hidden rows once I check the reset button in cell B2. I tried to use the reuse the following code and use unhide to no avail. let rows = sh.getRange(5, e.range.columnStart, sh.getLastRow() - 4).getValues().flat().map((e, i) => (e == "X") ? i + 5 : '').filter(e => e); rows.forEach(r => sh.unhideRows(r)); How do I need to alter this to unhide the rows after I have reset allergens

Allergen Chart

Upvotes: 0

Views: 121

Answers (1)

Cooper
Cooper

Reputation: 64082

Hiding Rows that contain X in the checked column

function onMyEdit(e) {
  //e.source.toast("entry");
  const sh = e.range.getSheet();
  if (sh.getName() == "Your sheet name" && e.range.rowStart == 3 && e.value == "TRUE") {
    //e.source.toast("gate1")
    let rows = sh.getRange(5, e.range.columnStart, sh.getLastRow() - 4).getValues().flat().map((e, i) => (e == "X") ? i + 5 : '').filter(e => e);
    e.range.setValue("FALSE");
    rows.forEach(r => sh.hideRows(r));
  }
}

Upvotes: 0

Related Questions