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
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:The short answer for Excel is: No, but Asterisk.
Excel Power Querydoesn’t support
custom connectors(as of
- To securely use
Auth2 flow, it requires a
- 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 connectorin the
Power BI Service, it requires an
How to implement
Creating a Custom Connector
OAuth2flow using with
Azure Active Directoryusing the
If you have better answer, please add a comment about this, thank you!