Google Apps Script提取Google Trends数据：Sheets第二、三列无输出求助

我尝试通过Google Apps Script从Google Trends提取趋势搜索数据至Google Sheets，目前第一列可正常输出，但第二、三列（新闻标题和链接）无内容。以下是我的代码：

function extractTrendingSearches() {

// 1. Visit this url: https://trends.google.com/trends/trendingsearches/daily/rss?geo=US

const url = "https://trends.google.com/trends/trendingsearches/daily/rss?geo=US";

// Fetch the RSS feed

const response = UrlFetchApp.fetch(url);

const feed = XmlService.parse(response.getContentText());

const items = feed.getRootElement().getChildren("channel")[0].getChildren("item");

// Get the sheet to store the data

const sheetName = "Trends";

const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);

// Get the existing data in the sheet

const dataRange = sheet.getDataRange();

const dataValues = dataRange.getValues();

// Extract unique titles from existing data

const existingTitles = new Set();

dataValues.forEach(row => {

const title = row[0];

if (title) existingTitles.add(title);

});

// Clear only the first three columns

sheet.getRange(1, 1, dataValues.length, 3).clearContent();

// Add headers to the sheet if the first row is empty

if (sheet.getRange("A1").getValue() === "") {

const headers = ["Title", "News Item Title", "News Item URL"];

sheet.appendRow(headers);

}

// Extract the data from each item and add it to the sheet if it is not already in the sheet

let count = 0;

for (let i = 0; i < items.length; i++) {

const item = items[i];

const title = item.getChild("title").getText();

if (!existingTitles.has(title)) {

const newsItem = item.getChild("ht:news_item", XmlService.getNamespace("ht"));

const newsItemTitle = newsItem ? newsItem.getChild("ht:news_item_title", XmlService.getNamespace("ht")).getText() : "";

const newsItemUrl = newsItem ? newsItem.getChild("ht:news_item_url", XmlService.getNamespace("ht")).getText() : "";

const row = [title, newsItemTitle, newsItemUrl];

sheet.appendRow(row);

count++;

if (count === 10) break; // Stop after extracting 10 items

}

}

}

问题排查与修复 #

核心原因 #

原代码处理XML命名空间的方式错误。Google Trends RSS里的ht前缀对应的命名空间URI是http://www.google.com/trends/hottrends，直接调用XmlService.getNamespace("ht")无法正确匹配节点，导致无法获取ht:news_item相关字段，所以第二、三列返回空值。

修复后的代码 #

function extractTrendingSearches() {
  const url = "https://trends.google.com/trends/trendingsearches/daily/rss?geo=US";
  const response = UrlFetchApp.fetch(url);
  const feed = XmlService.parse(response.getContentText());
  
  // 明确指定ht命名空间的URI
  const htNamespace = XmlService.getNamespace("ht", "http://www.google.com/trends/hottrends");
  
  const items = feed.getRootElement().getChildren("channel")[0].getChildren("item");

  const sheetName = "Trends";
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(sheetName) || SpreadsheetApp.getActiveSpreadsheet().insertSheet(sheetName);

  const dataRange = sheet.getDataRange();
  const dataValues = dataRange.getValues();

  const existingTitles = new Set();
  dataValues.forEach(row => {
    const title = row[0];
    if (title) existingTitles.add(title);
  });

  sheet.getRange(1, 1, dataValues.length, 3).clearContent();

  if (sheet.getRange("A1").getValue() === "") {
    const headers = ["Title", "News Item Title", "News Item URL"];
    sheet.appendRow(headers);
  }

  let count = 0;
  for (let i = 0; i < items.length; i++) {
    const item = items[i];
    const title = item.getChild("title").getText();

    if (!existingTitles.has(title)) {
      // 使用正确的命名空间获取节点，节点名无需带ht:前缀
      const newsItem = item.getChild("news_item", htNamespace);
      const newsItemTitle = newsItem ? newsItem.getChild("news_item_title", htNamespace).getText() : "";
      const newsItemUrl = newsItem ? newsItem.getChild("news_item_url", htNamespace).getText() : "";

      const row = [title, newsItemTitle, newsItemUrl];
      sheet.appendRow(row);
      count++;
      if (count === 10) break;
    }
  }
}

关键修改点 #

1. 命名空间定义：通过XmlService.getNamespace("ht", "http://www.google.com/trends/hottrends")明确绑定前缀与对应URI。
2. 节点获取逻辑：获取news_item系列节点时，传入正确的命名空间对象，且节点名不再携带ht:前缀（命名空间已通过参数声明）。

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