Exception from sheet protection

Question

I have created a script that is working on automaticly asigning range protection to my sheets and specific users (based on their email adresses) are excluded from protection and can edit anything. This was the easy part. But when i make script that is supposed to edit protected cell it tells user that he is trying to edit a protected cell. I know it's obvious but i need to allow user to edit those specific cell when clicking on script but he cannot change it manualy.

To visualise: Script is typing "123" in cell "A1", but user that is starting a script cannot manualy type anything to cell "A1" Code for simple function:

function ABC() {
var arkusz = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Podsumowanie"); 
arkusz.getRange("A1").setValue("123");
}

Button for invoking function ABC

Error from google-sheets that says i cannot perform script because im trying to edit range that is protected from me

How can i make this exception while invoking script? Thanks

See https://stackoverflow.com/questions/70369266/how-to-run-certain-scripts-under-the-authority-of-a-specific-user/70369812#70369812 — TheMaster, Oct 04 '22 at 19:49

score 0 · Accepted Answer · answered Oct 04 '22 at 23:08

As stated in the other post from the comment posted by TheMaster this would be expected behavior because the script would run as the users and not as the person who created the script.

In this case, your best option is to set an installable onOpen trigger using a checkbox that when activated it would run as one of the persons who have permission to edit the range so that anyone that has access to edit that checkbox can run the script.

Sample script:

function ABC(e) {
  let ss = SpreadsheetApp.getActiveSpreadsheet();
  let r = e.range;
  let val = e.value;
  if(r.getA1Notation() == "B1" && val == "TRUE")
  {
    ss.getRange("A1").setValue("ABC");
  }
}

Trigger:

In this sample, the checkbox is in cell B1. I have configured 2 sets of protected ranges, 1 for cell A1 where I am the only one who can edit it, and another for B2 where I have added a single test user who is able to edit the checkbox.

Exception from sheet protection

1 Answers1

Sample script:

Trigger:

References: