addEditor and removeEditor on certain conditions for google sheet

0
votes

I learned from [this question] (Automatic Lock/Protect Rows/cells in Google Sheets at a specific time in other Cells) on how to lock certain cells with certain conditions and Jess's answer works perfectly fine.

I am just wondering if I could add more codes to unlock the cells at a certain conditions as well. e.g: if users do thing1, lock the cell B1, if users do thing2, unlock the cell B1 for future editing.

Here the code I revised based on Jess's answer, the blocking (removeEditor) works fine, but the "else if" condition I added to unblock the cell did not work, and the cells are NOT unblocked under the condition.

function onEdit(e) { 

 var ss1=SpreadsheetApp.getActiveSpreadsheet();

 var sheet1=ss1.getSheetByName("Sheet Name");  

 var active = sheet1.getActiveCell();

 var nextnum = sheet1.getRange(active.getRow(),8).getValue();

 var shouldbeblocking= sheet1.getRange(active.getRow()-1,7)

 var them = Session.getActiveUser();

 var theiremails = them.getEmail();


// to unlock the cell, NOT WORKING

   if (nextnum == 22) {
 
 shouldbeblocking.protect().addEditor(theiremails);
 
 shouldbeblocking.setNote("unLocked") ;
  }

 // to lock the cell (codes working fine)

  else if (nextnum == 1) {

 shouldbeblocking.protect().removeEditor("[email protected]") ;
 shouldbeblocking.setNote("Locked") ;
  }



}

As per @ross's suggestion, I added Logger.log onto my code (within the if statement), and below is the screenshot of the execution transcript, it seems like the code runs through the if statement, and oddly the "unlocked" setNote works cuz I can see the "unlocked" note on the cell, but that cell is still blocked:

execution log screenshot

1
google-apps-scriptgoogle-sheetstriggers
Nick, are you sure that 1) nextnum ever reaches the 22 value; 2) getActiveUser().getEmail() result is the same as the email in removeEditor()? – Oleg Valter
Hey Oleg, thanks for the reply! [1] yes! on google sheet i set up some if statement to change the value of nextnum to 22. [2] yes! currently i am only sharing my google sheet to one other person's gmail account. and the getActiveUser's email is the same as the [email protected] I used for removeEditor. With all these, do you think my code has logical issues? thanks !! – Nick Liu
Add a Logger.log inside your if statement that you think working to make sure that it is actually getting into that part of the code. Also could you show your execution transcript (go to view > execution transcript) to view this then add the contents to the question. – ross
Nick, I expected at least one of the answers to be "no") Please, do what @ross said to make sure that the script execution actually reaches the the if statement (btw, about [2] - are sure or you called something like theiremails==='[email protected]' and it returned true?) – Oleg Valter
@ross, Hey Ross, thanks for the help! I added the logger.log + execution transcript onto the content, it seems like the code gets to the if statement, and I did notice the "setNote" of "unlocked" appears on the cell, oddly the code runs the setNote part in the if statement, but not on the other addEditor, – Nick Liu

1 Answers

1
votes

So, to sum up - there is an easily overlooked note in the protect() method documentation that "If the range is already protected, this method creates a new protected range that overlaps the existing one", which means that each time your onEdit(e) trigger fires, at least one new protection layer is created instead of being overridden.

You should instead access Protection instances created for the Spreadsheet via getProtections() method and call the addEditor() and removeEditor() methods on an element from the resulting Array (to be able to distinguish between Protection instances, make use of getRange() and getEditors() methods (at least Logger.log() or console.log() them to keep track).

You will probably want to check if the user can edit the protected Range before the add/remove procedure – in this case, use the canEdit() method on an aquired Protection.

Useful links

  1. canEdit(e) reference;
  2. remove() reference;
  3. getProtections() reference (protections must be filtered by type);