0

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

Improve this question

1 Answer 1

Reset to default
0

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)); } }
Improve this answer
Sign up to request clarification or add additional context in comments.

8 Comments

Thank you. The only issues I am having now is that the checkbox in the row that I select the allergen is unselecting itself after the script runs (I would like for it to remain visible) I am going to subsequently use the checkbox in B2 (reset allergens) to clear the checkboxes in C3:P3 and then unhide the rows. Sorry if I wasn't clear.
Here is a screenshot of my code so far (too long to insert into a comment). drive.google.com/file/d/1hnxMNB0D-gkR2VfCLp4cDYsAsLJG38h4/…
remove e.range.setValue("FALSE");
I don't follow links to google accounts because it can reveal my email
That works great. If I could ask one more bit of help from you. The last thing I am getting tripped up with is trying to unhide the hidden rows once I check the reset button in cell B2. I tried to copy the code you wrote above 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 all the checkboxes. Thanks
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.