My Google Apps Script protects whole sheet instead of protecting certain columns and allowing them to be edited by editors

Question

I have this code where I want certain columns to be protected and only be allowed to be edited by certain editors. However, my code just makes the whole sheet except Column I protected while editors can't edit any other Column. I thought that this part of my script

protection.removeEditors(protection.getEditors());

if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
}

was causing the whole sheet protected but when I took it out the whole sheet is still protected. Can someone please help me find out what part of my script is wrong and how I can fix it? I don't have much knowledge in coding or writing script and would appreciate any help.

Edit: Apparently editors can edit but a part of the code is making the protected ranges "view only" as well when I checked on "protected sheets and ranges" under "Data". If someone can help me get rid of the view only then I would really appreciate it."

function OnOpen(){
    // Protect the active sheet except colored cells, then remove all other users from the list of editors.
    var ss = SpreadsheetApp.getActiveSheet();
    var range = ss.getRange("A1:B10");
    var range1 = ss.getRange("D1:E10");
    var protection = range.protect().setDescription('Sample protected sheet');
    var protection1 = range1.protect().setDescription('Sample protected sheet');

    // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
    // permission comes from a group, the script will throw an exception upon removing the group.
    var me = Session.getEffectiveUser();
    var editor = ["[email protected]"]
    var editor1 =["[email protected]"]

    if (me.getEmail() == editor){
        protection.addEditor(editor);  
    }

    if (me.getEmail() == editor1){
        protection1.addEditor(editor1);
    }

    protection.removeEditors(protection.getEditors());

    if (protection.canDomainEdit()) {
        protection.setDomainEdit(false);
    }
}

I tested this function and in my test sheet it works fine. After running the function only specified ranges were protected: Sheet!A1:B10 and Sheet!D1:E10. Did you check protected ranges in Data -> Protected sheets and ranges in your active sheet? — edward
Hey edward thanks for pointing that out. I did check it out and it does allow my editor to edit. However, my script somehow also makes the protected ranges view only. I don't know what part of my code is making it view only or how I should make my code get rid of the view only. — Leslie
for me it's not 100% clear what you want to achieve, but you use protection.removeEditors(protection.getEditors()); which removes editors from protection range: A1:B10. Try firstly to remove this line of code. — edward
Actually I tried this script in a new sheet and went with what you suggested edward. When I viewed the protections my editors aren't even added even after removing that line. It only allows me, the owner, to allow edits. You tested my script are the editors being added for you? And thanks for the help edward I really appreciate it. — Leslie
edward what I want to achieve is to make Sheet!A1:B10 and Sheet!D1:E10 but to make Sheet!A1:B10 editable by certain editors and Sheet!D1:E10 editable by certain editors. — Leslie

Leslie Leslie · Accepted Answer · 2018-07-17T21:23:53

Answer as provided by edward.

function OnOpen(){
    // Protect the active sheet except colored cells, then remove all other users from the list of editors.
    var ss = SpreadsheetApp.getActiveSheet();
    var range = ss.getRange("A1:B10");
    var range1 = ss.getRange("D1:E10");
    var protection = range.protect().setDescription('Sample protected sheet');
    var protection1 = range1.protect().setDescription('Sample protected sheet');

    // Ensure the current user is an editor before removing others. Otherwise, if the user's edit
    // permission comes from a group, the script will throw an exception upon removing the group.
    var me = Session.getEffectiveUser(); 
    protection.addEditor(me); 
    protection1.addEditor(me); 
    protection.removeEditors(protection.getEditors()); 
      if (protection.canDomainEdit()) { 
        protection.setDomainEdit(false); 
      } 
      protection1.removeEditors(protection1.getEditors()); 
      if (protection1.canDomainEdit()) { 
        protection1.setDomainEdit(false); 
      } 
    }
      protection.addEditors(['[email protected]','[email protected]']); 
      protection1.addEditors(['[email protected]','[email protected]']); 
    }

My Google Apps Script protects whole sheet instead of protecting certain columns and allowing them to be edited by editors

1 Answers