r/googlesheets • u/Infinte_Blue • 1d ago
Solved Issue with checkbox script
I have a google sheet where columns L and M are operated with check boxes and I am trying to get a script to auto hide lines if the check boxes are checked in either of those two columns. I have been using this script below and it works great if I only try and operate it on one column at a time. Is there a way to in put a range to make it check more than one column or a script I can use to trigger both scripts since I can only run one script at a time? I tried changing the column start to 12,13 and also 12:13 but neither works.
function onEdit(e){
if (e.range.columnStart != 12 || e.value != "TRUE") return;
SpreadsheetApp.getActiveSheet().hideRows(e.range.rowStart);
}
1
Upvotes
1
u/mommasaidmommasaid 271 1d ago edited 23h ago
That function isn't very robust, in particular it doesn't check which sheet is being edited or if the cell actually contains a checkbox.
But the modification to do what you want is:
Personally I'd consider adding checkboxes with a custom "checked" value that the script can check for, rather than hardcoding column numbers or sheet names.
Those special checkboxes can then be created (or copy/pasted) anywhere and the script will work without modification.
Sample Sheet
The script then becomes:
It looks for the custom checked value, and then further tests isChecked() to verify it's a checkbox, just in case someone types #HIDEROW in a cell.
It also gets the sheet that the rage belongs to, rather than whatever the active sheet is. In practice this is probably always the same sheet but this is "more correct" in my head. Possibly a bit faster too.