如何跨不同Google表格持久化Google表格插件的用户API密钥

Great question—this is a super common challenge when building Google Sheets add-ons that depend on user-specific API credentials. Let’s walk through the best storage options, ranked by security, usability, and alignment with your workflow:

#1 Google Workspace Properties Service (User Properties) #

This is the official, recommended solution from Google, and it’s perfect for your use case. The Properties Service has three tiers, but the one you care about is User Properties:

• It stores data tied to the user’s Google account, not a specific spreadsheet. That means once a user enters their API key once, it’s available across all their Google Sheets (new or existing) when they use your add-on.
• Data is encrypted at rest and only accessible to the user who stored it (and your add-on, when running as that user).

How it fits your workflow: #

1. User installs your add-on in any spreadsheet → your add-on checks if a key exists in User Properties.
2. If no key is found, trigger a friendly prompt asking them to input their API key.
3. Validate the key (e.g., make a quick test call to the API to ensure it’s valid) to avoid storing bad credentials.
4. Store the valid key with PropertiesService.getUserProperties().setProperty('API_KEY', userInput).
5. When the user installs your add-on in a new spreadsheet, your add-on automatically pulls the existing key from User Properties—no need for them to re-enter it.

Example code snippet: #

function getValidApiKey() {
  const userProps = PropertiesService.getUserProperties();
  let apiKey = userProps.getProperty('API_KEY');

  // If no key exists, prompt the user
  if (!apiKey) {
    const ui = SpreadsheetApp.getUi();
    const promptResponse = ui.prompt(
      'API Key Required',
      'Please enter your API key to use this add-on:',
      ui.ButtonSet.OK_CANCEL
    );

    if (promptResponse.getSelectedButton() === ui.Button.OK) {
      apiKey = promptResponse.getResponseText().trim();
      // Validate the key before storing
      if (isValidApiKey(apiKey)) {
        userProps.setProperty('API_KEY', apiKey);
      } else {
        ui.alert('Oops, that API key doesn’t seem valid. Please try again.');
        return null;
      }
    } else {
      ui.alert('You’ll need to enter an API key to use the custom formulas in this add-on.');
      return null;
    }
  }

  return apiKey;
}

// Helper function to validate the API key
function isValidApiKey(apiKey) {
  try {
    // Replace with your API's test endpoint and authentication method
    const testResponse = UrlFetchApp.fetch('https://api.your-service.com/test', {
      headers: { 'Authorization': `Bearer ${apiKey}` }
    });
    return testResponse.getResponseCode() === 200;
  } catch (error) {
    console.error('API validation failed:', error);
    return false;
  }
}

#2 OAuth 2.0 (If Your API Supports It) #

If the API you’re integrating with supports OAuth 2.0, this is even better than storing an API key. Instead of asking users to copy-paste a key, your add-on can trigger an OAuth flow to authenticate the user directly with the API provider.

• No need to store any credentials at all—Google handles the token management securely.
• Better user experience (no manual key entry) and higher security (tokens can be revoked, unlike static API keys).

If OAuth is an option for your API, this should be your top choice. Google’s Apps Script has built-in support for OAuth 2.0 via the OAuth2 library.

What to Avoid #

• Document Properties: These are tied to a single spreadsheet, so users would have to re-enter their key every time they use a new sheet—exactly what you’re trying to avoid.
• Script Properties: These are shared across all users of your add-on, so storing user-specific keys here would be a massive security breach (everyone’s keys would be accessible to everyone else).
• Storing in Sheet Cells: This is unsafe—keys are visible to anyone with access to the sheet, can be accidentally deleted, and might leak in formula logs or shared links.

Final Notes #

• Always validate the API key before storing it to prevent users from entering invalid credentials and getting confused later.
• Never log the API key in your script’s execution logs (even accidentally)—this is a common security slip-up.
• Request the minimum necessary OAuth scopes for your add-on. For example, if you only need to access the user’s properties and make external API calls, your scopes should be https://www.googleapis.com/auth/script.external_request and https://www.googleapis.com/auth/userinfo.profile (if needed).

内容的提问来源于stack exchange，提问作者Dave Sottimano