YouTube Data API导入谷歌表格importchannel函数失效及需求咨询

针对你的问题，我们需要修复频道查询逻辑并调整参数支持YouTube频道handle（带@或不带格式），以下是具体修改方案：

核心修改说明 #

• 将原channelName参数替换为channelHandle，自动兼容带@的输入格式
• 使用YouTube Data API的channels.list接口，通过forHandle参数精准查询频道数据，解决返回结果一致的问题
• 补充参数编码逻辑，避免特殊字符导致的请求异常

修改后的完整脚本 #

/**
 * IMPORT FROM YOUTUBE CUSTOM FUNCTIONS
 * 
 * Written with ❤️ by Joseph Asinyo
 * Modified to support YouTube Channel Handles
 */

// IMPORTANT NOTE: Deploy the script as a web app and insert the url below
var WEB_APP_URL = "https://script.google.com/macros/s/**keyhiddenforprivacy**/exec";
// 替换为你的YouTube Data API密钥（需在Google Cloud Console启用API）
var YOUTUBE_API_KEY = "YOUR_YOUTUBE_API_KEY";

// Web Apps using as the wrapper.
function doGet(e) {
  var request_type = e.parameter.request_type;
  var res;
  if(request_type == "importvideo"){
    res = getVideoData(e.parameter.videoLink);
  } else if(request_type == "importsearch"){
    res = getSearchData(e.parameter.searchTerm, e.parameter.numberOfVideos);
  } else if(request_type == "importchannel"){
    // 替换参数名从channelName为channelHandle
    res = getChannelData(e.parameter.channelHandle);
  } else if(request_type == "importchannelvideos"){
    // 同步调整频道视频查询的参数
    res = getChannelVideosData(e.parameter.channelHandle, e.parameter.numberOfVideos);
  } else if(request_type == "importcomments"){
    res = getCommentsData(e.parameter.videoLink, e.parameter.numberOfComments, e.parameter.getReplies);
  } 

  return ContentService.createTextOutput(JSON.stringify({data: res}));
}

function makeRequestToWebApp(parameters) {
  var url = WEB_APP_URL+"?";

  for (const key of Object.keys(parameters)) {
    // 增加参数编码，避免handle中的特殊字符破坏请求
    url += key+"="+encodeURIComponent(parameters[key])+"&";
  }
  url = url.slice(0, -1); // 移除末尾多余的&

  console.log(url);

  var response = UrlFetchApp.fetch(url).getContentText();
  var result = JSON.parse(response).data;

  return result;
}

// 修复并重写频道数据查询函数，支持handle格式
function getChannelData(channelHandle) {
  // 自动去除handle前的@符号
  const cleanHandle = channelHandle.replace(/^@/, "");
  
  // 构建YouTube API请求，获取频道核心数据
  const apiUrl = `https://www.googleapis.com/youtube/v3/channels?part=snippet,statistics,contentDetails&forHandle=${cleanHandle}&key=${YOUTUBE_API_KEY}`;
  
  try {
    const response = UrlFetchApp.fetch(apiUrl);
    const data = JSON.parse(response.getContentText());
    
    if (data.items.length === 0) {
      return ["未找到对应频道"];
    }
    
    const channel = data.items[0];
    // 返回自定义字段，可根据需求调整
    return [
      channel.snippet.title,
      channel.snippet.description,
      channel.statistics.subscriberCount,
      channel.statistics.viewCount,
      channel.statistics.videoCount,
      channel.snippet.thumbnails.high.url
    ];
  } catch (error) {
    return ["API请求错误: " + error.message];
  }
}

// 同步修改频道视频查询逻辑，通过handle获取上传列表ID
function getChannelVideosData(channelHandle, numberOfVideos) {
  const cleanHandle = channelHandle.replace(/^@/, "");
  const apiUrl = `https://www.googleapis.com/youtube/v3/channels?part=contentDetails&forHandle=${cleanHandle}&key=${YOUTUBE_API_KEY}`;
  
  try {
    const response = UrlFetchApp.fetch(apiUrl);
    const data = JSON.parse(response.getContentText());
    
    if (data.items.length === 0) {
      return [["未找到对应频道"]];
    }
    
    const uploadsPlaylistId = data.items[0].contentDetails.relatedPlaylists.uploads;
    // 调用播放列表视频查询函数（需确保原代码有此函数，或补充实现）
    return getPlaylistVideos(uploadsPlaylistId, numberOfVideos);
  } catch (error) {
    return [["API请求错误: " + error.message]];
  }
}

// 补充播放列表视频查询函数（若原代码未实现）
function getPlaylistVideos(playlistId, maxResults) {
  const apiUrl = `https://www.googleapis.com/youtube/v3/playlistItems?part=snippet,contentDetails&playlistId=${playlistId}&maxResults=${maxResults}&key=${YOUTUBE_API_KEY}`;
  
  const response = UrlFetchApp.fetch(apiUrl);
  const data = JSON.parse(response.getContentText());
  
  return data.items.map(item => [
    item.snippet.title,
    `https://www.youtube.com/watch?v=${item.contentDetails.videoId}`,
    item.snippet.publishedAt,
    item.snippet.description
  ]);
}

使用注意事项 #

1. API密钥配置：需在Google Cloud Console创建项目，启用YouTube Data API v3并生成密钥，替换脚本中的YOUTUBE_API_KEY
2. Web App重新部署：修改脚本后需重新部署为Web App，更新WEB_APP_URL
3. Sheet函数调用：将原=IMPORTCHANNEL("频道名称")改为=IMPORTCHANNEL("@频道handle")或=IMPORTCHANNEL("频道handle")

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