UrlFetchApp请求在菜单功能中失败，但在自定义功能中失败(连接到外部REST API) [英] UrlFetchApp request fails in Menu Functions but not in Custom Functions (connecting to external REST API)

问题描述

我正在使用以下功能，使用Google Apps脚本连接到外部API(Binance)，以检索JSON数组(市价).这个简单的查询网址可以在浏览器中正常运行(不需要API密钥):

I am using the following function to connect to an external API (Binance) using a Google Apps Script to retrieve a JSON array (of market prices). This simple query url works fine in a browser (no need for API keys):

function getMyArray() {
  var url ="https://api.binance.com/api/v3/ticker/price"; // works perfectly in browser
  var params =  {"method"  : "get",  "muteHttpExceptions":true };  
  var response = UrlFetchApp.fetch(url, params);
  var array = JSON.parse(response.getContentText());
  
  return array;
}

但是，当我尝试在Google Apps脚本中运行该函数时，情况却有所不同:

However it's a different story when I try to run the function in Google Apps Script:

1. 脚本编辑器:可在脚本编辑器中轻松实用地运行，但出现403 error请求被阻止"
2. 菜单功能:从添加到电子表格UI =>的菜单项中调用该功能.相同的403 error
3. 自定义功能:编辑任意单元格，然后输入=getMyArray() =>请求有效，我可以使用Logger跟踪阵列

1. Script Editor: easy and practical to run within the script editor, but I get a 403 error "Request blocked"
2. Menu function: call the function from a menu item added to spreadsheet UI => same 403 error
3. Custom function: edit any cell and type =getMyArray() => request works and I can trace the array using Logger

为什么从菜单或脚本编辑器调用我的简单请求时会被阻止，并且可以更改它?谢谢

Why is my simple request getting blocked when called from Menu or Script Editor, and is it possile to change that? Thanks

推荐答案

自定义功能和脚本编辑器使用UrlFetchApp时，我认为区别在于是否使用IPv6，而IPv4的地址已更改每次运行.在这种情况下，脚本编辑器和自定义菜单的结果相同.我认为这可能是您遇到问题的原因.但是我不确定我的猜测是否正确.因此，在这个答案中，我想提出以下解决方法.

When UrlFetchApp is used by the custom function and the script editor, I think that the difference is whether IPv6 is used, while the address of IPv4 is changed every run. In this case, the results of the script editor and custom menu are the same. I thought that this might be the reason of your issue. But I'm not sure whether my guess is the correct. So, in this answer, I would like to propose the following workaround.

1. 使用脚本将公式=getMyArray()放置到单元格中.
   • 通过这种方式，将值检索到单元格中.

1. Put the formula =getMyArray() to a cell using the script.
   • By this, the value is retrieved to the cell.

通过这种流程，我认为您的目标可以实现.

By this flow, I think that your goal can be achieved.

示例脚本如下.

在该脚本中，作为测试，将=getMyArray()放在单元格"A1"中.在活动工作表上，然后从单元格中检索值.使用此功能时，请在脚本编辑器和自定义菜单上运行功能main().这样，可以将值检索到array.

In this script, as a test, =getMyArray() is put to the cell "A1" on the active sheet and the value is retrieved from the cell. When you use this, please run the function main() at the script editor and custom menu. By this, the value can be retrieved to array.

function getMyArray() {
  var url = "https://api.binance.com/api/v3/ticker/price";
  var params =  {"method": "get", "muteHttpExceptions": true};
  var response = UrlFetchApp.fetch(url, params);
  return response.getContentText();
}

// Please run this function by the script editor and the custom menu.
function main() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange("A1");
  range.setFormula("=getMyArray()");
  SpreadsheetApp.flush();
  var value = range.getValue();
  range.clearContent();
  var array = JSON.parse(value);
  console.log(array)
}

参考文献:

• Google表格中的自定义功能
• setFormula()
• flush()

References:

• Custom Functions in Google Sheets
• setFormula()
• flush()

https://httpbin.org/get的响应值如下.

function sample() {
  var url = "https://httpbin.org/get";
  var res = UrlFetchApp.fetch(url);
  console.log(res.getContentText())
  return res.getContentText();
}

结果:

模式1.脚本使用脚本编辑器运行.

Result:

Pattern 1. Script is run with the script editor.

{
  "args": {}, 
  "headers": {
    "Accept-Encoding": "gzip,deflate,br", 
    "Host": "httpbin.org", 
    "User-Agent": "Mozilla/5.0 (compatible; Google-Apps-Script; beanserver; +https://script.google.com; id: ###)", 
    "X-Amzn-Trace-Id": "Root=###"
  }, 
  "origin": "### IPV6 ###, ### IPV4 ###", // or "### IPV4 ###, ### IPV4 ###"
  "url": "https://httpbin.org/get"
}

• 使用IPV6时，origin是"### IPV6 ###, ### IPV4 ###".但是，当您使用IPV4时，origin是"### IPV4 ###, ### IPV4 ###".
• 在这种情况下，无法从https://api.binance.com/api/v3/ticker/price中检索正确的值.
• When you are using IPV6, origin is "### IPV6 ###, ### IPV4 ###". But when you are using IPV4, origin is "### IPV4 ###, ### IPV4 ###".
• In this case, the correct value cannot be retrieved from https://api.binance.com/api/v3/ticker/price.

在这种情况下，将=sample()放在单元格中并检索值.

In this case, =sample() is put to a cell and the value is retrieved.

{
  "args": {}, 
  "headers": {
    "Accept-Encoding": "gzip,deflate,br", 
    "Host": "httpbin.org", 
    "User-Agent": "Mozilla/5.0 (compatible; Google-Apps-Script; beanserver; +https://script.google.com; id: ###)", 
    "X-Amzn-Trace-Id": "Root=###"
  }, 
  "origin": "### IPV4 ###", 
  "url": "https://httpbin.org/get"
}

• 在这种情况下，可以从https://api.binance.com/api/v3/ticker/price中检索正确的值.
• In this case, the correct value can be retrieved from https://api.binance.com/api/v3/ticker/price.

当UrlFetchApp与自定义功能一起使用时，不需要授权.但是，当UrlFetchApp与OnEdit事件触发器一起使用时，授权需要安装触发器.我认为此授权可能会发生此问题.所以我比较了这个.

When UrlFetchApp is used with the custom function, no authorization is required. But when UrlFetchApp is used with the OnEdit event trigger, the installable trigger is required by authorizing. I thought that this authorization might occur this issue. So I compared this.

将UrlFetchApp与可安装的OnEdit事件触发器一起使用时，将检索以下结果.

When UrlFetchApp is used with the installable OnEdit event trigger, the following result is retrieved.

{
  "args": {}, 
  "headers": {
    "Accept-Encoding": "gzip,deflate,br", 
    "Host": "httpbin.org", 
    "User-Agent": "Mozilla/5.0 (compatible; Google-Apps-Script; beanserver; +https://script.google.com; id: ###)", 
    "X-Amzn-Trace-Id": "Root=###"
  }, 
  "origin": "### IPV4 ###", 
  "url": "https://httpbin.org/get"
}

• 此结果与上述模式2相同.
• 在这种情况下，可以从https://api.binance.com/api/v3/ticker/price中检索正确的值.
• This result is the same with above pattern 2.
• In this case, the correct value can be retrieved from https://api.binance.com/api/v3/ticker/price.
• 所有模式的标头都包括User-Agent.
• 从模式2和3来看，它与Google方面的授权无关.
• 当检索带有IPV4的WHOIS时，将返回相同的结果.
  • 当origin为"### IPV4 ###, ### IPV4 ###"时，第二个IPV4是Google的IP地址.
  • The headers including User-Agent are the same for all patterns.
  • From pattern 2 and 3, it is not related to the authorization for Google side.
  • When WHOIS with IPV4 is retrieved, the same result is returned.
    • When origin is "### IPV4 ###, ### IPV4 ###", 2nd IPV4 is the Google's IP address.

    从上述结果来看，所有模式的不同之处在于origin的值是1还是2.

    From above results, the different of all patterns is whether the value of origin is 1 or 2.

    这篇关于UrlFetchApp请求在菜单功能中失败，但在自定义功能中失败(连接到外部REST API)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！