如何在Google Apps Script中遍历多URL获取Simpli.fi API的JSON响应

Let's walk through fixing your script to handle bulk API calls properly—you're almost there, just a few key issues with how you're building your URL list and using UrlFetchApp.fetchAll.

First, Let's Identify the Core Problems #

• URL Array Construction: You're overwriting urlOneArray with a single string each time instead of adding URLs to the array.
• Misusing fetchAll: fetchAll is designed to take an array of URLs (or request objects) and process them in bulk—you don't need to loop through it, and passing a single string instead of an array is causing that error.
• Response Handling: fetchAll returns an array of responses, not a single one, so your current check for response.getResponseCode() won't work.
• Data Parsing: Your getData function assumes a single audience object, but you'll get multiple audiences per client, plus multiple clients' data to process.

Here's the Corrected Full Script #

// Authenticate API call - Keep these secure (consider using PropertiesService instead of hardcoding!)
var X_USER_KEY = 'XXXX';
var X_APP_KEY = 'XXXX';

function simplifiService() {
  var baseURL = 'https://app.simpli.fi/api/organizations';
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  
  // Get client codes from the formatting sheet
  var sheet = ss.getSheetByName('formatting');
  var range = sheet.getRange('B2:B').getValues();
  var clients = range.filter(String); // Remove empty rows
  
  // 1. Build the full array of URLs correctly
  var urlArray = [];
  for (var i = 0; i < clients.length; i++) {
    var clientId = clients[i][0]; // clients is a 2D array, so we need to get the first element of each row
    var fullUrl = baseURL + '/' + clientId + '/audiences'; // Added slash between baseURL and clientId
    urlArray.push(fullUrl);
  }
  Logger.log('Generated URLs: ' + urlArray);

  // 2. Set up API request parameters
  var params = {
    method: 'GET',
    headers: {
      "x-app-key": X_APP_KEY,
      "x-user-key": X_USER_KEY
    },
    muteHttpExceptions: true
  };

  // 3. Use fetchAll correctly - bulk fetch all URLs at once
  var responses = UrlFetchApp.fetchAll(urlArray, params);
  
  // 4. Process each response individually
  responses.forEach(function(response, index) {
    var clientId = clients[index][0]; // Match response to its client
    if (response.getResponseCode() === 200) {
      var data = JSON.parse(response.getContentText()); // Get JSON content from response
      Logger.log('Successfully fetched data for client: ' + clientId);
      processClientAudiences(data, clientId); // Process this client's audiences
    } else {
      Logger.log('Error fetching data for client ' + clientId + ': ' + response.getResponseCode());
    }
    Utilities.sleep(500); // Optional: Throttle if needed
  });
}

// 5. Process and write audience data to the sheet
function processClientAudiences(audienceData, clientId) {
  var date = new Date();
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var targetSheet = ss.getSheetByName('Campaign Data');
  
  // Check if audienceData has an 'audiences' array (adjust based on Simpli.fi's actual response structure)
  if (!audienceData.audiences || !Array.isArray(audienceData.audiences)) {
    Logger.log('No audiences found for client ' + clientId);
    return;
  }

  // Prepare rows to append (batch append is more efficient than single appendRow)
  var rowsToAppend = audienceData.audiences.map(function(audience) {
    return [
      date,
      clientId,
      audience.id || 'N/A', // Use actual audience ID field from API
      audience.name || 'N/A'
    ];
  });

  // Append all rows at once if there's data
  if (rowsToAppend.length > 0) {
    targetSheet.getRange(targetSheet.getLastRow() + 1, 1, rowsToAppend.length, rowsToAppend[0].length).setValues(rowsToAppend);
  }
}

Key Changes Explained #

1. URL Array Fix: Instead of overwriting urlOneArray, we use push() to add each constructed URL to the array. Also, note that getValues() returns a 2D array, so we access clients[i][0] to get the actual client ID string.
2. Proper fetchAll Usage: We pass the entire urlArray to fetchAll in one call, which is more efficient than looping individual fetch calls.
3. Response Processing: We loop through the responses array (one per URL) and handle each client's data separately. We also use getContentText() to get the JSON string from the response.
4. Batch Data Writing: Instead of calling appendRow() for each audience (which is slow), we build an array of rows and write them all at once with setValues()—this is a best practice for Google Apps Script performance.
5. Robust Data Handling: We added checks for missing data fields and ensure we're accessing the correct audiences array from the API response.

Additional Tips #

• Secure Your API Keys: Don't hardcode keys in your script—use PropertiesService.getScriptProperties() to store them securely.
• Check API Rate Limits: Simpli.fi's API might have rate limits, so adjust the Utilities.sleep() duration or add error handling for rate limit responses (429 status code).
• Validate API Response Structure: Double-check Simpli.fi's API docs to confirm the exact structure of the /audiences response—adjust the processClientAudiences function if the fields differ from what's assumed here.

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