如何在Google Apps Script中使用需API Key的外部API？

Absolutely! Google Apps Script’s UrlFetchApp service fully supports making HTTP requests to external APIs, including those that require an API key for authentication. As long as the API follows standard REST conventions, you can interact with it just like you would in any other programming environment.

Here’s a step-by-step guide to doing this securely and effectively:

1. Get Your API Key First #

First, you’ll need to obtain an API key from the service provider of the API you want to use. This usually involves signing up for their service, creating a project, and generating a key. Make sure to read their documentation to understand how the key should be passed (either in the URL parameters or request headers).

2. Store Your API Key Securely #

Never hardcode your API key directly in your script—this is a huge security risk, especially if you share your script or publish it. Instead, use Google Apps Script’s built-in PropertiesService to store the key:

Option 1: Set the key via code #

Run this function once to save your key to the script’s properties (you can delete or comment it out afterward):

function saveApiKey() {
  const props = PropertiesService.getScriptProperties();
  props.setProperty('MY_API_KEY', 'your_actual_api_key_here');
}

Option 2: Set via the script editor UI #

1. Open your script in the Apps Script editor
2. Click Project Settings (the gear icon)
3. Scroll to the Script properties section
4. Click Add script property, enter a key name (like MY_API_KEY) and your actual API key as the value

3. Make the API Request #

How you structure the request depends on how the API expects to receive the key. Here are the two most common scenarios:

Scenario 1: API Key in URL Parameters #

Many APIs expect the key to be passed as a query parameter (e.g., ?key=your_key). Here’s an example:

function fetchDataWithUrlKey() {
  const props = PropertiesService.getScriptProperties();
  const apiKey = props.getProperty('MY_API_KEY');
  const apiUrl = `https://api.example.com/endpoint?key=${apiKey}&query=your_search_term`;

  try {
    // Make the request
    const response = UrlFetchApp.fetch(apiUrl);
    
    // Parse the JSON response
    const data = JSON.parse(response.getContentText());
    
    // Do something with the data (e.g., log it or write to a Sheet)
    console.log('Successfully fetched data:', data);
    return data;
  } catch (error) {
    console.error('Error fetching data:', error.message);
    // Optionally handle the error (e.g., send an alert)
    throw error;
  }
}

Scenario 2: API Key in Request Headers #

Some APIs require the key to be sent in an HTTP header (e.g., Authorization: Bearer your_key or X-API-Key: your_key). Here’s how to do that:

function fetchDataWithHeaderKey() {
  const props = PropertiesService.getScriptProperties();
  const apiKey = props.getProperty('MY_API_KEY');
  const apiUrl = 'https://api.example.com/endpoint';

  // Define request options with headers
  const options = {
    method: 'GET',
    headers: {
      'X-API-Key': apiKey, // Adjust the header name based on the API's docs
      'Content-Type': 'application/json'
    }
  };

  try {
    const response = UrlFetchApp.fetch(apiUrl, options);
    const data = JSON.parse(response.getContentText());
    
    console.log('Successfully fetched data:', data);
    return data;
  } catch (error) {
    console.error('Error fetching data:', error.message);
    throw error;
  }
}

Important Notes #

• Check Rate Limits: Most APIs have rate limits (how many requests you can make per minute/hour). Make sure your script stays within these limits to avoid being blocked.
• Handle Error Responses: Always check the response status code (using response.getResponseCode()) to catch errors like 401 (unauthorized) or 429 (rate limit exceeded).
• Use Secret Manager for Sensitive Keys: If you’re working with a particularly sensitive API key, consider using Google Cloud’s Secret Manager instead of PropertiesService (this requires linking your script to a Google Cloud project).

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