1

I recorded a macro using the traditional way (GUI) to protect a range. The following script was generated.

function protect() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C:C').activate();
  var protection = spreadsheet.getRange('C:C').protect();
  protection.setDescription('test');
};

However, when I apply that macro on other sheets, anybody (say user X) can edit the range. When I check for the protected range, it shows that the owner and user X are permitted to edit. This shouldn't happen.

However, when I manually set permissions then everything works fine.

How to solve this?

1

1 Answer 1

1

I found a solution thanks to this answer.

Using this, I gave the current user (the one running the macro i.e. myself) edit permission and removing other users. So the resulting script becomes as follows:

function protect() {
  var spreadsheet = SpreadsheetApp.getActive();
  spreadsheet.getRange('C:C').activate();
  var protection = spreadsheet.getRange('C:C').protect();
  protection.setDescription('test');

  var me = Session.getEffectiveUser();
  protection.addEditor(me);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) {
    protection.setDomainEdit(false);
  }
};

Remember if you need to protect several ranges then you will have to create several protection variable and hence you will need to write that block again for each protection variable. Looping is recommended but I haven't tried it.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.