Google Apps Script触发器调用API无法获取实时数据问题排查

使用Google Apps Script设置定时触发器，调用多个WordPress站点的API跟踪插件版本，同时记录「最后更新」时间查看API调用时机。但触发器运行时始终返回首次调用的数据，即使增删插件也无变化，函数无报错但无法获取实时数据。疑问：是Google Script缓存了数据，还是API调用存在限制？

function teste()
{
  var all_data = getDataFromPluginList()
  /*return
  [ { first_cell: 'A2',
    last_cell: 'I8',
    first_row: 2,
    last_row: 8,
    total_row: 7,
    url: 'https://example1.wpengine.com/' },
  { first_cell: 'A10',
    last_cell: 'I15',
    first_row: 9,
    last_row: 14,
    total_row: 6,
    url: 'https://example2.wpengine.com/' } ]

  */

  for (var i = 0; i < all_data.length; i++) {
    trigger_(all_data[i].url, all_data[i].first_row, all_data[i].last_row, all_data[i].total_row);
  }
}

function trigger_(url, first_row, last_row, total_row)
{
    var new_trigger = ScriptApp.newTrigger("writePluginData").timeBased().everyMinutes(1).create();
    var trigger_id = new_trigger.getUniqueId();
    PropertiesService.getUserProperties().setProperty(trigger_id, url+","+first_row+","+last_row+","+ total_row);
}

function writePluginData(event) 
{

  var trig = ScriptApp.getProjectTriggers();
  let sheet_name = 'trigger_result';
  let lowest_row = lowestRow(sheet_name);
  for(var i =0; i<trig.length; i++)
  {
   if(trig[i].getUniqueId()== event.triggerUid )
   {
    var urlParam = PropertiesService.getUserProperties().getProperty(event.triggerUid);
    urlParam = urlParam.split(",");
    var url = urlParam[0];
    var first_row = urlParam[1];
    var last_row = urlParam[2];
    var total_row = urlParam[3];
    var new_first_row = 0;
    var ss = SpreadsheetApp.getActive().getSheetByName(sheet_name); // change accordingly
    var data = ListPlugins(url);
    for (var i = 1; i < data.length; i++) {
      let empty = ''
      data[i].push(empty);
    }

    if(total_row != data[0][7]){
      ss.deleteRows(first_row, first_row);
      ss.getRange(lowest_row + 1 , 1,data[0][7],9).setValues(data);
    } else {
      ss.getRange(first_row, 1,total_row,9).setValues(data);
    }
   }
  }
}

function ListPlugins(url) {
  let sheet_name = 'apps and plugins'

  var options = {
     "async": true,
     "crossDomain": true,
     "method" : "GET",
     "headers" : {
       "Token" : TOKEN_HERE,
       "cache-control": "no-cache"
     }
   };
  var urlEncoded = encodeURI(url)
  let api_url = urlEncoded + "/wp-json/bd/v1/public/plugin-list/"
  var jsondata = UrlFetchApp.fetch(api_url,options)
  var object   = JSON.parse(jsondata.getContentText())

  if(object){
    return object;
  } else {
    return "not found"
  }
}

1. UrlFetchApp缓存导致数据不更新 #

Google Apps Script的UrlFetchApp默认会缓存GET请求结果，即使请求头设置了cache-control: no-cache，也可能被GAS内部缓存机制覆盖，导致重复返回首次请求的数据。

修复方案：

• 在API请求URL后添加随机时间戳参数，强制绕过缓存：

  let api_url = urlEncoded + "/wp-json/bd/v1/public/plugin-list/?t=" + new Date().getTime();
  

• 或者在请求配置中明确禁用GAS缓存：

  var options = {
     "async": true,
     "crossDomain": true,
     "method" : "GET",
     "headers" : {
       "Token" : TOKEN_HERE,
       "cache-control": "no-cache"
     },
     "cache": false // 禁用GAS内置缓存
   };
  

2. 触发器重复创建+存储值未更新 #

• 每次运行teste()都会创建新的定时触发器，多个触发器重复执行可能导致数据覆盖或逻辑冲突。
• PropertiesService中存储的total_row是初始值，插件数量变化后未更新，导致数据更新判断逻辑失效。

修复方案：

• 创建新触发器前先清理旧触发器，避免重复：

  function trigger_(url, first_row, last_row, total_row)
  {
      // 删除所有指向writePluginData的旧触发器
      var existingTriggers = ScriptApp.getProjectTriggers();
      for (var t = 0; t < existingTriggers.length; t++) {
          if (existingTriggers[t].getHandlerFunction() === "writePluginData") {
              ScriptApp.deleteTrigger(existingTriggers[t]);
          }
      }
      var new_trigger = ScriptApp.newTrigger("writePluginData").timeBased().everyMinutes(1).create();
      var trigger_id = new_trigger.getUniqueId();
      PropertiesService.getUserProperties().setProperty(trigger_id, url+","+first_row+","+last_row+","+ total_row);
  }
  

• 插件数量变化时，同步更新PropertiesService中的存储值：

  if(total_row != data[0][7]){
    // 修正删除行数逻辑：第二个参数是删除的行数，不是结束行
    ss.deleteRows(parseInt(first_row), parseInt(total_row));
    ss.getRange(parseInt(first_row), 1, data[0][7],9).setValues(data);
    // 更新PropertiesService中的最新行数参数
    PropertiesService.getUserProperties().setProperty(event.triggerUid, url+","+first_row+","+(parseInt(first_row)+data[0][7]-1)+","+ data[0][7]);
  } else {
    ss.getRange(parseInt(first_row), 1, parseInt(total_row),9).setValues(data);
  }
  

3. 循环变量冲突导致逻辑异常 #

writePluginData函数中，外层循环使用var i，内层循环也使用var i，导致变量覆盖，逻辑执行异常。

修复方案：
将内层循环变量改为j：

for (var j = 1; j < data.length; j++) {
  let empty = ''
  data[j].push(empty);
}

4. 其他细节修正 #

• 从PropertiesService取出的参数都是字符串，需要转为整数再使用，避免类型判断错误
• 原代码中deleteRows(first_row, first_row)逻辑错误，第二个参数应为删除的行数，不是起始行号

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