1

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("example1@domain.com");
      protection.removeEditor("example2@domain.com");
      protection.addEditor("example3@domain.com");

      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 ...

  • 3
    Have you tried reading the docs? "Neither the owner of the spreadsheet nor the current user can be removed." – user79865 Jul 19 '16 at 14:49
  • @Bookend I guess I never made it that far down, just examples at the top and then other people's tests from this site and other forums! So there is NO way for someone to copy a sheet and not have permissions to access certain sheets/cells? – Chris Weems Jul 19 '16 at 18:39
  • 2
    Apparently not. If a user owns a spreadsheet, they really own it. Consider importrange for giving access to certain parts of a sheet. – user79865 Jul 19 '16 at 19:58
  • idea: add the creator's name to a list in another file that you control, maybe by form submission, wait for the user to close the sheet and then remove his access. I will see if I can come up with a proof of concept when I have access to my PC. – HardScale Aug 2 '16 at 15:19
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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