Google-sheets – Script to add protection and remove the user running it

google sheetsgoogle-appsgoogle-apps-script

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 …

Best Answer

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.

Related Solutions

Google-sheets – Can this script be more optimized and why is it running slow sometimes

The first thing to do is to minimize the number of API calls interacting with the sheet, such as getValue() and setValue(). Think of the script as a factory and the sheet as its warehouse across the town. If a worker has to drive to warehouse every time they need a detail, they won't get much done. Instead, they should get all the materials they need at the beginning, and turn in all finished product at the end.

So, instead of the multiple .getRange(row, column).getValue() and .getRange(row, column).setValue(), there should be

one call to get values: e.g., sheet.getDataRange().getValues() which gets you all the data in the sheet. (Or, if you know a specific range you'll be working in, get all values from there). Then loop over this double array, analyzing its contents
one call to set new values with .setValues(), from a double array of the same size (like [['This year', 2015], ['Next year', 2016]]
one call to set backgrounds with .setBackgrounds(), also from a double array.

In between 1 and 2, you work purely in JavaScript, processing one array and forming two others.

Sometimes you can't have just one array in 2 or 3, because, e.g., setValues would overwrite some formulas in the middle of your data. In this case, split the data range into groups of columns that can be handled at once. You will still have only a few setValues calls.

Google Sheets – Remove Protection from Range

Try this code:

function start() {
  var idFolder = '0B79ClRnKS87QcW9XbVhrcFZxd28'; // change
  var sheetNames = ['1', '2', '3']; // change
  var strRange = 'A1'; // change

  deleteProtection(idFolder, sheetNames, strRange) 

}


function deleteProtection(idFolder, sheetNames, strRange) {

  var folder = DriveApp.getFolderById(idFolder);
  var contents = folder.getFiles();
  var file;
  var sheet;
  var sheets;
  var sheetName;
  var range;
  var strRangeProtect;
  var protections;
  var protection;

  var app = SpreadsheetApp;

  while(contents.hasNext()) {
    file = app.openById(contents.next().getId());
    sheets = file.getSheets();

    for(var i = 0; i < sheets.length; i++) {
      sheet = sheets[i];
      sheetName = sheet.getName();
      protections = file.getProtections(app.ProtectionType.RANGE);

      for (var i = 0; i < protections.length; i++) {
        var protection = protections[i];
        var range = protection.getRange();
        var sheetName = range.getSheet().getName();
        strRangeProtect = range.getA1Notation();
        if (strRangeProtect == strRange && sheetNames.indexOf(sheetName) > 0) {
          protection.remove();
        }      
      }           
    }  
  }  
}

Befor you start:

First you need to paste all the files into one folder, then copy it's id:

and change this lines:

  var idFolder = '0B79ClRnKS87QcW9XbVhrcFZxd28'; // change
  var sheetNames = ['1', '2', '3']; // change
  var strRange = 'A1'; // change

Best Answer

Related Solutions

Google-sheets – Can this script be more optimized and why is it running slow sometimes

Google Sheets – Remove Protection from Range

Related Topic