I’m working with a Google Sheets document where users fill in data in columns B and C from rows 5 to 22. There’s a status column (column F) that shows either “Active” or “Inactive” for each row.
My sheet has protection enabled for most areas except B5:C22 where users can enter data. The sheet recalculates automatically every few minutes.
I want to create a script that reads column F and when it finds “Inactive”, it should protect columns B and C for that specific row so only I can edit them. The script runs every few minutes but I’m having issues:
Each time it runs, new protected ranges get created instead of updating existing ones
The removeEditors method isn’t working properly to restrict access
Here’s my current approach:
function protectRows() {
var spreadsheet = SpreadsheetApp.getActiveSheet();
for (var row = 5; row <= 22; row++) {
var statusCell = "F" + row;
var cellValue = spreadsheet.getRange(statusCell).getValue();
if (cellValue == "Inactive") {
var rangeToProtect = "B" + row + ":C" + row;
var protectedRange = spreadsheet.getRange(rangeToProtect).protect();
var label = "Protected Row " + row;
protectedRange.setDescription(label);
var currentEditors = protectedRange.getEditors();
protectedRange.removeEditors(currentEditors);
}
}
}
How can I fix this so it doesn’t create duplicate protections and properly removes editing access?
Had the same issue building a project tracker in Sheets. Your script keeps creating new protections without checking what’s already there, and removeEditors doesn’t work like you think. First - check existing protections before making new ones. Use getProtections() to see what’s currently protected, then filter by description to find your row protections. If there’s already protection on a row, skip it. Second - removeEditors() just removes specific users, it doesn’t lock things down to owner-only. Try setDomainEdit(false) and setRangeName() instead. Honestly though, I found it’s cleaner to just delete the existing protection and recreate it when status changes. Don’t forget the reverse case - when a row goes from “Inactive” to “Active”, you need to remove protection entirely. Track this by comparing current vs previous status, or check all existing protections and remove ones that don’t match “Inactive” anymore. Add some error handling around protection operations too - they’ll fail if sheet permissions aren’t set right.
First, check existing protections with getProtections() and filter by description. Your removeEditors problem is happening because you’re not restricting it to owner-only access. Add setDomainEdit(false) right after removing editors - that’ll lock it down. Don’t forget to handle the flip side too: remove protections when rows go active again.
You’re creating new protections every time without checking what’s already there. I’ve encountered this same issue with inventory sheets where protection status changes constantly. Your script just keeps adding more protections, resulting in a mess of duplicates. First, retrieve all existing protections with getProtections() and check their descriptions to determine which rows are already protected. Only add new protections for rows that actually require them. Regarding the editor removal issue - removeEditors() won’t grant you owner-only access. After removing editors, use setDomainEdit(false) if you’re in a workspace to effectively lock it down. For a more efficient approach, track what changed instead of performing a full check every time. Store the previous status values and only modify protections when a row transitions from Active to Inactive. This reduces API calls and minimizes permission conflicts. Lastly, remember to clean up; when Inactive rows revert to Active, those protections need to be entirely removed, not just adjusted.