如何统一不同Spreadsheet调用Google Cloud Endpoint的Client ID？

Got it, let's break down how to get all your Google Spreadsheets using the same Client ID when making requests to your Cloud Endpoints backend—perfect for your setup where a central App Script library handles business logic, and sheet-bound scripts just pass data along.

Core Idea #

By default, each Spreadsheet's bound script uses its own auto-generated OAuth Client ID. To unify this, we'll create a single, shared OAuth 2.0 Client ID in your Google Cloud project, then configure your central App Script library and all bound scripts to use this client for authentication.

Step 1: Create a Shared OAuth 2.0 Client ID #

First, set up the unified client in your Cloud Console (same project as your App Engine/Endpoints):

• Go to APIs & Services > Credentials
• Click Create Credentials > OAuth client ID
• Select Desktop app (or "Other"—this type works well for App Script use cases)
• Name it something descriptive (e.g., "Unified Spreadsheet Endpoints Client")
• Save the generated Client ID and Client Secret—you'll need these later

Step 2: Configure OAuth Scopes in Scripts #

Make sure both your central App Script library and all sheet-bound scripts declare the required OAuth scopes. You'll need:

• Your Cloud Endpoints API's custom scope (e.g., https://www.googleapis.com/auth/your-api-scope)
• https://www.googleapis.com/auth/script.external_request (for UrlFetchApp)
• https://www.googleapis.com/auth/spreadsheets (if accessing sheet data)

Edit the appsscript.json manifest file (enable it via View > Show manifest file):

{
  "oauthScopes": [
    "https://www.googleapis.com/auth/script.external_request",
    "https://www.googleapis.com/auth/spreadsheets",
    "https://www.googleapis.com/auth/your-custom-endpoint-scope"
  ]
}

Step 3: Update the Central Library's Authentication Logic #

Modify your library to use the shared Client ID instead of default authentication. We'll use Google's official OAuth2 library to simplify token management (it handles refresh tokens automatically):

1. In your library's script editor, go to Resources > Libraries
2. Add the OAuth2 library with ID: 1B7FSrk5Zi6L1rSxxTDgDEUsPzlukDsi4KGuTMorsTQHhGBzBkMun4iDF
3. Use this code to handle authentication and send requests:

// Initialize the shared OAuth service (run this once to set up)
function initUnifiedOAuth() {
  const oauthService = OAuth2.createService("UnifiedEndpointsClient")
    .setClientId("YOUR_SHARED_CLIENT_ID")
    .setClientSecret("YOUR_SHARED_CLIENT_SECRET")
    .setAuthorizationBaseUrl("https://accounts.google.com/o/oauth2/auth")
    .setTokenUrl("https://oauth2.googleapis.com/token")
    .setScope([
      "https://www.googleapis.com/auth/your-custom-endpoint-scope",
      "https://www.googleapis.com/auth/script.external_request"
    ])
    .setCallbackFunction("authCallback")
    .setPropertyStore(PropertiesService.getScriptProperties());

  if (!oauthService.hasAccess()) {
    const authUrl = oauthService.getAuthorizationUrl();
    Logger.log("Authorize once via this URL: " + authUrl);
  } else {
    Logger.log("OAuth service initialized successfully");
  }
}

// OAuth callback handler
function authCallback(request) {
  const oauthService = OAuth2.createService("UnifiedEndpointsClient");
  const isAuthorized = oauthService.handleCallback(request);
  return HtmlService.createHtmlOutput(isAuthorized ? "Authorization successful!" : "Authorization failed.");
}

// Core function to send data to Endpoints
function sendSheetDataToEndpoints(sheetData) {
  const oauthService = OAuth2.createService("UnifiedEndpointsClient");
  if (!oauthService.hasAccess()) throw new Error("OAuth service not authorized");

  const endpointUrl = "https://YOUR_PROJECT_ID.appspot.com/_ah/api/YOUR_API/v1/your-endpoint";
  const requestOptions = {
    method: "POST",
    contentType: "application/json",
    headers: {
      "Authorization": `Bearer ${oauthService.getAccessToken()}`
    },
    payload: JSON.stringify(sheetData)
  };

  const response = UrlFetchApp.fetch(endpointUrl, requestOptions);
  return JSON.parse(response.getContentText());
}

Step 4: Set Up All Sheet-Bound Scripts #

Each Spreadsheet's bound script only needs to call the library's sendSheetDataToEndpoints function—no auth logic needed here:

// Replace with your library's project ID
const DataLib = SpreadsheetApp.getActiveSpreadsheet().getScriptLibrary("YOUR_LIBRARY_PROJECT_ID");

function processAndSendData() {
  const activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const sheetData = activeSheet.getDataRange().getValues();
  
  try {
    const result = DataLib.sendSheetDataToEndpoints(sheetData);
    Logger.log("Data processed successfully: " + JSON.stringify(result));
  } catch (err) {
    Logger.log("Error: " + err.message);
  }
}

Step 5: Configure Cloud Endpoints to Accept the Shared Client ID #

Update your Endpoints API config (e.g., openapi.yaml) to allow requests from your unified Client ID:

securityDefinitions:
  google_id_token:
    type: oauth2
    authorizationUrl: ""
    flow: implicit
    x-google-issuer: "https://accounts.google.com"
    x-google-jwks_uri: "https://www.googleapis.com/oauth2/v3/certs"
    x-google-audiences: "YOUR_SHARED_CLIENT_ID"

Key Notes #

• One-Time Authorization: Run initUnifiedOAuth in your library once with an account that has Cloud project permissions—this stores the refresh token in the library's script properties, so all bound scripts can reuse it.
• Security: Keep your Client Secret private—only library admins should have access to edit the library code.
• Token Refresh: The OAuth2 library automatically refreshes access tokens when they expire, so you don't have to handle that manually.

内容的提问来源于stack exchange，提问作者Deviling Master