Script to add protection and remove the user running it?

Question

We have a worksheet that will have multiple sheets, one in which they will edit and the others we want protected. This is an invoice/quote builder, so every time they have a new one they will be making a copy of the main. Obviously protection doesn't carry over so we are trying to use Script Editor to remove all users except for me and one other.

I couldn't get onOpen() to work so I am just adding a button into the menu that they will have to click (we trust them to do this each time), that will run a function, here's where we are at:

function onOpen() {
      var ui = SpreadsheetApp.getUi();
      ui.createMenu('Custom Menu')
          .addItem('First item', 'testProtect')
          .addToUi();
}


function testProtect(){
      var sheet = SpreadsheetApp.getActiveSheet();
      var protection = sheet.protect().setDescription('Test');
      protection.removeEditor("[email protected]");
      protection.removeEditor("[email protected]");
      protection.addEditor("[email protected]");

      var ui = SpreadsheetApp.getUi();
      ui.alert("Permissions Set");
}

Function runs and will do everything EXCEPT remove the user that is the one in there running it. I have tried getting the active user and removing them that way as well as throwing in their exact email (we only have eight employees who might touch this, so I'm fine with adding/removing each line by line if I had to).

Aside from this working we will just have to trust they don't touch anything they aren't supposed to LOL, but we know how that goes sometimes ...

Answer 1

Though it's understandable why the spreadsheet owner could not be blocked out, it would seem reasonable to block the current user. But since a user cannot block themselves out using the GUI, the script cannot either.

However, if you're only worried about accidental edits, you can use:

protection.setWarningOnly(true);

This applies to all users, including the owner. Unfortunately, there is no way to set the warning text to something more helpful and the user can choose to ignore it.

The importrange suggestion is also a good one.

Answer 2

Here's a script that may achieve what you want:

function protectAllSheets(){
    const sheet = SpreadsheetApp.getActiveSpreadsheet().getAchiveSheet();
    let protection = sheet.protect();
    protection.setWarningOnly(true);
}