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

RommelTJ Source

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?

google-apps-scriptgoogle-spreadsheetgoogle-docs

Answers

comments powered by Disqus