google apps script - Sheet embedded function -only works for spreadsheet owner, not "anyone"

Question

Long time learner/lurker, first time poster:

Howdy folks-

I've deployed a web-app that runs as the user (not me). It creates a form and a linked-spreadsheet in the users drive with "anyone" access and "edit" permissions.
Bound to this spreadsheet is an installable "onOpen" trigger that creates a UI menu item to the right of the spreadsheet's "help" menu, and on that menu item is a function that manipulates the data in the spreadsheet. That all works properly -for the user who created the form+spreadsheet.

Here's my problem: The use-case for my web-app is to allow someone to create the form and spreadsheet then hand it off to someone to use the form and view responses and tally votes via the spreadsheet.

Because the spreadsheet's access is "anyone", the person to whom the spreadsheet is handed-off can EDIT the spreadsheet, but the "onOpen" trigger never fires unless and until the opening-user signs in.

Then, the next problem: Assuming the handed-off person signs in and sees the new UI menu item, they cannot successfully RUN the function on the menu. It appears only the user that originally created the form+spreadsheet is the one who can run the funtion from the UI (addMenu) menu.

Questions:

• what's the best/recommended way to detect a user is not signed-in ..so I can present a modal alert when the spreadsheet is first opened? The goal is to alert the user that if they signed-in, they would see the menu item by activating the installable trigger.

• Can a modal alert be presented when a user is not signed-in? I'm not sure how that code would be triggered too, as it's likely the same problem as not triggering the onOpen UI-addMenu code

• what's the best/recommended way to set the permissions for the spreadsheet's (embedded) menu-function -so the user opening the spreadsheet can run it vs. only the original creator of the spreadsheet.

web-app example: https://forms.fattm.org/contest-form-creator (several forms can be created, but the problem described above relates only to the form named: CONTEST BALLOT (Item 1179) ..so you can un-check the others before hitting submit)

Thanks for your time/thoughts/help!

question from:https://stackoverflow.com/questions/65906291/sheet-embedded-function-only-works-for-spreadsheet-owner-not-anyone

Answer 1

Welcome to SO!. The script you have that creates a custom menu using onOpen is a container-bound one, bound to the spreadsheet you are sharing. However the onOpen function is a trigger function which must be installed first (for each user that wants to use it) There is no way to get around this, unless you publish an add on, in which case the users would still have to install the add on the sheet you are providing.