Add a JSON data connection with bearer token authentication RRS feed

  • Question

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





    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 as well.


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



    Monday, December 10, 2018 6:26 PM

All replies

  • Hi there,

    You could refer to the following link:

    Download data to Excel using VBA and the API (workbook with code attached

    Hopefully it helps you.

    Best Regards,


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread.

    Tuesday, December 11, 2018 7:20 AM
  • Hey Bruce,

    Thanks for your timely response.

    I already implemented something like that using VBA code but we do not want to take that path. However, we would like to somehow be able to use Excel's native functionality and Refresh button.

    We also have Odata service available for same API which work perfectly if we do not have authentication in place.

    With authentication, I just cannot figure out how to make it work. 

    Tuesday, December 11, 2018 6:10 PM