protectRowForEditors(sheet, row, keepEmails) locks one row so only the people you list (and you) can edit it. If the row already has a protection, it does nothing and returns null, so a trigger can call it twice without stacking up duplicates.
When to use
- Locking an order, approval, or timesheet row once it's done
- Stopping edits to rows that other formulas or reports depend on
- Any row-by-row sign-off flow
How to use this snippet
/**
* Protect one row, leaving only keepEmails (plus the script runner) as editors.
* Skips rows that are already protected, so it is safe to run twice.
*
* @param {GoogleAppsScript.Spreadsheet.Sheet} sheet
* @param {number} row 1-based row number
* @param {string[]} keepEmails editors who can still change the row
* @return {GoogleAppsScript.Spreadsheet.Protection|null} null if already protected
*/
function protectRowForEditors(sheet, row, keepEmails) {
var existing = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE).filter(function (p) {
var r = p.getRange();
return r.getRow() === row && r.getNumRows() === 1;
});
if (existing.length) return null; // already locked
var keep = (keepEmails || []).map(function (e) { return String(e).toLowerCase(); });
var me = Session.getEffectiveUser().getEmail().toLowerCase();
var p = sheet.getRange(row, 1, 1, sheet.getMaxColumns()).protect()
.setDescription('Row ' + row + ' locked');
if (keep.length) p.addEditors(keep);
var remove = p.getEditors().filter(function (u) {
var e = u.getEmail().toLowerCase();
return e !== me && keep.indexOf(e) === -1;
});
if (remove.length) p.removeEditors(remove);
if (p.canDomainEdit()) p.setDomainEdit(false);
return p;
}
Example
function lockRow7() {
var sheet = SpreadsheetApp.getActive().getSheetByName('Orders');
protectRowForEditors(sheet, 7, ['manager@example.com']);
}
Notes
- The spreadsheet owner can always edit protected ranges. Protection keeps everyone else out, not the owner.
- The account running the script stays an editor, otherwise it couldn't manage the protection later.
- Domain-wide edit access is switched off for the row too.
Happy Coding!
