如何使用Google Apps Script批量获取YouTube频道的主题分类并添加至新列？

Got it, let's walk through how to automate adding topic categories for your 10,000 YouTube channels to a new column in your Google Sheet. This relies on the YouTube Data API, and we'll use Google Apps Script to handle the heavy lifting.

Step 1: Enable the YouTube Data API #

First, you need to enable the YouTube Data API v3 in your script's advanced services:

• Open your Google Sheet, go to Extensions > Apps Script to open the script editor.
• In the script editor, click Services > Add a service, search for "YouTube Data API v3", and enable it. This lets your script talk to YouTube's API without manually handling API keys.

Step 2: Paste the Script #

Replace the default code in the script editor with this complete solution. It handles different channel identifier formats, maps category IDs to readable names, and respects API limits:

function addYouTubeChannelCategories() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const data = sheet.getDataRange().getValues();
  const categoryMap = getYouTubeCategoryMap();
  
  // Assumes channel IDs/URLs are in Column A, starting at Row 2 (Row 1 is header)
  // We'll write categories to Column B
  for (let i = 1; i < data.length; i++) {
    const channelIdentifier = data[i][0];
    if (!channelIdentifier) continue; // Skip empty rows
    
    try {
      const channelId = extractChannelId(channelIdentifier);
      if (!channelId) {
        sheet.getRange(i+1, 2).setValue("Invalid channel ID/URL");
        continue;
      }
      
      // Fetch channel snippet data to get category ID
      const channelResponse = YouTube.Channels.list('snippet', {id: channelId});
      if (channelResponse.items.length === 0) {
        sheet.getRange(i+1, 2).setValue("Channel not found");
        continue;
      }
      
      const categoryId = channelResponse.items[0].snippet.categoryId;
      const categoryName = categoryMap[categoryId] || "Unknown category";
      sheet.getRange(i+1, 2).setValue(categoryName);
      
      // Pause every 50 requests to avoid hitting API rate limits
      if (i % 50 === 0) {
        SpreadsheetApp.flush();
        Utilities.sleep(1000);
      }
    } catch (error) {
      sheet.getRange(i+1, 2).setValue(`Error: ${error.message}`);
    }
  }
}

// Fetch a map of YouTube category IDs to human-readable names
function getYouTubeCategoryMap() {
  // Adjust regionCode to match your target region (e.g., 'GB' for UK, 'DE' for Germany)
  const response = YouTube.VideoCategories.list('snippet', {regionCode: 'US'});
  const categoryMap = {};
  response.items.forEach(category => {
    categoryMap[category.id] = category.snippet.title;
  });
  return categoryMap;
}

// Extract valid channel ID from URLs or raw IDs
function extractChannelId(identifier) {
  // Handle common channel URL formats: channel/, user/, c/
  const urlRegex = /(?:https?:\/\/)?(?:www\.)?(?:youtube\.com\/(channel\/|user\/|c\/)|youtu\.be\/)([a-zA-Z0-9_-]+)/;
  const match = identifier.match(urlRegex);
  
  if (match) {
    const idPart = match[2];
    // Handle YouTube usernames (youtube.com/user/XXX)
    if (identifier.includes('/user/')) {
      const userResponse = YouTube.Channels.list('id', {forUsername: idPart});
      return userResponse.items.length > 0 ? userResponse.items[0].id : null;
    }
    // Handle custom channel URLs (youtube.com/c/ChannelName)
    else if (identifier.includes('/c/')) {
      const searchResponse = YouTube.Search.list('id', {q: idPart, type: 'channel', maxResults: 1});
      return searchResponse.items.length > 0 ? searchResponse.items[0].id.channelId : null;
    }
    // Direct channel ID or channel/ URL
    else {
      return idPart;
    }
  }
  // Assume input is a raw channel ID if no URL pattern matches
  return identifier;
}

Step 3: Customize and Run the Script #

• Adjust Columns: If your channel list isn't in Column A, modify the data[i][0] part (e.g., data[i][1] for Column B) and the output column in sheet.getRange(i+1, 2) (e.g., 3 for Column C).
• Region Code: In getYouTubeCategoryMap(), change regionCode: 'US' to your target region for localized category names.
• Run the Script: Click the run button (▶️) in the script editor. You'll need to authorize the script to access your sheet and the YouTube API (follow the prompts, you may need to click "Advanced" > "Go to [Script Name]" to proceed).

Important Notes #

• API Quota: The YouTube Data API has a daily quota of 10,000 units. Each Channels.list request uses 1 unit, while Search.list (for custom /c/ URLs) uses 10 units. If you have many custom URLs, you may hit quota limits—try to get direct channel IDs where possible to save quota.
• Rate Limits: The script pauses every 50 requests to avoid triggering YouTube's rate limits. If you still get errors, increase the sleep time (e.g., Utilities.sleep(2000) for 2 seconds).
• Error Handling: The script will log errors like invalid URLs or missing channels directly in the spreadsheet so you can review and fix them later.

内容的提问来源于stack exchange，提问作者mert dökümcü