How do I protect a cell range from editing only by owner?

Question

I have a Google Sheet with an onEdit trigger running Google Apps Script code to add cell range protection. The idea is that after editing a cell, the spreadsheet will be locked from editing. You should not be able to delete or modify a cell after adding your data, so the spreadsheet can act as a ledger of sorts.

Anyway, here is the relevant code from the trigger:

// Remove existing protected ranges
var protections = sheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
for (var i = 0;i<protections.length;i++) {
    if (protections[i].getDescription() === sheetname + range) {
        protections[i].remove();
    }
}

// Add Protected Range to prevent further editing up to the second from last row
var lastRow = SpreadsheetApp.getActiveRange().getLastRow() - 1;
var protectedRange = selectedSheet.getRange("A1:J".concat(lastRow));
var protection = protectedRange.protect().setDescription(sheetName() + ' Protection Range');

// Note: The spreadsheet owner is always able to edit protected ranges and sheets. 
var sheetOwner = SpreadsheetApp.getActiveSpreadsheet().getOwner();
protection.addEditor(sheetOwner); // Owner is the only one who can edit.
protection.removeEditors(protection.getEditors()); // Remove all editors.
if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
}

This code successfully adds the protection to the cells, however, it is always Editable by the current logged in user (so the Owner + current user can Edit), thus defeating the protection.

I have tried something like the following:

var sheetOwner = SpreadsheetApp.getActiveSpreadsheet().getOwner();
var me = Session.getEffectiveUser();
protection.addEditor(sheetOwner);
protection.removeEditor(me);

and also something like the following:

protection.addEditors(['[email protected]']);
protection.removeEditors(protection.getEditors());

but it seems like no matter what I do, the current user always gets granted with Edit permissions. Why is that? How do I remove the Edit permissions of the current user for the protected range?

Try to check the script created in this post by Google Product Help Forum, additionally, you don't also want the users to edit the script of your spreadsheet, so you need another solution which is to protect the script and this SO post could help you figure out. — MαπμQμαπkγVπ.0

Praveen Rathi Praveen Rathi · Accepted Answer · 2019-01-12T08:31:25

I am not any code writer at all but somehow managed to restrict users from editing once they enter any data in a cell.

I am using below simple code and trigger is on edit. This auto lock a cell once input is given. Just mention email id of all users (whom edit right is given) in remove editors in below code

function LOCKALL() {
var spreadsheet = SpreadsheetApp.getActive();
var protection = spreadsheet.getActiveRange().protect();
protection.setDescription('LOCKALL')
.removeEditors(['mention email id' , 'mention email id']);
};

How do I protect a cell range from editing only by owner?

1 Answers