如何从Google Sheet的YouTube链接提取播放量并汇总？

Got it, let's tackle this problem—since your old playlist analyzer tool is gone and you don't want to drop cash on ScreamingFrog for a one-off task, here are three free, practical methods to extract YouTube view counts directly from your Google Sheet URLs:

1. Google Apps Script 批量提取（最推荐，无需外部工具） #

This is the smoothest option because it works entirely within your Google Sheet, no extra apps or API keys required (just a quick authorization step). Here's how to set it up:

• Open your Google Sheet and make sure your YouTube URLs are in a dedicated column (let's say column A, starting at row 2).
• Click Extensions > Apps Script to open the script editor.
• Delete the default myFunction() code and paste this instead:

function getYouTubeViews(url) {
  // Extract video ID from both youtube.com/watch and youtu.be formats
  let videoId;
  if (url.includes("youtube.com/watch")) {
    videoId = url.match(/v=([^&]+)/)[1];
  } else if (url.includes("youtu.be/")) {
    videoId = url.split("youtu.be/")[1].split("?")[0];
  } else {
    return "Invalid URL";
  }
  
  // Fetch video statistics
  try {
    const video = YouTube.Videos.list('statistics', {id: videoId});
    if (video.items.length > 0) {
      return video.items[0].statistics.viewCount;
    } else {
      return "Video not found";
    }
  } catch (e) {
    return "Error: " + e.message;
  }
}

// Optional: Bulk process an entire column at once
function bulkExtractViews() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const urls = sheet.getRange("A2:A" + sheet.getLastRow()).getValues();
  const views = urls.map(row => row[0] ? [getYouTubeViews(row[0])] : [""]);
  sheet.getRange("B2:B" + sheet.getLastRow()).setValues(views);
}

• Save the script with a name like YouTubeViewExtractor, then click the run button (▶️). The first time you run it, you'll need to authorize the script to access your sheet and YouTube data—just follow the prompts (you might need to click "Advanced" > "Go to [Script Name]" to bypass the safety warning).
• To use the function:
  • For single cells: In cell B2, type =getYouTubeViews(A2) and drag the fill handle down to apply to all rows.
  • For bulk processing: Run the bulkExtractViews function directly from the script editor, and it will auto-fill view counts in column B for all URLs in column A.

2. YouTube Data API + Google Sheets Formula（适合少量数据） #

If you don't want to mess with scripts, you can use a custom formula with the YouTube Data API. The free quota (10,000 requests per day) is more than enough for a one-off task:

1. Head to Google Cloud Console (find it via a quick search) to create a new project, enable the YouTube Data API v3, and generate a free API key.
2. Back in your Google Sheet, in cell B2 (next to your URL in A2), paste this formula (replace YOUR_API_KEY with the key you generated):

=INDEX(IMPORTDATA("https://www.googleapis.com/youtube/v3/videos?part=statistics&id="&REGEXEXTRACT(A2,"v=([^&]+)|youtu.be/([^?]+)")&"&key=YOUR_API_KEY"),2,2)

3. Drag the fill handle down to apply the formula to all rows. The formula extracts the video ID from the URL, fetches the stats via the API, and pulls the view count into your sheet.

3. Free Online Bulk Tools（适合不想折腾代码的情况） #

If you want to avoid code entirely, there are free online tools that let you paste multiple YouTube URLs and get view counts in return. Just search for "free bulk YouTube view counter"—stick to tools that don't require you to create an account, and double-check their privacy policy (since you're sharing URLs, make sure they don't store your data long-term). This is a good option if you only have a handful of videos to process.

Quick Notes #

• Make sure your YouTube videos are public—you can't get view counts for private/unlisted videos with these methods.
• Both Apps Script and the Data API have quota limits, but they're more than sufficient for a one-off task.
• If you use the API key, don't share it publicly—you can delete the key or disable the API in Google Cloud once you're done.

内容的提问来源于stack exchange，提问作者Space Toe Jam