getOAuthToken returns null when called from Google Sheets

Question

I'm working on a way to limits some Google Sheets for specific users, with the oAuth specification and AWS API Gateway calls, but i'm facing a problem with the ScriptApp.getOAuthToken() function.

When i'm running the code with the Google Apps Script debugger, everything's fine, ScriptApp.getOAuthToken() returns me a token i can pass to my AWS API. The expected result for now is just to recieve the username. But if i try to use my function as a macro in a Google Sheets cell, i have the following error Header:null (line 13)

Here is the code in the Code.gs file

function HelloW() {
  var token = ScriptApp.getOAuthToken();
  var headers = {
    'Authorization' : token
  }

  var options = {
    'headers' : headers,
    'method' : 'post',
    'contentType': 'application/json',
    'payload' : JSON.stringify(data)
  };

  var response = UrlFetchApp.fetch('https://###/demo-lambda', options);

  var txt = response.getContentText();
  var json = JSON.parse(txt);
  var name = json.Message;

  return name;
}

And the manifest just in case

{
  "timeZone": "Europe/Paris",
  "dependencies": {
  },
  "exceptionLogging": "STACKDRIVER",
  "oauthScopes": ["https://www.googleapis.com/auth/script.external_request", 
"https://www.googleapis.com/auth/spreadsheets", 
"https://www.googleapis.com/auth/userinfo.email",
"https://www.googleapis.com/auth/drive"],
  "sheets": {
    "macros": [{
      "menuName": "HelloW",
      "functionName": "HelloW"
    }]
  }
}

I've got an error because token is null, but i don't understand why it runs well with the debugger, and it doesn't in the Sheets document. I'm missing something and i don't find what.

Any help would be much appreciated.

Answer 1

You cannot make calls inside macros that require user authorization.

Unlike most other types of Apps Scripts, custom functions never ask users to authorize access to personal data. Consequently, they can only call services that do not have access to personal data.

Source