在打开电子表格之间缓存自定义功能结果 [英] Cache custom function result between spreadsheet opens
问题描述
我有一个Google电子表格，该电子表格使用自定义功能加载了数据. 自定义函数调用REST API，转换数据并显示它.
I have a Google spreadsheet that has it's data loaded using a custom function. The custom function calls a REST API, transforms the data and displays it.
问题在于，每次我打开文档时，都会调用该函数，并且所有REST API调用都会消失.
The problem is that every time I open the doc, the function is called and all the REST API calls go out.
我需要能够手动触发服务调用以刷新数据，并且在这之间显示上一次的数据.
I need to be able to manually trigger service calls to refresh data, and in between show the data from last time.
我怎样才能最好地做到这一点?
How can I best achieve that?
推荐答案
简短答案
不是从公式(自定义函数)中调用函数，而是从以下位置中调用它:
Short answer
Instead of calling your function from a formula (custom function) call it from:
- 脚本编辑器
- 自定义菜单
这时，在打开电子表格及其任何参数更改其值时，将重新计算自定义函数.要运行该功能，请使用脚本编辑器的播放或调试按钮或自定义菜单.您也可以使用其他触发器.选择最适合您的需求.
At this time custom functions are recalculated when the spreadsheet is open and when any of its arguments changes its values. To run the function at will use the script editor play or debug buttons or a custom menu. Also you could use other triggers. Choose the one that best suit your needs.
如果您仍想缓存调用相应REST API的结果，请使用类缓存.
If you still want to cache the result of calling the corresponding REST API use the Class Cache.
- Overview of Google Apps Script
- Custom functions in Google Sheets
- Cache Service
这篇关于在打开电子表格之间缓存自定义功能结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
