如何让Google Apps Script每小时更新表格并实现追加去重？

一、设置每小时定时运行 #

• 打开目标Google表格，点击顶部菜单「工具」>「脚本编辑器」
• 点击左侧菜单栏的「触发器」图标（闹钟样式）
• 点击「添加触发器」，按以下配置设置：
  • 选择函数：youTubeSearchResults
  • 事件源：「时间驱动」
  • 类型：「小时计时器」
  • 间隔：「每小时」
  • 保存后，脚本就会每小时自动运行一次

二、修改后的完整代码 #

function youTubeSearchResults() {
  // 1. 获取当前工作表及A列关键词
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const values = sheet.getRange("A2:A" + sheet.getLastRow()).getDisplayValues().filter(([a]) => a);

  // 2. 调用API获取YouTube搜索结果
  const modifyResults = values.flatMap(([keywords]) => {
    const searchResults = JSON.parse(UrlFetchApp.fetch(`https://yt.lemnoslife.com/noKey/search?part=snippet&q=${keywords}&maxResults=10&type=video&order=viewCount&videoDuration=short&publishedAfter=2022-09-27T00:00:00Z`).getContentText());
    
    const fSearchResults = searchResults.items.filter(function (sr) { return sr.id.kind === "youtube#video" });
    return fSearchResults.map(function (sr) { return [keywords, sr.id.videoId, `https://www.youtube.com/watch?v=${sr.id.videoId}`, sr.snippet.title, sr.snippet.publishedAt, sr.snippet.channelTitle, sr.snippet.channelId, `https://www.youtube.com/channel/${sr.snippet.channelId}`, sr.snippet.thumbnails.high.url] });
  });

  // 3. 获取视频播放量和频道订阅数
  const { videoIds, channelIds } = modifyResults.reduce((o, r) => {
    o.videoIds.push(r[1]);
    o.channelIds.push(r[6]);
    return o;
  }, { videoIds: [], channelIds: [] });
  const limit = 50;
  const { viewCounts, subscriberCounts } = [...Array(Math.ceil(videoIds.length / limit))].reduce((obj, _) => {
    const vIds = videoIds.splice(0, limit);
    const cIds = channelIds.splice(0, limit);
    const res1 = YouTube.Videos.list(["statistics"], { id: vIds, maxResults: limit }).items.map(({ statistics: { viewCount } }) => viewCount);
    const obj2 = YouTube.Channels.list(["statistics"], { id: cIds, maxResults: limit }).items.reduce((o, { id, statistics: { subscriberCount } }) => (o[id] = subscriberCount, o), {});
    const res2 = cIds.map(e => obj2[e] || null);
    obj.viewCounts = [...obj.viewCounts, ...res1];
    obj.subscriberCounts = [...obj.subscriberCounts, ...res2];
    return obj;
  }, { viewCounts: [], subscriberCounts: [] });
  const ar = [viewCounts, subscriberCounts];
  const rr = ar[0].map((_, c) => ar.map(r => r[c]));

  // 4. 合并所有数据
  const newData = modifyResults.map((r, i) => [...r, ...rr[i]]);

  // 5. 读取现有数据（跳过表头）并合并新数据
  const existingData = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn()).getValues();
  const allData = [...existingData, ...newData];

  // 6. 基于C列（视频链接，数组索引为2）去重，保留最新数据
  const uniqueDataMap = {};
  allData.forEach(row => {
    const key = row[2]; // C列的值作为唯一键
    uniqueDataMap[key] = row; // 后续相同键的行覆盖旧行，实现保留新值
  });
  const uniqueData = Object.values(uniqueDataMap);

  // 7. 将去重后的数据写入表格（从第2行开始，覆盖原有数据）
  if (uniqueData.length > 0) {
    sheet.getRange(2, 1, uniqueData.length, uniqueData[0].length).setValues(uniqueData);
  }
}

关键修改说明 #

• 数据追加：先读取表格中已有的所有数据（跳过第1行表头），将新获取的数据与现有数据合并，再进行去重处理
• 去重逻辑：使用对象uniqueDataMap，以C列的视频链接作为键，遍历所有数据时，新的相同键条目会覆盖旧条目，最终保留最新获取的数据
• 写入数据：将去重后的完整数据从第2行开始写入，确保表头不变，同时更新为去重后的最新数据

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