3-Legged OAuth2 Authorization code flow for Excel?

Question

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

Answer 1

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

• To use a custom connector in the Power BI Service, it requires an on-premises gateway

• How to implement OAuth2 flow

• Creating a Custom Connector

• Using built-in OAuth2 flow using with Azure Active Directory using the Web and OData connectors

Answer 2

I used excel VBA code to handle the MYOB API connection by invoking MS Edge, with Seliumn Basic drivers.

VBA then executes the MYOB API Connector Add-On.

VBA receives the database files I require from MYOB and writes them to a.txt file location on my PC.

VBA then refreshes the data connections for Power Query which runs the M Code I’ve written in advanced editor.

When I close excel, VBA closes all the connections and MYOB add on connector.