How can I avoid being prompted to authorize macro-enabled spreadsheets from trusted users?

Question

When I try to open a new macro-enabled spreadsheet, Sheets prompts me to provide authorization even though:

1. The owner of the document and I are part of the same Google Workspace organization.
2. I have already approved the same permissions for previous spreadsheets from that user.
3. The documents are located in a Google Workspace Shared drive (formerly Team drive).
4. All users have been given Content Manager permissions

Is there any way to stop Sheets from repeatedly requesting the same permissions for every new document?

For example, can I identify a particular Workspace user to give blanket permission to so their documents are automatically authorized?

The files that I'm using right now is shared through Team Drive, where all of the participants of the Team Drive have also been added as "Content Manager".

Answer 1

No. There is no way to disable it for new documents.

Macros provide users with no programming experience an easy way to extend Sheets functionality but the authorization dialog they trigger is necessary because of the necessary permissions.

Even macros that have already been authorized in a document can trigger the prompt if the underlying macro is changed.

An alternative may be to replace the macro with an add-on which would require authorization only once no matter how many spreadsheets use it.

Add-ons are more complicated to create and manage. They can't contain macros and the learning curve to recreate the same functionality would likely dwarf any inconvenience posed by the authorizing prompts when opening new documents.

Additional Resources:

• Authorization for Google Services > Permissions and types of scripts
• Google Sheets Macros
• Extending Google Sheets with add-ons