Add an Editor for script to run, then Remove them - temporary Editor permission

Question

I shared a spreadsheet with a sheet named "times". This sheet is range protected to other users, but them must view and have to sort it in several ways. I create some menus with menuEntries.push etc... wrote the scripts for sort this sheet in all the ways i need, but only people I set as administrator can sort using my menu. The others can't to do it cause they can't execute the script on range protected. I would like to grant permission to everybody only during the script exectuting, the code should sound something like this above ( that don't works )

function Editors() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName("times");
  var user = SpreadsheetApp.getActive().getEditors();
  var permission = targetSheet.getSheetProtection();
  permission.addUser(user);
  SpreadsheetApp.flush();

  var tableRange = "orario!b7:ap209";   
  var tableRange = "times";
  var range = ss.getRange(tableRange); 
  range.sort( { column: 2, ascending: true } ); 

  permission.removeUser(user)
  targetSheet.setSheetProtection(permission)
}

...if someone can help me .... thanks in advance....

Have you looked in the Execution Transcript to see if the code succeeded, or failed? — Alan Wells, Feb 28 '15 at 21:59
You can try an installable trigger - see https://stackoverflow.com/questions/36871241/how-to-allow-onedit-function-to-affect-protected-cell-in-a-google-sheet — josle, May 13 '18 at 10:00

Alan Wells · Answer 1 · 2015-03-03T01:09:24.820

0

You should publish the Project to execute the script as you. Save a version, then publish.

In the script editor, choose FILE, MANAGE VERSIONS. Save a new version. Then choose, PUBLISH, DEPLOY AS WEB APP. Make the settings. That's the only thing I can think of.

The getSheetProtection() method has been deprecated. Don't use it. Also,

sheet.setSheetProtection(permission);

is also deprecated. Don't use that.

And

var user = ss.getEditors();

Gets an array of users. That's not what you want. You want the current user. For that, you must use the Session class.

var user = Session.getActiveUser().getEmail();

And you need to use addEditor() not addUser()

permission.addEditor(user);

Code

function Editors() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName("Sheet1");

  var user = Session.getActiveUser().getEmail();

  //Cancel all protections
  var protections = targetSheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);

  for (var i = 0; i < protections.length; i++){
    var thisProtection = protections[i];

    if (thisProtection && thisProtection.canEdit()) {
      thisProtection.remove();
    };
  };
  var permission = targetSheet.protect();

  permission.addEditor(user);

  SpreadsheetApp.flush();

  var tableRange = "orario!b7:ap209";   
  var tableRange = "times";

  var range = ss.getRange(tableRange); 
  range.sort( { column: 2, ascending: true } ); 

  permission.removeEditor(user);
}

edited Mar 03 '15 at 01:09

answered Feb 28 '15 at 22:14

Alan Wells

30,746
15
104
152

Hi. Thank a lot for your help. I cut deprecated methods and used your code, but if I run it as "administrator" of the file there is an error in red on the sheet "Per proteggere questo foglio, devi rimuovere eventuali intervalli protetti al suo interno" id est ... " to protect this sheet you have to cancel range protection inside" . If I lunch the function in the script the error is on the line with " var permission = targetSheet.protect(); " – Fabrizio Gereon Mar 02 '15 at 16:43
I added some code to cancel all the protections. See what happens with that code. – Alan Wells Mar 02 '15 at 17:30
I add the cicle FOR and now the sheet's red alert (as admin ) report is something like: " to protect this sheet you have to remove protected range inside it " if I lunch the script ad USER it still says : " you are try to modify a protct cell or range "..contact the ADMIN – Fabrizio Gereon Mar 02 '15 at 19:22
I try several variant of the code ( also this one http://stackoverflow.com/questions/28778327/google-spreadsheet-error-when-remove-range-protection I create a sheet and put you as admin hoping you could help me..... – Fabrizio Gereon Mar 03 '15 at 00:17
Got your invite, and looked at your sheet. Try publishing the Project to execute the App as "ME". (Which is YOU). – Alan Wells Mar 03 '15 at 01:06
well,I published the project. But sorry..... where I have to put that URL ??? This is the URL https://script.google.com/macros/s/AKfycbyHkEpEsI1GHvg7F8718uE8ZTJ4APPuTKKY_pT_gwPzzpJQRz4/exec and where I have to put it to make it works ? – Fabrizio Gereon Mar 03 '15 at 07:47
You won't put the url anywhere. I just wanted to see it that would make any difference. I'm not sure what is going wrong, and trying to test different things. – Alan Wells Mar 03 '15 at 14:18

score 0 · Answer 2 · answered Mar 03 '15 at 00:15

I create a simply sheet to test the script. Here it is: https://docs.google.com/spreadsheets/d/1DhDo_1A20tWdSaYMknAmuxnVJFW14QY6xBXr9BGyS-g/edit#gid=0

If I use the "menuTeacher" 2. and 3. as admin to sort the sheet it works correctly. but as user it doesn't work

( use only the menu "1.sortSURNAME" to test the permissions ) I protect columnA:B and columnE:M

I put Mr Sandy Good as admin like me ( with his mail xxxtrashmatXXX@gmail.com ) to make tests....

Fabrizio Gereon · Answer 3 · 2015-03-03T13:03:53.290

Well, I poste the complete code and make a summary of the problem:

function onOpen() {
var ss = SpreadsheetApp.getActive();
// also tried with var ss = SpreadsheetApp.getActiveSpreadsheet();
menuEntries.push({name: "1. sortSURNAME", functionName: "Cognome"});
ss.addMenu("menuTeacher", menuEntries); 
}

function Cognome(){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var targetSheet = ss.getSheetByName("orario");

var user = Session.getActiveUser().getEmail();
var protections = targetSheet.getProtections(SpreadsheetApp.ProtectionType.SHEET);
var permission = targetSheet.protect();
permission.addEditor(user);
SpreadsheetApp.flush();


for (var i = 0; i < protections.length; i++)
{
var thisProtection = protections[i];
if (thisProtection && thisProtection.canEdit()) {
thisProtection.remove();
}
}

var tableRange = "orario!b7:ap209";   
var range = ss.getRange(tableRange); 
range.sort( { column: 2, ascending: true } ); 

permission.removeEditor(user);
}

executing this code a red alert advise that: " TO PROTECT THIS SHEET, YOU HAVE TO REMOVE PROTECTED RANGE" and the line marked in the script is this one:

var ss = SpreadsheetApp.getActiveSpreadsheet();

So, it seems impossible to remove a protected range with a script if you are an user and not the admin. Mr Sandy Good suggest to create a web app to execute the script as ADMIN even if you are an USER I generate it but I don't know how to use it.... Here is the URL of the web app I generate

https://docs.google.com/spreadsheets/d/1DhDo_1A20tWdSaYMknAmuxnVJFW14QY6xBXr9BGyS-g/edit#gid=0

Add an Editor for script to run, then Remove them - temporary Editor permission

3 Answers3

And

Code