1

Trying to run a script triggered by an image inserted in a protected sheet. The sheet is shared by several users. oOly the author is enabled to edit; all other users are locked out via setDomainEdit(false), except for range ('a1:b3').

when a user clicks the image a script is called to execute (simple msgbox function --> Browser.msgBox("Clicked");),

Google sheets is returning an error message:

Exception you are trying to edit a protected cell or object. Contact the spreadsheet owner to remove the protection if you need to edit.

the image is a .jpg inserted above cells in the unprotected range ('a1:b3').

QUESTION:
is there any way to unprotect/ unlock an image (within a protected sheet) to enable a script to run when clicked by all users?

(if necessary, the image can be replaced by a google 'drawing' or any other clickable object that would run the required script/ whose properties can be modified to enable the script to run -- but the sheet must remain locked).

aynber
  • 22,380
  • 8
  • 50
  • 63
lockedOut
  • 11
  • 1

2 Answers2

1

There isn't.

The above because Google Sheets protection can be set for sheets and ranges but either of this options includes a way to keep a drawing "clickable" while the sheet / range over it's placed is protected.

Consider to use a custom menu. You could use it to show a dialog / sidebar.

Rubén
  • 34,714
  • 9
  • 70
  • 166
  • hi Ruben -- thanks for the prompt response. I tried adding a custom Ui menu, which only appears for a specific user in a specific sheet - - however, the issue of running a script called from the Ui menu again clashed with the sheet permission -- which has the sheet locked down except for select cells. To circumvent this, I tried to add (temporary) addEditor rights for the individual -- but that does not seem to work either. Follow up question: is there a way to programmatially enable a user to temporarily relax the sheet permission so that they can run a script called through the Ui menu? – lockedOut Aug 19 '20 at 01:13
  • Only the spreadsheet Owner can "relax the sheet permission". This could be done by using a Google Apps Script web app "executed as me" (the spreadsheet owner) to do something like "relaxing the sheet permissions". In order to choose the "right solution" it's required to understand several things of your "solution" – Rubén Aug 19 '20 at 02:25
0

for the benefit of anyone with the same issue/ question (originally posted), Google confirmed that the feature of executing a script by clicking a shape/ image in a locked sheet is available by following the following steps:

(1) Create a Spreadsheet as User A;
(2) Add an image over range (for example A1:A4)
(3) Assign a script to the image  
(4) Run script on Sheet1 to protect it, except for range A1:A4;
(5) Share the Spreadsheet with another user (User B) with Editor access;
(6) User B should  be able to click the image/ run the script

For the rest of the functionality mentioned, a WebApp (executed as me) and installable trigger did the trick.

lockedOut
  • 11
  • 1