Running Script through image in Locked gSheet

264 Views Asked by At

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).

2

There are 2 best solutions below

2
Wicket On

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.

0
lockedOut On

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.