How to assign UrlFetchApp with basic authorization to button?

Question

I created function in Google Apps Script, that works well when I run it in Google Apps Script. Output data returns to Google Sheets.

function testFunction11() {
  var rng = SpreadsheetApp.getActiveRange();
  var encodedAuthInformation = Utilities.base64Encode("username:key");
  var headers = {"Authorization" : "Basic " + encodedAuthInformation};
  var params = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': headers
  };
  var res = UrlFetchApp.fetch("https://api.apiservice.com/api/v1/xxx?fields=somefields", params);
  Logger.log(res.getContentText());
  rng.setValue(res);
}

Output in cell:

[
  {
    "id": xxx,
    "createdDate": "2019-02-01T04:54:00Z",
    "reference": "XXX"
  },
etc

Then I assign script to button, 'testFunction11'. And when I click button, it returns

{
  "message": "An error has occurred."
}

It looks like response from API server.

My only hypothesis is that google sheet's button adds some headers, User-Agent or content-type to request, which not allowed in API server. And after some search, I guess I can't reassign User-Agent in request. Is that something right or I do it wrong?

EDIT 1:

Headers for each case console.log(UrlFetchApp.getRequest(url, params)): When clicking button in spreadsheet:

{headers={Authorization=Basic XXXXXXXXQVU6MWVhODlmZmFkN2U3NGNjOGJkOTc1YTE1ZjVhNTE3MzE=, X-Forwarded-For=178.xx.my.ip}, method=get, payload=, followRedirects=true, validateHttpsCertificates=true, useIntranet=false, contentType=null, url=https://api.apisite.com/api/v1/SalesOrders?fields=Id,Createddate,Reference&where=Createddate%3E2019-02-01T00:00:00Z}

And for script:

{headers={Authorization=Basic XXXXXXXXQVU6MWVhODlmZmFkN2U3NGNjOGJkOTc1YTE1ZjVhNTE3MzE=}, method=get, payload=, followRedirects=true, validateHttpsCertificates=true, useIntranet=false, contentType=null, url=https://api.apisite.com/api/v1/SalesOrders?fields=Id,Createddate,Reference&where=Createddate%3E2019-02-01T00:00:00Z}

So the button only adds X-Forwarded-For.

When I tried manually add X-Forwarded-For: 'unknown' there are error like this

There are attribute with impossible value: Header:X-Forwarded-For

Text of error in russian, so sorry for maybe not accurate translating. It's fun, because when I added Test: unknown in same way, there are no error, but obviously not working. Looks like google don't allow to change this value.

Will try different headers in postman and maybe confirm that this header is the cause of the error. Thank you @TheMaster

EDIT 2:

I tried different headers via Postman. So, result is when I add to headers X-Forwarded-For key with any value, it return "message": "An error has occurred." When I don't add this key, it works well.

So, the question is any way to disable adding this header via Google Apps Script. It seems like not.

Answer 1

• In your situation, when UrlFetchApp.fetch() is run from a button on Spreadsheet, X-Forwarded-For is automatically added to the header.
• By added X-Forwarded-For to the header, the error of An error has occurred. occurs.
• On the other hand, X-Forwarded-For is not used in the header, no error occurs.

If my understanding is correct, how about this workaround? I think that there might be several workarounds. So please think of this as just one of them. In this workaround, Web Apps is used as a wrapper function.

Sample script:

At first, please copy and paste the following script. And please set testFunction11() to the button. When testFunction11() is run, testFunction11() requests to Web Apps (doGet()), and Web Apps requests to https://api.apiservice.com/api/v1/xxx?fields=somefields. By this, X-Forwarded-For is not used to the header of request. Then, the result of Web Apps is returned and put the value to the spreadsheet. Please deploy Web Apps, before you run the script.

function testFunction11() {
  var rng = SpreadsheetApp.getActiveRange();
  var url = ScriptApp.getService().getUrl();
  var params = {method: "get", headers: {Authorization: "Bearer " + ScriptApp.getOAuthToken()}};
  var res = UrlFetchApp.fetch(url, params);
  Logger.log(res.getContentText());
  rng.setValue(res);
}

function doGet() {
  var url = "https://api.apiservice.com/api/v1/xxx?fields=somefields";
  var encodedAuthInformation = Utilities.base64Encode("username:key");
  var headers = {"Authorization" : "Basic " + encodedAuthInformation};
  var params = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': headers
  };
  var res = UrlFetchApp.fetch(url, params);
  return ContentService.createTextOutput(res.getContentText());
}

Deploy Web Apps:

Before you run this script, please deploy Web Apps.

• On the Script Editor
• Publish -> Deploy as Web App
  1. Create new Project version
  2. At "Execute the app a"s, select "Me"
  3. At "Who has access to the app",
     • if the function is used by only you, select "Only myself".
     • if the function is used by several users, select "Anyone".
  4. Click "Deploy"
  5. Copy "Current web app URL"
  6. Click "OK"

Note:

• When you modified the script, please redeploy Web Apps as new version. By this, the latest script is reflected to Web Apps. This is an important point.
• This is a simple sample script. So please modify to your situation.

References:

• Web Apps
• Taking advantage of Web Apps with Google Apps Script

Answer 2

For future searches on this topic: I had an issue with the exact same error message, where I was using UrlFetchApp#getRequest as a utility to generate a request object, then sending it with UrlFetchApp#fetchAll.

The solution was to avoid using getRequest and instead build the request by myself without using any X-Forwarded-For header, which allowed the request to complete successfully.

I can't find any documentation to support this, but I would speculate that the following issue occurred: when Google injects the X-Forwarded-For header before making a request, the IP it injects depends on the context the script gets executed in. I think Google validates that if that header is set manually, then the IP address needs to match either their GAS proxy's IP or the user's browser IP; which one to use seems to change depending on the context the script is executed in.