使用Google Apps Script获取特定Google Trends数据遇问题求助

初始尝试触发429错误 #

我想通过Google Apps Script获取Google Trends折线图的最后一个数值（目标值43），运行了以下代码：

function W5(){
  var url ='https://trends.google.com/trends/explore?geo=US&q=%2Fm%2F07s_c';  
  var result = UrlFetchApp.fetch(url);
  const $ = Cheerio.load(result.getContentText());
  Logger.log($);
  return;
}

但直接收到429错误：

Exception: Request failed for https://trends.google.com returned code 429. Truncated server response: Error 429 (Too Many Req...(use muteHttpExceptions option to examine full response)
我从未成功发起过请求，却提示请求过多。

参考方案后仍无法获取CSV数据 #

之后我修改了脚本，从表格读取参数生成查询链接和CSV下载链接，但脚本仅能生成链接，无法获取实际CSV内容：

function startQuery() {
  var sheet = ss.getSheetByName("Sheet2");

  // start the query
  var result = buildQueryString(sheet.getRange("A2").getValue(),
     sheet.getRange("B2").getValue());

  
  // display the resulting link in a cell
  sheet.getRange("C2").setValue(result);

  var csv_result = generateCsvDownload(sheet.getRange("A2").getValue(),
     sheet.getRange("B2").getValue());

  sheet.getRange("D2").setValue(csv_result);
}

function buildQueryString(geo,q) {
  return "http://www.google.com/trends/explore?" +
  "geo=" + encodeURIComponent(geo) +
  "&q=" + encodeURIComponent(q)
}

function generateCsvDownload(geo,q) {
  return "http://www.google.com/trends/viz?" +
 "geo=" + encodeURIComponent(geo) +
  "&q=" + encodeURIComponent(q) + 
  "&graph=all_csv";
}

表格参数如下：

解决方案 #

1. 解决429请求过多错误 #

Google Trends会检测请求来源，直接用UrlFetchApp发起请求会被识别为非浏览器请求，需要添加模拟浏览器的请求头，同时避免频繁请求：

function fetchTrendsData() {
  var url = 'https://trends.google.com/trends/explore?geo=US&q=%2Fm%2F07s_c';
  var options = {
    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'
    },
    muteHttpExceptions: true // 方便查看完整错误响应
  };
  var result = UrlFetchApp.fetch(url, options);
  Logger.log(result.getResponseCode());
  Logger.log(result.getContentText());
}

2. 正确获取并解析CSV数据 #

旧的trends/viz链接已失效，现在需要通过Google Trends的动态token机制获取CSV数据，完整脚本示例：

function getTrendsCSVAndLastValue() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
  var geo = sheet.getRange("A2").getValue();
  var query = sheet.getRange("B2").getValue();
  var encodedQuery = encodeURIComponent(query);
  
  // 第一步：获取页面动态token
  var exploreUrl = `https://trends.google.com/trends/explore?geo=${geo}&q=${encodedQuery}`;
  var options = {
    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'
    },
    muteHttpExceptions: true
  };
  var pageResponse = UrlFetchApp.fetch(exploreUrl, options);
  var pageContent = pageResponse.getContentText();
  
  // 提取页面中的请求token
  var initialStateMatch = pageContent.match(/"exploreTask":.*?"token":"([^"]+)"/);
  if (!initialStateMatch) {
    sheet.getRange("E2").setValue("无法获取请求token");
    return;
  }
  var token = initialStateMatch[1];
  
  // 第二步：请求CSV数据
  var csvUrl = `https://trends.google.com/trends/api/widgetdata/multiline/csv?req=%7B%22time%22%3A%22today%205-y%22%2C%22resolution%22%3A%22WEEK%22%2C%22locale%22%3A%22en-US%22%2C%22comparisonItem%22%3A%5B%7B%22geo%22%3A%7B%22country%22%3A%22${geo}%22%7D%2C%22complexKeywordsRestriction%22%3A%7B%22keyword%22%3A%5B%7B%22type%22%3A%22BROAD%22%2C%22value%22%3A%22${encodedQuery}%22%7D%5D%7D%7D%5D%2C%22requestOptions%22%3A%7B%22property%22%3A%22%22%2C%22backend%22%3A%22IZG%22%2C%22category%22%3A0%7D%7D&token=${token}&tz=-480`;
  
  var csvResponse = UrlFetchApp.fetch(csvUrl, options);
  var csvContent = csvResponse.getContentText();
  
  // 解析CSV，提取最后一行的数值
  var rows = csvContent.split("\n");
  // 过滤表头和空行
  var dataRows = rows.filter(row => row.trim() !== "" && !row.startsWith("Date"));
  if (dataRows.length === 0) {
    sheet.getRange("E2").setValue("无有效数据");
    return;
  }
  var lastRow = dataRows[dataRows.length - 1];
  var lastValue = lastRow.split(",")[1].trim(); // 假设第二列为目标数值列
  
  // 将结果写入表格
  sheet.getRange("C2").setValue(exploreUrl);
  sheet.getRange("D2").setValue(csvUrl);
  sheet.getRange("E2").setValue(lastValue);
}

注意事项 #

• User-Agent可根据实际浏览器版本调整，避免被识别为爬虫
• 动态token的提取逻辑可能随Google Trends页面更新失效，需定期检查页面结构
• 避免短时间内频繁发起请求，否则仍可能触发429错误

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