0

I have script to protect cell after data in any cell in range("A:A"), Sheet1 is updated

  1. Firstly, it worked perfectly, but now it does not protect Editor from editing that cell

  2. Now, after data in cell is updated, the script will run to protect cell but it does not remove editor to "View Only". It set permission to allow Editor and Owner to edit cell. The first time, it set permission to only Owner and other Editors are "View Only"

  3. Trigger is the same. It runs when "On Edit"

  4. Problem : Script does not remove editor to "View Only" in protection, it still keeps editor in permission list. Pls kindly help me accordingly.

function protectcell(e) {
  var range = e.range;
  if (range.getSheet().getName() != "Sheet1") return;
  if (range.getWidth() != 1 || range.getHeight() != 1) return;
  if (range.getColumn() != 1 || range.getRow() < 2) return;
  if (!e.value) return;

  var timeZone = Session.getScriptTimeZone();
  var stringDate = Utilities.formatDate(new Date(), timeZone, 'dd/MM/yy HH:mm');
  var description = 'Protected on ' + stringDate;
  var protection = range.protect().setDescription(description);
  protection.removeEditors(protection.getEditors());
  if (protection.canDomainEdit()) protection.setDomainEdit(false);
}
v1787v
  • 45
  • 1
  • 5
  • I tested your script and it works for me as intended (the protection disables all editors apart from the script owner fro editing the `e.range` (but not the other cells in the spreadsheet). If for your it does not work in the same way, either there is some conflict between script owner / trigger owner / file owner, or it might be related to [this](https://stackoverflow.com/questions/60092877/sheet-getprotections-no-longer-works/60094123#60094123). – ziganotschka Feb 12 '20 at 10:51
  • So there is no solution, right sir? – v1787v Feb 13 '20 at 01:53
  • Can you try this script with another sheet and see if it works? Since for me, on my sheet, it did. Double-check that your sharing settings are as desired and the script and trigger is owned by the person who is not supposed to be removed from the editor list. – ziganotschka Feb 13 '20 at 08:35
  • I've created new spreadsheet and test this script but problem is still the same sir. - Use new owner - User new user - Owner installed Trigger – v1787v Feb 13 '20 at 09:36
  • What your code does is: When the owner edits a cell, then this cells becomes protected and the other user cannot edit it anymore. However, the other user can still edit other cells and still has edit permissions to the spreadsheet as a whole. Is it not working like this for you? did you run the script once manually after creation? Have you checked the execution logs to see if the trigger is running at all? – ziganotschka Feb 13 '20 at 09:47
  • Sir.. Sorry for late response. After any cell in range(A:A) is updated, user can still edit it. this is my problem. Last time, user can not edit data in any cells in Range(A:A) even if it is updated by user – v1787v Feb 17 '20 at 04:34
  • How / by whom are the cells being updated? – ziganotschka Feb 17 '20 at 08:48
  • Does this answer your question? [sheet.getProtections no longer works?](https://stackoverflow.com/questions/60092877/sheet-getprotections-no-longer-works) – hftf Feb 18 '20 at 14:50
  • Now it works as normal. I don't know the root cause too. thanks all Masters – v1787v Feb 21 '20 at 07:18

0 Answers0