您的位置: 首页  >  技术问答  >  刷新由 Google 表格中的自定义函数检索的数据

刷新由 Google 表格中的自定义函数检索的数据

分类: 技术问答 • 2022-04-15 22:46:34
问题描述：

我编写了一个自定义 Google Apps 脚本，该脚本将接收 id 并从网络服务中获取信息(价格).

I've written a custom Google Apps Script that will receive an id and fetch information from a web service (a price).

我在电子表格中使用了这个脚本，它工作得很好.我的问题是这些价格发生了变化，而我的电子表格没有更新.

I use this script in a spreadsheet, and it works just fine. My problem is that these prices change, and my spreadsheet doesn't get updated.

如何强制它重新运行脚本并更新单元格(无需手动遍历每个单元格)?

How can I force it to re-run the script and update the cells (without manually going over each cell)?

答

好吧，看来我的问题是 google 的行为方式很奇怪 - 只要脚本参数相似，它就不会重新运行脚本，它使用之前运行的缓存结果.因此，它不会重新连接到 API，也不会重新获取价格，它只是返回缓存的先前脚本结果.

Ok, it seems like my problem was that google behaves in a weird way - it doesn't re-run the script as long as the script parameters are similar, it uses cached results from the previous runs. Hence it doesn't re-connect to the API and doesn't re-fetch the price, it simply returns the previous script result that was cached.

在此处查看更多信息(如果您受到影响，请为这些问题加星):

See more info here(Add a star to these issues, if you're affected):

在这里:总结未更新数据的脚本

我的解决方案是在我的脚本中添加另一个参数，我什至不使用它.现在，当您使用与之前调用不同的参数调用函数时，它必须重新运行脚本，因为这些参数的结果不会在缓存中.

My solution was to add another parameter to my script, which I don't even use. Now, when you call the function with a parameter that is different than previous calls, it will have to rerun the script because the result for these parameters will not be in the cache.

因此，每当我调用该函数时，对于额外的参数，我都会传递$A$1".我还创建了一个名为 refresh 的菜单项，当我运行它时，它将当前日期和时间放在 A1 中，因此所有以 $A$1 作为第二个参数的脚本调用都必须重新计算.这是我的脚本中的一些代码:

So whenever I call the function, for the extra parameter I pass "$A$1". I also created a menu item called refresh, and when I run it, it puts the current date and time in A1, hence all the calls to the script with $A$1 as second parameter will have to recalculate. Here's some code from my script:

function onOpen() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [{
    name : "Refresh",
    functionName : "refreshLastUpdate"
  }];
  sheet.addMenu("Refresh", entries);
};

function refreshLastUpdate() {
  SpreadsheetApp.getActiveSpreadsheet().getRange('A1').setValue(new Date().toTimeString());
}

function getPrice(itemId, datetime) {
  var headers =
      {
        "method" : "get",
        "contentType" : "application/json",
        headers : {'Cache-Control' : 'max-age=0'}
      };

  var jsonResponse = UrlFetchApp.fetch("http://someURL?item_id=" + itemId, headers);
  var jsonObj = eval( '(' + jsonResponse + ')' );
  return jsonObj.Price;
  SpreadsheetApp.flush();
}

当我想将 ID 为 5 的商品的价格放入单元格时，我使用以下公式:

And when I want to put the price of item with ID 5 in a cell, I use the following formula:

=getPrice(5, $A$1)

当我想刷新价格时，只需单击刷新"按钮即可.->刷新"菜单项.请记住，您需要在更改 onOpen() 脚本后重新加载电子表格.

When I want to refresh the prices, I simply click the "Refresh" -> "Refresh" menu item. Remember that you need to reload the spreadsheet after you change the onOpen() script.

相关推荐