How to set protection for everyone with google script for spreadsheet?

Question

I'm creating a script that copy-paste a sheet. The copied sheet is protected for everyone (except me the owner). When the user click on the button, the script runs and copy-paste my main sheet, the script set a protection to all users, except himself and me (the owner). Here is a part of the script so you can understand :

function protect(){
  var s = SpreadsheetApp.getActive();
  var copy = s.getSheetByName('BEI-').copyTo(s);
  var newsheet = copy.setName(NouvDem);
  var SheetToProtect=s.getSheetByName(NouvDem).activate();
  SheetToProtect.protect();
  var AllProtections = SheetToProtect.getProtections(SpreadsheetApp.ProtectionType.SHEET);
  var MyProtection = AllProtections[0];
  var Users = MyProtection.getEditors();
  MyProtection.removeEditors(Users);
}

How to protect the new sheet from himself (so he can only uses scripts to modify the sheet) ?

Thank you ! :)

Jescanellas Jescanellas · Accepted Answer · 2020-01-16T12:13:58

It's not possible.

When the user makes a copy of your Sheet, he becomes the owner of that copy, therefore he can't limit himself from editing it. Also, is not possible for the user to protect a Range from himself.

The closest solution is setting a Warning but that only shows a skipable pop-up asking the user if he is sure he wants to edit that Range.

However, you could create the copies yourself and share them with the users, then limit the ranges they have access to.

How to set protection for everyone with google script for spreadsheet?

1 Answers