javascript - UrlFetchApp request fails in Menu Functions but not in Custom Functions (connecting to external REST API)

Question

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;
}

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

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

Answer 1

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. Put the formula =getMyArray() to a cell using the script.
   • By this, the value is retrieved to the cell.
2. Retrieve the values from the cell using the script.
3. Clear the put formula.

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

The sample script is as follows.

Sample script:

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)
}

References:

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

Added:

The response value from https://httpbin.org/get is as follows.

Sample script for testing this:

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

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"
}

• 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.

Pattern 2. Script is run with the custom function.

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"
}

• In this case, the correct value can be retrieved from https://api.binance.com/api/v3/ticker/price.

Pattern 3. Script is run with the OnEdit event trigger.

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.

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"
}

• 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.

Result:

• 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.

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