I have a shared Google Sheet which is an Installation Schedule for a small company. Each sheet is a week. I've created script actions that do things like look for data changes, and if appropriate, hide and unhide rows. This works for everyone that the spreadsheet is shared with, with no prompting for permission.
The function to create a new weeks is executing by clicking on a entry in a Custom Menu. It then prompts the user for the date of the first week and the number of weeks to create. After some user entry error checking, it copies a hidden template sheet to a sheet named after the week's date range, updates a date entry cell, copies sheet protections and then unhides the newly copied sheet. This works for me as the sheet owner, however, it is creating authentication dialogs for any other user to which the sheet has been shared.
I could get around this by deploying this as a test app in the Google Cloud Platform interfaces, but then I would have to specifically share it with the users, which a) are not part of a Google Workspace and b) are likely to change, therefore requiring on-going maintenance. Given that the script is modifying "my" sheets file, I don't understand why permission would be needed and am hopeful there is a way around this.
I have searched for and tried many possibilities, but none seem to solve the problem.
Anyone have any expertise? I am a competent coder, but relatively new to Google Apps, so please be as detailed as possible.
CodePudding user response：
Functions called from Google Workspace editor custom menus executes under the active user. If the function use any method that requires authorization to run it will show the OAuth consent screen for your script.
Unfortunately there is no way to avoid this. Even making calls to APIs through UrlFechApp by passing the appropriate credentials, in this case to call the Google Sheets API, it will be required authorization of the active user for https://www.googleapis.com/auth/script.external_request.
The option to use the custom menu to open a dialog or sidebar to run client side code will also require the user to authorize the script.
Considering the above is you really want to avoid to ask the user to authorize anything, forget about using custom menus. You might consider to use installable triggers, i.e. use edit / change installable triggers to call functions when edit is made like clicking a checkbox or selective a value from a data validation dropdown.
Related