google-apps-script - 停止自定义函数自动刷新/定期调用外部 API
问题描述
我正在使用 Google Apps 脚本和自定义函数来调用外部 API 来验证电话号码。
下面是我的函数的代码。
/**
* This CUSTOM FUNCTION uses the numVerify API to validate
* a phone number based on the input from JotForm and a
* country code which is derived from the JotForm country
*
* Numverify website: https://numverify.com/dashboard (account via LastPass)
* Numverify docs: https://numverify.com/documentation
*/
function PHONE_CHECK(number, country){
if(country == "")
return [["", "country_not_set"]]
// check the API result has already been retrieved
var range = SpreadsheetApp.getActiveSheet().getActiveRange()
var apires = range.offset(0, 1).getValue()
if(apires.length > 0)
return range.offset(0, 0, 1, 2).getValues()
var url = 'http://apilayer.net/api/validate'
+ '?access_key=' + NUMVERIFY_KEY
+ '&number=' + encodeURIComponent(number)
+ '&country_code=' + encodeURIComponent(country)
+ '&format=1';
var response = UrlFetchApp.fetch(url, {'muteHttpExceptions': true});
var json = response.getContentText();
var data = JSON.parse(json);
if(data.valid !== undefined){
if(data.valid){
return [[data.international_format, "OK"]]
}else{
return [["", "invalid_number"]] // overflows data to the next column (API Error) while keeping the phone field clear for import into TL
}
}else if(data.success !== undefined){
if(data.error.type.length > 0){
return [[number, data.error.type]]
}else{
return [[number, "no_error_type"]]
}
}else{
return [[number, "unexpected_error"]] // this generally shouldn't happen...
}
}
给定这个公式,它需要一个电话号码和国家代码,然后它将根据 numverify API 检查电话号码,并在单元格中返回结果并溢出到其右侧的单元格。溢出用于指示 API 是否被成功调用并检查结果是否已经被检索。
例子:
=PHONE_CHECK("+32123456789", "BE")
请注意,第一个单元格是空的,因为 API 返回一个“无效的电话号码”代码。由于隐私原因,我不会在这里放任何真实的电话号码。如果我使用的是真实电话号码,第一个单元格将包含以国际号码格式格式化的电话号码。
由于我使用的是免费计划,如果我已经知道结果是什么,我不想每次都重新运行该函数,因为我不想违反速率限制。不幸的是,这似乎不起作用并且定期(看起来每天一次),它将刷新工作表中每一行的结果。
所以两个问题:
- 我在检查 API 结果然后退出函数时的逻辑有问题吗?(代码见下文)
- 如果逻辑正确,为什么 Google 表格似乎会定期忽略(或刷新?)第二列中的值并无论如何调用外部 API?
var range = SpreadsheetApp.getActiveSheet().getActiveRange() // get the cell from which the function is called
var apires = range.offset(0, 1).getValue() // get the values directly to the right of the cell
if(apires.length > 0) // check if there's anything there...
return range.offset(0, 0, 1, 2).getValues() // return an array that basically just resets the same values, effectively stopping the script from running
解决方案
你的目标:
你想要一个自定义函数,也就是一个公式,只运行一次,或者产生特定结果所需的多次。您希望相同的公式将值写入另一个单元格,例如相邻的单元格,这将告诉公式将来是否应该再次运行。
简短的回答:
恐怕从自定义函数 AKA 公式评估的值是瞬态的,你想要完成的事情是不可能的。
解释:
您可以使用此自定义函数运行快速测试:
function arrayTest() {
return [[1, 2, 3, 4 ,5]]
}
如果你把它放在一个单元格中,如下所示:
您会看到,如果您删除原始单元格中的公式,溢出值也会消失。
因此,类似以下代码的代码几乎总是会产生相同的值:
function checkTest() {
var cell = SpreadsheetApp.getActiveRange()
var status = cell.offset(0, 1).getValue();
if (status != "") {
return "already executed" // in your case without calling API
} else {
return [["OK","executed"]] // in your case making API call - will happen ~90% of the time.
}
}
// OUTPUT [["OK","executed"]]
在这里,我插入一行并将其删除以强制重新计算公式。
在重新计算公式之前,Sheets 所做的第一件事是清除以前由公式填充的值。由于条件语句取决于其先前执行的值,因此它将始终评估为相同的结果。在您的情况下,它几乎总是会进行 API 调用。
令人困惑的是,这不是 100% 可靠的!您会发现,有时它会按您的意愿工作。虽然在我的测试中,这种情况只发生了 10 次左右,而且最常见的是在将更改保存到脚本编辑器时更新公式时。
理想情况下,虽然不可能,但您希望能够编写如下内容:
function checkTest() {
var cell = SpreadsheetApp.getActiveRange();
var cellValue = cell.getValue();
var adjacentCell = cell.offset(0, 1);
var status = adjacentCell.getValue();
if (status == "") {
cell.setValue(cellValue)
adjacentCell.setValue("executed")
}
}
setValue()
唉,公式一旦运行就会清除公式!如果您想使用setValue()
,您需要从菜单、触发器或脚本编辑器运行您的脚本。在这种情况下,它作为公式将不再有意义。z
参考
https://developers.google.com/apps-script/guides/sheets/functions
推荐阅读
- c# - 我可以避免将 TreeViewItem 的内容放在 WPF 窗口的左上角吗?
- css - 尝试使用隐藏属性为显示/隐藏定义列表设置动画(隐藏属性导致动画不起作用)
- javascript - 服务器端渲染不渲染 UI 库
- html - CSS如何使包装器元素包含2个它的大小元素,1个离屏
- hash - 哈希表中不同Key值插入序列的数量
- elasticsearch - 用于登录 Kubernetes 的 ElasticSearch 无法工作
- http - 关于 http 标头中大小限制的一个奇怪问题
- azure-active-directory - 天蓝色活动目录应用程序注册显示错误的发布者域
- excel - Excel-VBA 从文本文件导入模块(无需信任中心)
- ssis - SSIS 2008R2 禁用在设计时从运行时存储回来的属性