I need help setting up cell protection in Google Sheets where users can only modify specific rows based on their identity. I want to use Google Sheets built-in protection features if possible, maybe combining named ranges with lookup functions, but I’m open to other approaches too.
Here’s what I’m trying to achieve:
User
Column A
Column B
Sarah
Sarah can modify this cell
Sarah can edit here too
Sarah
Another row Sarah controls
More editable content for Sarah
Mike
Mike has access to this data
Mike can change this info
Sarah
Sarah gets this row as well
Additional content for Sarah
Basically I want each person to only edit rows where their name appears in the first column, but they shouldn’t be able to change the name itself. The protection should work automatically when new rows get added.
try the INDIRECT function with named ranges. I built something like this - created a named range that updates with a FILTER formula. set up a range called “sarah_rows” that equals FILTER(A:C,A:A=“Sarah”), then protect everything except those dynamic ranges. takes some work to set up, but it updates automatically when you add rows. way easier than scripts.
Sheet-level permissions work way better than protecting individual cells. Had the same problem with a client tracking sheet - salespeople only needed their own records. Skip the cell protection headache. I made separate sheets for each user with IMPORTRANGE formulas that pull their specific rows from a master sheet. Added a simple dashboard with buttons linking to everyone’s individual sheet. The master stays read-only for most people, but each person gets full editing on their filtered view. New data flows back to the master automatically. Way cleaner than juggling protected ranges, and Google Sheets handles permissions through normal sharing settings.
Google Apps Script is your best bet for dynamic protection based on user identity in Google Sheets. The built-in features just don’t cut it for this kind of customization. You’ll need to create a script that triggers on edit and manages protective ranges based on the current user’s email and the values in column A. The script clears existing protections and sets new ones as edits happen - sounds heavy but it runs smoothly. I built something similar for a project management sheet where team leads could only edit their own rows. The key is using getActiveUser().getEmail() to identify who’s editing and match it against usernames in column A. Just make sure column A stays protected so nobody can mess with it. Works great as you add new entries too.