Help with API for Newbie

Authentication
1

Hi, I am struggling to use the FreeAgent API to extract data. CoPilot has been helpful in getting me so far but I still can’t make it work. Can someone help me understand what I am doing wrong please?
Here is my Power Query code with the security bits removed

let
// Define your credentials and endpoints
clientId = “xxxxxHIDDENxxxxx”,
clientSecret = “xxxxxHIDDENxxxxx”,
authorizationCode = “xxxxxHIDDENxxxxx”,
tokenEndpoint = “https://api.freeagent.com/v2/token_endpoint”,
invoicesEndpoint = “https://api.freeagent.com/v2/invoices”,
// Step 1: Get the access token
tokenResponse = Json.Document(
Web.Contents(tokenEndpoint,
[
Headers = [#“Content-Type” = “application/x-www-form-urlencoded”],
Content = Text.ToBinary(“grant_type=authorization_code&client_id=” & clientId & “&client_secret=” & clientSecret & “&code=” & authorizationCode)
]
)
),
accessToken = tokenResponse[access_token],
// Step 2: Fetch invoice details
invoiceResponse = Json.Document(
Web.Contents(invoicesEndpoint,
[
Headers = [#“Authorization” = "Bearer " & accessToken, #“Accept” = “application/json”]
]
)
),
// Convert JSON response to table
invoicesTable = Table.FromList(invoiceResponse[invoices], Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
invoicesTable

The error message I get is:
DataSource.Error: Web.Contents failed to get contents from ‘https://api.freeagent.com/v2/token_endpoint’ (400): Bad Request
Details:
DataSourceKind=Web
DataSourcePath=https://api.freeagent.com/v2/token_endpoint
Url=https://api.freeagent.com/v2/token_endpoint

Thank you

2

Hi Bruce.

When you authorise on the authorization endpoint, you are given an authorization code.
You use that authorization code against the token endpoint to get an access token.
That access token is then added to the Header for all requests to the endpoints.

I don’t use Power Query so can’t help you with code.

But I found this really useful and it’s not really difficult to set up, so maybe invest a few moments trying it?

Go the section here called Generate Access and Refresh Tokens and try it.

Good luck

3

To be clearer:

  • Go the authorization endpoint and get an authorization code
  • Use that code to go to the token endpoint and get an access token
  • Pass that access token in the Header when you call the invoices (or any other) endpoint.

Good luck

4

Thanks John,
As I understand it, that’s exactly what I’m doing, though I’m no coder myself really and used CoPilot to help with the Power Query MCode.
My code is failing at the authorization endpoint and won’t return an access token.
When I use the OAuth 2.0 Playground, I can pull data, but I need it to work in Power Query to pass the data to Power BI automatically.
I’m hoping there is an MCode/Power Query expert on the forum somewhere.

5

The authorisation endpoint doesn’t return tokens.
It returns an authorisation code, which you should then be passing to the token endpoint.
They are two different endpoints!

If you have it working in the Playground, you should be able to see those steps separately.
And are you setting a scope?

Anyway sorry I can’t help more…I don’t do Power Query and hope I never have to
:slight_smile:

6

Looking at your code:
You seem to be passing the client ID and secret to the token endpoint.
You don’t do that.
You pass them to the authorisation endpoint,and get an authorisation code back.
You then pass that to the token endpoint to get a token.