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

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.