Search code examples
exceloauthpowerquerymyob

3-Legged OAuth2 Authorization code flow for Excel?

This might be a long shot, but I'm using the MYOB Business API which has the same OAuth authorization flow as the LinkedIn API - 3-legged - and am planning on making queries to it through Excel, most likely power query.

The big issue with this is obviously dealing with the sending and receiving of the code=, access_token and refresh_token. Most of the searches I've done relating to using Power Query and OAuth seem to only deal with username and password OAuth and not the authorization flow, shown here https://developer.indeed.com/docs/authorization/3-legged-oauth.

I'm curious if there is Power Query M Language code out there that I haven't come across or if I have to go the route of building an OData Feed (or another method?) on top of the API so that it can be queried in Excel?

Any help or further direction would be appreciated.

I should also note they have a website containing their Postman collection using different languages here; https://accountrightapi.myob.cloud/?version=latest#f2b93dfa-803f-44b9-8a07-756874494227

Solution

  • The short answer for Excel is: No, but Asterisk.

    • Excel Power Query doesn't support custom connectors (as of 2022-02-07)
    • To securely use Auth2 flow, it requires a custom connector
    • There is some built in support, if the connector supports it. ( Like Web or OData)

    Here's a summarized version of Chris Web. (He's a Power Query Wizard) Connecting To REST APIs With OAuth2 Authentication In Power Query/Power BI @ blog.crossjoin.co.uk

    the definitive, Microsoft-endorsed answer to this question is: If want to connect from Power BI to a REST API that uses OAuth2 authentication then you need to build a custom connector.

    You can use OAuth2 credential flow in regular Power Query queries without needing a custom connector.

    this is not recommended: it’s not secure and it’s not reliable. In particular, hard-coding usernames/passwords or client ids/client secrets in your M code is a really bad idea

    The only exception is that you can connect to some APIs that use AAD authentication using the built-in web or OData connectors