connecting excel to the oauth api on azure

You can do OAuth 2.0 authentication on AzureAD from your client application such as Excel, Word, Powerpoint and Publisher. This works for opening entire files (Office 2016 or above) or import data from a webpage (Office 2019 / M365-apps).

The way you set up OAuth authentication will differ a bit from the regular and well documenten OAuth flow as you use it in your REST clients.

Instead of sending a header that forwards the client to{tenant}/oauth2/v2.0/authorize you need to return the http response code 401 (unauthorized) and add the next headers:

WWW-Authenticate: Bearer resource="" client_id="{the client id of your registered app in Azure AD}", trusted_issuers="[email protected]*", token_types="app_asserted_user_v1 service_asserted_app_v1", authorization_uri="{tenant}/oauth2/v2.0/authorize",Basic Realm=""'

In Azure AD in your app registration:

  • In API permissions:
    • Azure Active Directory Graph > User.Read In
  • Expose an API:
    • Add a scope: https://{fqdn of your api}/email (email or other attribute you use for authentication)
    • Authorized client application > Add a client application: d3590ed6-52b3-4102-aeff-aad2292ab01c (the ID of MS Office)

Note: all this is not documented at all. I found out about the headers by sniffing the traffic between Outlook and O365.

Btw: If you serve files using the webdav protocol: opening a file in Excel from a webpage works well with javascript:

location.href = "ms-excel:ofe|https://{yourAPI}/your_output.xlsx"

CLICK HERE to find out more related problems solutions.

Leave a Comment

Your email address will not be published.

Scroll to Top