I have a Google Sheets document that multiple people can edit. When someone finishes working on a row, they tick a checkbox in column AC to show that the item is ready for processing.
I need to automatically protect these rows when the checkbox gets marked. Only myself and the sheet owner should be able to edit protected rows. I also want the ability to unprotect rows if someone checks the box by mistake.
The checkbox column is AC and I want to protect the entire row from column A to AG when the box is checked.
I tried using the built-in protection features and recorded macros but I think a custom script would work better for this situation. Has anyone done something similar before?
You’re on the right track! I built something similar for our inventory sheet last year and hit a few snags that might help you avoid some pain. Use getRange().protect() with Protection.addEditor() to control who can edit protected rows. Just make sure you run SpreadsheetApp.getActiveSheet().getProtections() first to check what’s already protected before adding new ones. One gotcha - the script crashed when multiple users triggered it at once. I fixed it by adding Utilities.sleep(100) to the onEdit function. Also don’t forget to handle unchecked boxes with removeProtection(). Once you nail the logic for detecting checkbox changes in column AC, it runs pretty smoothly.
I built something almost identical for our project tracker about six months back. Use an onEdit trigger that fires when someone toggles the checkbox in column AC. The script checks if they edited column AC, then protects or unprotects that row based on the checkbox value. For protection settings, just add user email addresses to the protection object. Pro tip - always check if protections already exist on the row before adding new ones, or you’ll get errors. Also add error handling for users who can’t modify protections. Works like a charm once it’s set up. I can share the basic structure if you want, but onEdit beats Google Sheets’ built-in conditional formatting protection every time.