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@email.com']);
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](https://productforums.google.com/forum/#!topic/docs/gnrD6_XtZT0), 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](https://stackoverflow.com/questions/16075446/how-to-protect-the-apps-script-code-in-a-google-spreadsheet) could help you figure out. — MαπμQμαπkγVπ.0, Aug 17 '18 at 08:51

score 0 · Answer 1 · answered Jan 12 '19 at 08:31

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 Answers1