为什么为与Google Sheets相关的Google Apps Script项目配置授权这么困难？

人工智能 2026-07-11

The project comprises 8 Google spreadsheets and one library; it implements logic to support a business operating in a remote location with a requirement to track the earnings and expenses of its collaborators. The spreadsheets’ and library’s logic rely solely on standard Google Sheets and Drive logic.

该项目包含8 个Google表格和一个库；它实现了一套逻辑，支持在偏远地区运营的业务，并需要跟踪合作者的收入与支出。电子表格和库的逻辑仅依赖标准的Google表格和Google Drive逻辑。

My goal is to set it up to minimize, if not eliminate, Google authorization requests issued to users for each script in the application.

我的目标是把它搭建成尽量减少，甚至消除，对应用中每个脚本向用户发出的Google授权请求。

I’ve read the Google OAuth for its workspaces and cloud projects documentation; it is too deep, and I could not form a coherent mental model of how it works and how to implement it for a simple application like mine. I’ve consulted a plethora of AI engines, each suggesting slightly different approaches, all leading to Google OAuth dead-ends, e.g., despite being the owner of the workspace and project and having the required credentials, I cannot create a secret key required to configure the application.

我已阅读Google针对工作区和云项目的OAuth文档；内容太深，我无法形成一个连贯的认知模型，来理解它的工作原理以及如何将其应用到像我这样简单的应用中。我咨询了大量AI引擎，它们各自给出略有不同的方法，但都走向Google OAuth的死胡同，例如，尽管我是工作区和项目的拥有者并具备所需凭据，我仍然无法创建配置应用所需的密钥。

How to make it work?

如何让它真正运作？

解决方案

这是与Gemini AI引擎的一段冗长对话的简化版本，在ChatGPT和 Manus之外，它给出了最简单、最有前景的路径。

这之所以感觉如此困难，是因为Google将同一套安全基础设施用于“简单表格”和大规模的第三方集成。

在标准的GAS环境中，脚本有两种运行方式：

以用户身份运行： 脚本以坐在键盘前的用户身份执行。如果他们无法访问这8 张表格中的其中一个，脚本就会失败。这会为每一个独立的脚本项目向每个用户触发一次授权提示。
以你（软件工程师）身份运行： 脚本以你本人的权限来执行。这是消除其他用户授权提示的“神奇”做法，通常通过将逻辑发布为一个 Web App 来实现。

“Web App”充当桥梁，使用户能够在没有访问权限的表格中与数据交互，而无需被提示授权脚本。

Deploy as: Web App.
Execute as: Me (Your account).
Who has access: Anyone within your Workspace (or "Anyone").

我将把我的8 个工作表生态系统重构为一个 Library-Powered Hub 模型。

Library: Contains the "Brain" (The Pure Business Logic);
Hub (WebApp): Contains the "Hands" (The Google Services);
Spreadsheets: Contain the "Eyes" (The UI/Buttons)。

The Hub (WebApp) will the responsible for authenticating requests, retrieving and setting data on the spreadsheets, and interacting with the Library to perform said requests.

Hub（WebApp）将负责对请求进行身份验证、在电子表格中检索和设置数据，并与Library交互以执行上述请求。

Key Software Elements

The Manifest

I'll keep appsscript.json as tight as possible, focusing on spreadsheets access only, read-only access to local files, etc.

我会尽量把 appsscript.json 收紧到最小，主要聚焦于对电子表格的访问，以及对本地文件的只读访问等。

  "oauthScopes": [

    "https://www.googleapis.com/auth/spreadsheets",

    "https://www.googleapis.com/auth/drive.readonly",

    "https://www.googleapis.com/auth/script.external_request"

  ]

Calling the WebApp

function sendToMaster(actionName, payloadData) {

  // Determine environment dynamically

  const config = getEnvironmentConfig();

  const targetUrl = config.activeUrl;

  const requestPayload = {

    action: actionName,

    payload: payloadData,

    env: config.environment // Useful to tell the Hub which DB/Sheet to use

  };

  const options = {

    method: "post",

    contentType: "application/json",

    payload: JSON.stringify(requestPayload),

    headers: { "Authorization": "Bearer " + ScriptApp.getOAuthToken() },

    muteHttpExceptions: true

  };

  const response = UrlFetchApp.fetch(targetUrl, options);

  return JSON.parse(response.getContentText()).data;

}

Sheet Registry

// Global Configuration Object in the Hub Script

const SHEET_REGISTRY = {

  "PROD": {

    "EXPENSES_ID": "REAL_EXPENSE_SHEET_ID",

    "EARNINGS_ID": "REAL_EARNINGS_SHEET_ID"

  },

  "DEV": {

    "EXPENSES_ID": "SANDBOX_EXPENSE_SHEET_ID",

    "EARNINGS_ID": "SANDBOX_EARNINGS_SHEET_ID"

  }

};

Handling the Requests

function doPost(e) {

  try {

    const request = JSON.parse(e.postData.contents);

    // Default to PROD if the caller didn't specify an environment

    const env = (request.env || "prod").toUpperCase(); 

    const action = request.action;

    const data = request.payload;

    // Get the correct set of Sheet IDs based on the environment

    const activeRegistry = SHEET_REGISTRY[env] || SHEET_REGISTRY["PROD"];

    let result;

    switch (action) {

      case "RECORD_EXPENSE":

        // Pass the specific ID from the registry to the function

        result = handleExpenseEntry(data, activeRegistry.EXPENSES_ID);

        break;

      case "GET_RATE":

        result = handleRateLookup(data, activeRegistry.EARNINGS_ID);

        break;

      default:

        throw new Error("Action not found");

    }

    return ContentService.createTextOutput(JSON.stringify({status: "success", data: result}))

      .setMimeType(ContentService.MimeType.JSON);

  } catch (f) {

    return ContentService.createTextOutput(JSON.stringify({status: "error", message: f.toString()}))

      .setMimeType(ContentService.MimeType.JSON);

  }

}

Lib/Hub Division of Labor

I'll call the Library to do the heavy thinking inside the Hub (Web App), leaving the Hub to use its "Master" permissions to collect data and write the result.

我将调用 Library 在 Hub (Web App) 内完成深度思考，让 Hub 使用其“Master”权限来收集数据并写出结果。

// Inside the HUB (Web App)
function handleEarningsCalculation(data) {
  // 1. The Hub gets the raw data from a sheet

  const rawRate = SpreadsheetApp.openById("SHEET_B_ID").getRange("A1").getValue();

  // 2. The Hub asks the LIBRARY to do the complex math

  // (No Google Services used in this Library function)

  const finalEarnings = BusinessLibrary.calculateComplexEarnings(rawRate, data.hours);

  // 3. The Hub writes the result back

  SpreadsheetApp.openById("SHEET_A_ID").getRange("B2").setValue(finalEarnings);

  return "Processed successfully";

}

Google Sheets