Consume python data api in excel based on Azure AD login

Question

Consume python data api in excel based on Azure AD login

Rashbir Kohli 0

Hey team,

I am trying to consume a Python-based API in Excel.

Just a little background, the Pyhton-based API is connecting to Postgress to read data and perform some transformations and then returning the dataframe in JSON format that we are consuming in Excel.

Now the main question is that we want to perform authentication based on organization and AD group in the API.

Things that we have already tried or can't use:

Using Graph API(As this requires us to use web browsers so failed)
Can not pass username/Password/Token as header.

So is it possible for us to authenticate and prompt users to enter their username/password and then return the authenticated username back to API in Python?

I saw in Excel there is an option for us to perform organizational login.

Mike Urnun 9,792 Reputation points Moderator

2023-08-07T04:56:25.46+00:00

Hello, @Rashbir Kohli - Thanks for reaching out! Per my understanding, you have a Python-based API that requires authentication, and you want to perform both authentication and consumption of its data in Excel. Could you confirm if my understanding is correct?
Deleted

This comment has been deleted due to a violation of our Code of Conduct. The comment was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.
Rashbir Kohli 0 Reputation points

2023-08-13T20:11:35.22+00:00

Hey @MikeUrnun yes. The python API is simply returning the DataFrame in a JSON format that I am reading using Get Data -> From Other Sources -> From Web from excel. But I am looking for a way to authenticate the API (Currently no authentication in place). As excel don't allow us to save cache or enter/authenticate using web so I am looking for way to authenticate that excel allow, also I can not use any power query as the consumer of the API are not at all tech savvy.
Pauline Mbabu 1,835 Reputation points Microsoft Employee

2023-08-16T18:51:57.73+00:00

Hi, @Rashbir Kohli - Where is the excel stored? Is it on One Drive or Sharepoint? I would also like to understand where the users are, Are they AAD Users?
Also, on the concerns you highlighted above -i.e. Things that we have already tried or can't use: Try and use the Client Credentials flow instead https://learn.microsoft.com/en-us/azure/active-directory/develop/v2-oauth2-client-creds-grant-flow. You will not require to use a web browser or pass the username/password as header
Rashbir Kohli 0 Reputation points

2023-10-29T18:28:07.7133333+00:00

Hey Paulin, the Excel is just running on Windows. I have data stored in postgress and data is getting returned from a python based APIs. This APIs is then getting consumed as Jason from excel.

Now what I am after is be able to log in using Azure company email user name and password into APIs and return data based on the user account.