如何自动获取Google Trends趋势数据并同步至Google Sheet

问题概述 #

需求为自动将Google Trends的Interest Over Time图表数据同步至Google Sheet，目标获取过去一年至今的搜索趋势数据。当前遇到两个核心问题：

• 使用Pytrends获取的结果与Google Trends官方数据存在偏差
• 通过Google Apps Script调用https://trends.google.com/trends/api/widgetdata/multiline/csv接口时，返回Error 400（Bad Request）错误

期望实现：在控制面板修改关键词后，触发按钮即可自动获取CSV数据并写入Sheet。

已尝试方案 #

• Pytrends：结果与官方数据不符
• Google Apps Script调用指定接口：返回400错误

问题分析与修复方案 #

原脚本存在几个关键问题：关键词格式不符合接口要求、硬编码的token会定期失效、缺少必要请求头导致被拦截，以下是修复后的完整方案：

修复后的Google Apps Script代码 #

function updateResults() {
  // 获取当前表格实例与控制面板Sheet
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const criteriaSheet = ss.getSheetByName("Criteria");
  
  // 提取并清洗关键词（过滤空行）
  const keywordRange = criteriaSheet.getRange("E1:E5").getValues();
  const keywords = keywordRange.flat().filter(keyword => keyword !== "");
  
  // 提取国家参数，无值则留空
  const country = criteriaSheet.getRange("G1").getValue() || "";
  // 自动计算过去一年的日期范围
  const endDate = new Date();
  const startDate = new Date(endDate.getFullYear() - 1, endDate.getMonth(), endDate.getDate());

  // 格式化日期为Google Trends要求的格式
  const formattedStartDate = Utilities.formatDate(startDate, "GMT", "yyyy-MM-dd");
  const formattedEndDate = Utilities.formatDate(endDate, "GMT", "yyyy-MM-dd");

  // 构建符合接口要求的关键词请求结构
  const comparisonItem = keywords.map(keyword => ({
    geo: country ? { country: country } : {},
    complexKeywordsRestriction: {
      keyword: [{ type: "BROAD", value: keyword }]
    }
  }));

  // 组装完整的请求参数
  const reqPayload = {
    time: `${formattedStartDate} ${formattedEndDate}`,
    resolution: "WEEK",
    locale: "en-US",
    comparisonItem: comparisonItem,
    requestOptions: {
      property: "",
      backend: "IZG",
      category: 0
    }
  };

  // 编码请求参数
  const encodedReq = encodeURIComponent(JSON.stringify(reqPayload));

  // 先访问Google Trends主页，动态获取有效token与时区
  const initialFetch = UrlFetchApp.fetch("https://trends.google.com/trends/explore", {
    headers: {
      "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36"
    }
  });
  
  const contentText = initialFetch.getContentText();
  const tokenMatch = contentText.match(/"token":"([^"]+)"/);
  const tzMatch = contentText.match(/"tz":(-?\d+)/);
  
  if (!tokenMatch || !tzMatch) {
    throw new Error("无法获取Google Trends的有效Token或时区");
  }
  
  const token = tokenMatch[1];
  const tz = tzMatch[1];

  // 构建最终请求URL
  const fetchUrl = `https://trends.google.com/trends/api/widgetdata/multiline/csv?hl=en-US&tz=${tz}&req=${encodedReq}&token=${token}`;

  // 发送请求获取CSV数据，添加必要请求头模拟浏览器访问
  const trendsResponse = UrlFetchApp.fetch(fetchUrl, {
    headers: {
      "User-Agent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/114.0.0.0 Safari/537.36",
      "Referer": "https://trends.google.com/trends/explore"
    }
  });

  // 处理返回的CSV数据，移除开头的无效字符
  const csvContent = trendsResponse.getContentText().replace(/^.*?\n/, "");
  const data = Utilities.parseCsv(csvContent);

  // 清空旧数据并写入新数据
  const resultsSheet = ss.getSheetByName("Results");
  resultsSheet.clearContents();
  
  if (data.length > 0) {
    resultsSheet.getRange(1, 1, data.length, data[0].length).setValues(data);
    // 格式化日期列
    const dateRange = resultsSheet.getRange(2, 1, data.length - 1, 1);
    dateRange.setNumberFormat("yyyy-MM-dd");
  }
}

关键修改说明 #

1. 关键词格式修正：将每个关键词转为接口要求的对象结构，确保与Google Trends官方请求一致
2. 动态获取Token：不再硬编码token，而是从主页实时提取有效token，避免失效问题
3. 请求头补充：添加User-Agent和Referer头，模拟浏览器访问，防止被接口拦截
4. 自动日期计算：自动生成过去一年的日期范围，无需手动输入
5. CSV数据清洗：移除接口返回的开头无效字符，确保解析正常
6. 批量写入优化：使用setValues批量写入数据，比逐行写入效率更高

额外优化建议 #

• 可添加try-catch块捕获请求错误，并在Sheet中显示错误提示
• 在Google Sheet中创建绘图按钮并绑定updateResults函数，方便快速触发同步
• 在Criteria Sheet中添加关键词非空验证，避免空关键词导致的请求异常
• 根据实际时区调整日期格式化的时区参数

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