如何关闭Google Sheets脚本的「需要授权」提示，供团队免授权使用？

Hey there! I get how frustrating it is to have your team re-authorize the script every time they create a new order—especially coming from Excel VBA where this kind of thing just works out of the box. Let's walk through the simplest, non-dev-heavy fixes that should solve this for you.

Why Your Current Fixes Aren't Working #

First, quick context: When you copy your template Sheet, the attached Apps Script gets copied too as a brand new project for each order. Google requires each user to authorize their own copy of the script because it's modifying their personal Google Drive content. The things you tried (disabling security prompts, GCP settings, making code public) don't bypass this because Google's security model prioritizes user data safety—you can't just turn off authorization for scripts that edit Sheets.

Solution 1: Move Your Script to a Shared Library (Free, Best for Small Teams) #

This lets all your order Sheets use a single, pre-authorized script instead of copying the script every time. Here's how to do it step-by-step:

1. Create a standalone script project

   • Open the Google Apps Script homepage directly, click "New project".
   • Delete the default myFunction() code, then paste all the code from your template's attached script into this new project.
   • Save the project with a clear name like "Order Template Script Library".

2. Deploy the project as a library

   • Click the "Deploy" button at the top right, then select "Deploy as library".
   • In the deployment settings:
     • Set "Version" to "New" and add a description (like "v1 - Order status handler").
     • Set "Who has access" to "Anyone with the link" (or "Everyone at your domain" if you use Google Workspace).
   • Click "Deploy", then copy the "Library ID" that pops up (you'll need this later).

3. Update your template Sheet to use the library

   • Open your original order template Sheet, go to Extensions > Apps Script to open its editor.
   • Delete all the existing code in this editor.
   • Click the "+" next to "Libraries" in the left sidebar, paste the Library ID you copied, then click "Look up".
   • Set the "Version" to the one you just deployed, and give it a short alias (like OrderLib).
   • Now replace your old button-triggered functions with calls to the library. For example, if your original button ran handleStatusChange(), write:

     function handleStatusChange() {
       OrderLib.handleStatusChange();
     }
     

   • Save this script, then test the button once with your admin account—you'll need to authorize this library call one last time.

4. Share the library with your team

   • Go back to your standalone library project, click "Share" at the top right, add your team members' email addresses, and give them "Viewer" access.

From now on, when your team copies the template to create a new order, the Sheet will call the pre-authorized library script instead of a new copied script. No more repeated authorization prompts!

Solution 2: Upgrade to Google Workspace (Paid, Most Convenient) #

If your team can budget for it, Google Workspace Business or Enterprise lets you set up domain-wide deployment for scripts. As the admin, you can authorize the script once for everyone in your company, and your team will never see an authorization prompt again.

This is the most hands-off option long-term—you just deploy the script once in your Workspace admin console, and all order Sheets using the script will work without extra steps for your team.

Quick Tips to Avoid Headaches Later #

• Test with one team member first: Before rolling out to everyone, have one person copy the template and test the button to make sure no authorization prompt appears.
• Keep the library updated: If you need to change the script later, update the standalone library project, deploy a new version, and update the version number in your template's library settings.

内容的提问来源于stack exchange，提问作者Techsol GmbH