CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Leslie
Leslie

Reputation: 21

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

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);
    }
}

Upvotes: 1

Views: 3453

Answers (1)

Leslie
Leslie

Reputation: 21

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]']); 
    }

Upvotes: 1

Related Questions