技术求助：如何从YouTube频道抓取上传日期并汇总至Google Sheet

Got it, let's walk through this step by step—no dense API docs to wade through, just straightforward actions to pull those YouTube upload dates into your Google Sheet. We'll use Google Apps Script since it integrates seamlessly with both YouTube and Sheets, so you don't have to mess with local code or authentication headaches.

• Open a new Google Sheet and name it something like "YouTube Upload Tracker"
• Click Extensions > Apps Script to open the script editor (this is where we'll write our code)
• Delete the default myFunction() code—we'll replace it with our own

Apps Script needs permission to access YouTube's API, so let's add that service:

• In the script editor, click Services > Add Service
• Find YouTube Data API in the list, select it, and click Add (make sure the version is set to v3)

Replace the empty script editor with this code—be sure to update the channelId value with your target channel's ID:

function fetchYouTubeUploadDates() {
  // Replace this with your target YouTube channel ID
  // Find it in the channel's URL (e.g., UC123456789abcdefghijklmnop)
  const channelId = "UCXXXXXXXXXXXXXXXXXXXX";
  
  // Get the active sheet in your Google Sheet
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  
  // Clear existing data (keep the header we'll add next)
  sheet.clearContents();
  // Add header row
  sheet.appendRow(["Video Title", "Upload Date", "Video Link"]);
  
  // First, get the channel's "uploads" playlist ID (YouTube stores all uploads here)
  const channelInfo = YouTube.Channels.list("contentDetails", {id: channelId});
  const uploadsPlaylistId = channelInfo.items[0].contentDetails.relatedPlaylists.uploads;
  
  // Fetch videos from the uploads playlist (handle pagination—YouTube limits 50 per request)
  let nextPageToken = "";
  do {
    const playlistItems = YouTube.PlaylistItems.list("snippet", {
      playlistId: uploadsPlaylistId,
      maxResults: 50,
      pageToken: nextPageToken
    });
    
    // Write each video's data to the sheet
    playlistItems.items.forEach(item => {
      const title = item.snippet.title;
      const uploadDate = item.snippet.publishedAt; // Format: YYYY-MM-DDTHH:MM:SSZ
      const videoUrl = `https://www.youtube.com/watch?v=${item.snippet.resourceId.videoId}`;
      sheet.appendRow([title, uploadDate, videoUrl]);
    });
    
    // Move to the next page of results (if there is one)
    nextPageToken = playlistItems.nextPageToken;
  } while (nextPageToken);
  
  // Show a confirmation alert when done
  SpreadsheetApp.getUi().alert("Success! Upload dates have been added to your sheet.");
}

Quick Notes on Customization: #

• Finding the Channel ID: If the channel uses a custom URL (like @MyChannel), you can add this helper function to get the ID automatically:

  function getChannelIdByUsername(username) {
    const searchResults = YouTube.Search.list("id", {q: username, type: "channel", maxResults: 1});
    return searchResults.items[0].id.channelId;
  }
  

  Then replace the channelId line with:

  const channelId = getChannelIdByUsername("MyChannel"); // Replace with the @ username
  

• Date Format: The upload date comes in ISO format (YYYY-MM-DDTHH:MM:SSZ). You can format it directly in your sheet using =DATEVALUE(LEFT(B2, 10)) to get just the date, or modify the script to format it before writing.

• Click the Run button (▶️) in the script editor. The first time you run it, Google will ask for permission:
  1. Click Review Permissions
  2. Select your Google account
  3. Click Advanced > Go to [Your Script Name] (unsafe) (this is safe—Google flags un-verified scripts, but you wrote this one!)
  4. Click Allow to grant access

If you want the sheet to refresh with new uploads automatically:

• Click the Triggers icon (⏰) in the script editor
• Click Add Trigger
• Configure it like this:
  • Choose which function to run: fetchYouTubeUploadDates
  • Choose which deployment to run: Head
  • Select event source: Time-driven
  • Select type of time based trigger: Pick a frequency (e.g., "Day timer" for daily updates)
  • Click Save

Important Quota Note: #

YouTube's Data API has a free daily quota of 10,000 units. This script uses ~1 unit per playlist page request plus 1 unit to get the channel info—most small to medium channels will use way less than the quota, so you won't hit limits.

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