Add a JSON data connection with bearer token authentication

Hello,



We have a API implemented and it works as follows-

POST request to authentication endpoint
http: //localhost:5698/api/services/app/account/authenticate
with JSON payload as
{ "usernameOrEmailAddress": "admin", "password": "123qwe" }

It returns a token which must be used in headers for all further requests. The format of response is as follows-
{ "result": "RETRIEVED_TOKEN", "success": true, "error": null, "unAuthorizedRequest": false }

We have an endpoint to get a list of all users.
POST request to endopoint
http: //localhost:5698/api/services/app/user/GetAllUsers
With following headers
Authorization- Bearer RETRIEVED_TOKEN
Content-Type- application/json
This returns the JSON results as




{

"result": {

"items": [

{

"name": "admin",

"surname": "admin",

"lastLoginTime": "2018-12-10T03:01:19.577Z",

"isActive": true,

"creationTime": "2018-10-08T10:50:00.557Z",

"id": 2

},

{

"name": "sso",

"surname": "sso",

"lastLoginTime": "2018-10-09T16:42:27.523Z",

"isActive": true,

"creationTime": "2018-10-09T05:53:10.697Z",

"id": 15

}

]

},

"success": true,

"error": null,

"unAuthorizedRequest": false,

}



Question:



In Excel, I want to create a data connection that will authenticate to this API, retrieve token and use that token to make further request to get all data. That data should be populated as a table.
Very Important: Users should be able to use Excel's Refresh button to get latest data from service.



It would be a plus if we can prompt user to enter credentials if the authentication fails or somehow allow a user to enter credentials at some place.



Thanks.

Which Excel refresh button? Do you mean right clicking on a table and refresh?

Which version of Excel will your users have? Will they be running 64-bit office (not windows)? Do they have Get and Transform?

Is this windows only or cross platform?

Originally Posted by Kyle123

Which Excel refresh button? Do you mean right clicking on a table and refresh?

Yes.

Originally Posted by Kyle123

Which version of Excel will your users have? Will they be running 64-bit office (not windows)? Do they have Get and Transform?

Users have 2013 and 2016 64-bit office.
Get and Transform - yes

Originally Posted by Kyle123

Is this windows only or cross platform?

Windows only

Get & transform wasn’t in 2013 are you saying that they have downloaded and installed the power query addin?

Bearer tokens typically expire, how are you wanting to deal with that?

Originally Posted by Kyle123

Get & transform wasn’t in 2013 are you saying that they have downloaded and installed the power query addin?

Bearer tokens typically expire, how are you wanting to deal with that?

We can have them install power query if that is the solution.
Is there anyway to prompt users for credentials if token expired and then regenerate Bearer token?