Home

Add a JSON data connection with bearer token authentication

kpsingh
Occasional Visitor

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
    AuthorizationBearer RETRIEVED_TOKEN
    Content-Typeapplication/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. Users should be able to refresh data source as well.

 

I would be a plus if we can prompt user to enter credentials if the authentication fails.

 

Thanks.