Add a JSON data connection with bearer token authentication

Occasional Visitor



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
  • 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,





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.



Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies