刷新Google表格中自定义功能检索的数据 [英] Refresh data retrieved by a custom function in Google Sheet
问题描述
我编写了自定义的Google Apps脚本,该脚本将收到 id
并从Web服务获取信息(价格)。
我在电子表格中使用这个脚本,它工作得很好。我的问题是,这些价格变化,我的电子表格并没有得到更新。
我如何强制它重新运行脚本并更新单元格翻过每个单元格)?
好的,我的问题似乎是谷歌以一种奇怪的方式行事 - 只要脚本参数相似,就重新运行脚本,它使用先前运行的缓存结果。因此,它不会重新连接到API并且不会重新获取价格,它只会返回缓存的前一个脚本结果。
查看更多信息此处: https://code.google.com / p / google-apps-script-issues / issues / detail?id = 888
和here:总结数据未更新的脚本
我的解决方案是将另一个参数添加到我的脚本中,我甚至不使用它。现在,当您使用与以前的调用不同的参数调用函数时,它将不得不重新运行脚本,因为这些参数的结果将不在缓存中。
<因此,无论何时我调用函数,为额外的参数传递$ A $ 1。
我也创建了一个名为refresh的菜单项,当我运行它时,它将当前日期和时间放入A1中,因此所有对$ A $ 1作为第二个参数的脚本调用都必须重新计算。下面是我的脚本中的一些代码:
function onOpen(){
var sheet = SpreadsheetApp.getActiveSpreadsheet();
var entries = [{
name:Refresh,
functionName:refreshLastUpdate
}];
sheet.addMenu(Refresh,entries);
};
函数refreshLastUpdate(){
SpreadsheetApp.getActiveSpreadsheet()。getRange('A1')。setValue(new Date()。toTimeString());
$ b函数getPrice(itemId,datetime){
var header =
{
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 +')');
返回jsonObj.Price;
SpreadsheetApp.flush();
}
当我想将ID为5的项目的价格放入单元格,我使用以下公式:
= getPrice(5,$ A $ 1)
当我想刷新价格时,只需点击刷新 - >刷新菜单项。
请记住,在更改 onOpen()
脚本后,您需要重新加载电子表格。
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)?
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: https://code.google.com/p/google-apps-script-issues/issues/detail?id=888
and here: Script to summarise data not updating
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.
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();
}
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)
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.
这篇关于刷新Google表格中自定义功能检索的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!