Google Sheets - Allow Users to run scripts on a protected range

Question

I'm new to Apps Script and have been slowing teaching myself. I have searched Stack Over Flow and found similar questions and tried to work through the issue using the information found but thus far with no success. Hopefully someone here can give a newbie some guidance.

I have a spreadsheet that I am sharing with other users. There are a few ranges with formulas in them that I am trying to protect. I have written a script that can change the forumlas within the protected ranges. It runs perfectly under my account but the shared users are unable to run the script because they "don't have permission".

I have tried to write a bit of code that tries to grant temporary access to edit the protected ranges. My thought was to have three different functions. The first function removes the protections. The second function performs the changes to the now unprotected ranges. The third function then protects the ranges again. The code below works perfectly under my account. However when logged in as a shared user it causes an alert saying that "You do not have permission to perform.."

Can someone please tell me where I am going wrong and point me in the right direction?

function myFunction() {
  //add protections back
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Spray Template');
  var range = sheet.getRange('B15:E15');
  var range2 = sheet.getRange('G15:H15');
  var range3 = sheet.getRange('D16:E16');
  var ranges = [range,range2,range3];
  for (var j = 0; j<3; j++){
    ranges[j].protect().removeEditor('[email protected]');
  }
}


function myFunction2(){
//remove protections
var ss = SpreadsheetApp.getActive();
var ss2= ss.getSheetByName('Spray Template');  
var protections = ss2.getProtections(SpreadsheetApp.ProtectionType.RANGE);
 f
 or (var i = 0; i < protections.length; i++) {
   var protection = protections[i];

protection.addEditor('[email protected]');
SpreadsheetApp.flush();
protection.remove();   
 }
}

function test() {
  myFunction2();
  functionThatChangesRange();
  myFunction();
}

Lloyd Lloyd · Accepted Answer · 2017-03-05T05:08:44

I imagine that without permission to edit/access those protected ranges, other people will also lack permission to edit the protection itself (even through scripts).

This is because the script inherits its permissions from the user that is running it (ie the script runs under that person's account).

Obviously, you can't both protect a range, and also allow anyone to change the protection - that would defeat the whole purpose!

Google Sheets - Allow Users to run scripts on a protected range

3 Answers