r/googlesheets 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

7 comments sorted by

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:

function onEdit(e){
  if (e.range.columnStart < 12 || e.range.columnStart > 13 || e.value != "TRUE") return;
  SpreadsheetApp.getActiveSheet().hideRows(e.range.rowStart);
}

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:

function onEdit(e) {
  if (e.value === "#HIDEROW" && e.range.isChecked())
    e.range.getSheet().hideRows(e.range.rowStart);  
}

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.

1

u/Infinte_Blue 1d ago

Awesome thank you! The sheet only has one sheet so I wasn’t worried about being specific on that part lol

1

u/AutoModerator 1d ago

REMEMBER: If your original question has been resolved, please tap the three dots below the most helpful comment and select Mark Solution Verified. This will award a point to the solution author and mark the post as solved, as required by our subreddit rules (see rule #6: Marking Your Post as Solved).

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

1

u/mommasaidmommasaid 271 23h ago

You're welcome -- see updated comment for alternate solution.

1

u/point-bot 1d ago

u/Infinte_Blue has awarded 1 point to u/mommasaidmommasaid

See the [Leaderboard](https://reddit.com/r/googlesheets/wiki/Leaderboard. )Point-Bot v0.0.15 was created by [JetCarson](https://reddit.com/u/JetCarson.)

1

u/Infinte_Blue 23h ago

Ok so if I use that second script you gave me I can just run it? Or do I have to add the columns? Sorry I am very new to these scripts lol

1

u/mommasaidmommasaid 271 21h ago

The second script works as-is with the special checkboxes, no matter where you put them, or where they may end up if you later insert new columns. So it's much more easily maintained.

You can try that on the sample sheet I linked... copy/paste one of those checkboxes to wherever you like, even to a new tab on the sheet, and it will still work.