1

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 ! :)

aynber
  • 22,380
  • 8
  • 50
  • 63
Simon
  • 13
  • 4

1 Answers1

2

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.

Jescanellas
  • 2,555
  • 2
  • 9
  • 20
  • Are you sure he becomes the owner of the sheet ? I mean I'm supposed to still be the owner the whole document, Am I right ? I thought to add a warning if the protection is not possible. Thank you for your answer ! – Simon Jan 16 '20 at 16:15
  • When the user runs the script and copies the Sheet, the new copy is owned by the user, but you still keep the ownership of the original document. Therefore the user can't protect the cells from himself in the copied Sheet. Please consider accepting the answer if it was useful. Cheers! – Jescanellas Jan 16 '20 at 16:43
  • Ok ! Now I understand, thank you ! So there is absolutely no possibility to protect the project from other users ? – Simon Jan 17 '20 at 08:55
  • 1
    From other users, yes, but from the owner of the file, nope. – Jescanellas Jan 17 '20 at 08:59