Has anyone managed to get data from FreeAgent into PowerBi?
Hi @eddablin,
I am interested in doing this as well. We have used Power BI to report on timesheet data quarterly by simply exporting all data and bringing it into Power BI via Excel, but I would now like to do this kind of analysis weekly or even daily via the API. If you have made any progress since your post I would love to hear more.
Thanks
Hi Rob,
No sadly got a bit stuck (certainly not a developer).
Thanks,
Ed
Rob,
Your message prompted me to have another go. I managed to achieve bringing in projects, contacts etc. and get around pagination so that I could have the data in one big list.
I need to do work on this and polish it up which may take me a while to find some time. I will then let you know how I did it. However, if you wanted to have a go in the meantime, these links were very useful:
Dealing with OAuth2.0 (adapting the code in the second post): https://community.powerbi.com/t5/Power-Query/Google-Oauth2-Google-My-Business-API/m-p/583211
Dealing with pagination: https://www.myonlinetraininghub.com/scrape-data-multiple-web-pages-power-query
Hi All,
Was any progress made with this?
Yes. I am bringing all sorts of data from FA into PowerBi and Excel. It is very very useful. Here is an example of getting contacts.
I use this query to get access (this was the hard bit):
(PageStart as text) =>
let
app_credentials ="client_id=...........",
url = app_credentials & "&refresh_token=" & "......." & "&grant_type=refresh_token",
GetJson = Web.Contents("https://api.freeagent.com/v2/token_endpoint",
[
Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
Content = Text.ToBinary(url)
]
),
AccessToken = Json.Document(GetJson)[access_token],
AccessTokenHeader = "Bearer " & AccessToken,
Source = Xml.Tables(Web.Contents("https://api.freeagent.com/v2/", [RelativePath="contacts?page="&PageStart&"&per_page=100", Headers=[#"Authorization"=AccessTokenHeader, Accept="application/xml", #"Content-Type"="application/xml"]])),
Table0 = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"Attribute:type", type text}})
in
#"Changed Type"
I then do this query to get data:
let
Source = {1..30},
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "PageStartNumbers"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"PageStartNumbers", type text}}),
#"Invoked Custom Function" = Table.AddColumn(#"Changed Type", "fnContactData", each fnContactData([PageStartNumbers])),
#"Expanded fnContactData" = Table.ExpandTableColumn(#"Invoked Custom Function", "fnContactData", {"contact", "Attribute:type"}, {"contact", "Attribute:type"}),
#"Expanded contact" = Table.ExpandTableColumn(#"Expanded fnContactData", "contact", {"url", "organisation-name", "active-projects-count", "created-at", "updated-at", "contact-name-on-invoices", "country", "charge-sales-tax", "locale", "account-balance", "status", "uses-contact-invoice-sequence", "emails-invoices-automatically", "emails-payment-reminders", "emails-thank-you-notes", "uses-contact-level-email-settings", "first-name", "last-name", "email", "billing-email", "address1", "town", "postcode", "address2", "default-payment-terms-in-days", "region", "address3", "sales-tax-registration-number", "mobile", "phone-number"}, {"url", "organisation-name", "active-projects-count", "created-at", "updated-at", "contact-name-on-invoices", "country", "charge-sales-tax", "locale", "account-balance", "status", "uses-contact-invoice-sequence", "emails-invoices-automatically", "emails-payment-reminders", "emails-thank-you-notes", "uses-contact-level-email-settings", "first-name", "last-name", "email", "billing-email", "address1", "town", "postcode", "address2", "default-payment-terms-in-days", "region", "address3", "sales-tax-registration-number", "mobile", "phone-number"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded contact",{"PageStartNumbers"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns1",{"url", "first-name", "last-name", "organisation-name", "active-projects-count", "created-at", "updated-at", "contact-name-on-invoices", "country", "charge-sales-tax", "locale", "account-balance", "status", "uses-contact-invoice-sequence", "emails-invoices-automatically", "emails-payment-reminders", "emails-thank-you-notes", "uses-contact-level-email-settings", "email", "billing-email", "address1", "town", "postcode", "address2", "default-payment-terms-in-days", "region", "address3", "sales-tax-registration-number", "mobile", "phone-number", "Attribute:type"}),
#"Renamed Columns1" = Table.RenameColumns(#"Reordered Columns",{{"organisation-name", "ContactID"}})
in
#"Renamed Columns1"
I think i have solved this by making the folling changes:
let
app_credentials ="client_id=......&client_secret=......&scope=&grant_type=authorization_code",
url = app_credentials & "&refresh_token=" & "......." & "&grant_type=refresh_token",
GetJson = Web.Contents("https://api.freeagent.com/v2/token_endpoint",
[
Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
Content = Text.ToBinary(url)
]
),
AccessToken = Json.Document(GetJson)[access_token],
AccessTokenHeader = "Bearer " & AccessToken,
Source = Xml.Tables(Web.Contents("https://api.freeagent.com/v2/", [RelativePath="contacts?page="&PageStart&"&per_page=100", Headers=[#"Authorization"=AccessTokenHeader, Accept="application/xml", #"Content-Type"="application/xml"]])),
Table0 = Source{0}[Table],
#"Changed Type" = Table.TransformColumnTypes(Table0,{{"Attribute:type", type text}})
in
#"Changed Type"
It seemed like the secret was missing but all good now - i was wondering @eddablin if you have managed to navigate getting past the constantly changing refresh token?
(had to edit this reply)
Ah yes, the refresh stuff is all good, was just me being stupid.
Have you managed to dynimically pull the ātotal contactā. From reading the docuemntation you can use the X-total-count but i am struggling to get PowerBI to retreve this contact, this will them make you second part of the code dynamic so that you dont need to know the maxium pages but rather pull that directly from teh API request.
Oh I do apologise. Yes I deleted that bit off it seems by accident whilst deleting out our key info.
I donāt know what you mean by constantly refreshing token?
In fact I think I do know what you mean. I am not a programmer so very much an amateur. This code was the result of trial and error and blood, sweat and tears.
If I am right, this code uses the OAuth identifier and OAuth secret to get a new bearer access token each time.
1 Like
Iām not sure how I got the ā¦ value on the third line of code. It is possible I used OAuth 2.0 Playground at some point to get a value.
@eddablin
Got it all up and working, thank you.
Have you managed to get it published to powerBI web and can you schedule refreshes? If so how did you get past the authentication on PowerBI Web?
Yes I have refreshes scheduled. I donāt even have paid service. Think you can refresh up to 6 times a day on free service. Think it all just worked when I published and then set up the refresh. Maybe share the details of any issues you are having?
Ah yes, ive managed to work it out and all looking good! i assumed you needed to go via a gateway but nope seems to be all good.
Thank you for your help its has been immensely useful.
Iām really struggling with this, would anyone be able to share a step by step guide on what to do. Iām reasonably technical but Iām no developer.
Thanks in advance.
Some of this I did years ago so may not be fully correct. Let me know if it works
FreeAgent Api
- Get FreeAgent API account at https://dev.freeagent.com/.
- Set up a new app. Leave url blank. You should have the following in āOAuth redirect URIsā:
https://developers.google.com/oauthplayground. - Use this app to get generate a secret.
OAuth 2.0 Playground
- In OAuth 2.0 Playground click settings cog and add the following in:
Authorisation endpoint: https://api.freeagent.com/v2/approve_app
Token endpoint: https://api.freeagent.com/v2/token_endpoint
OAuth Client ID: The āOAuth identifierā you get in your FreeAgent Api
OAuth Client secret: The āsecretā you get in your FreeAgent Api - In Step 1 Type āFreeAgentā and then Authorize API.
- In Step 2 add authorization code and get refresh token.
PowerBi
- Load PowerBi Desktop and start new one.
- Go to Transform data.
- Add new blank query and in Advanced Editor and paste in the following, making sure to change where I have [CAPITALS].
- The first part of this code gets a new access token using the refresh token to access data.
- The second part of the code starting with āSourceā is an example. This example gets year end accounts and manipulates it. You can change the Web.Contents relative path using different urls in FreeAgent Developer Dashboard to get different data.
let
Query2 = let
app_credentials ="client_id=[ADD CLIENT ID]&client_secret=[ADD SECRET]",
url = app_credentials & "&refresh_token=" & "[ADD REFRESH TOKEN]" & "&grant_type=refresh_token",
GetJson = Web.Contents("https://api.freeagent.com/v2/token_endpoint",
[
Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
Content = Text.ToBinary(url)
]
),
AccessToken = Json.Document(GetJson)[access_token],
AccessTokenHeader = "Bearer " & AccessToken,
Source = Xml.Tables(Web.Contents("https://api.freeagent.com/v2/", [RelativePath="accounting/profit_and_loss/summary?from_date=2024-10-01&to_date="&Text.From(Date.Year(DateTime.LocalNow()))&"-"&Text.From(Date.Month(DateTime.LocalNow()))&"-01", Headers=[#"Authorization"=AccessTokenHeader, Accept="application/xml", #"Content-Type"="application/xml"]])),
Table0 = Source{0}[Table]
in Source,
Table = Query2{0}[Table],
#"Expanded from" = Table.ExpandTableColumn(Table, "from", {"Element:Text"}, {"from.Element:Text"}),
#"Expanded to" = Table.ExpandTableColumn(#"Expanded from", "to", {"Element:Text"}, {"to.Element:Text"}),
#"Expanded less" = Table.ExpandTableColumn(#"Expanded to", "less", {"less"}, {"less.less"}),
#"Expanded less.less" = Table.ExpandTableColumn(#"Expanded less", "less.less", {"total"}, {"less.less.total"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded less.less",{{"less.less.total", "Corporation Tax"}}),
#"Removed Bottom Rows" = Table.RemoveLastN(#"Renamed Columns",2),
#"Changed Type" = Table.TransformColumnTypes(#"Removed Bottom Rows",{{"income", Int64.Type}, {"expenses", Int64.Type}, {"operating-profit", Int64.Type}, {"Corporation Tax", Int64.Type}, {"retained-profit", Int64.Type}, {"retained-profit-brought-forward", Int64.Type}, {"retained-profit-carried-forward", Int64.Type}}),
#"Merged Queries" = Table.NestedJoin(#"Changed Type", {"from.Element:Text"}, Y6, {"from.Element:Text"}, "FAProfit22/23", JoinKind.LeftOuter),
#"Removed Columns" = Table.RemoveColumns(#"Merged Queries",{"FAProfit22/23", "retained-profit", "retained-profit-brought-forward", "retained-profit-carried-forward"}),
#"Added Custom" = Table.AddColumn(#"Removed Columns", "NetProfit", each [income]-[expenses]-[Corporation Tax])
in
#"Added Custom"
My May 2023 post above is an example when data is needed to be taken from data that is paginated (e.g. timesheets). It loads multiple pages and then downloads data for each one.
Hi and thanks,
I got as far as generating the tokens, but then when I used your code in Power BI with my tokens inserted, I got this error:
Expression.Error: The āAuthorizationā header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Encoding, Accept-Language, Cache-Control, Content-Type, If-Modified-Since, Prefer, Range, Referer.
What did I do wrong?
Try in the Power Query interface go to Data source settings under Home tab. For FreeAgent permission in here click edit for each and change to type anonymous.
This has a habit of unsetting itself in my experience.
Bruce,
Sorry not sure i can help. This is not my profession and I got to this with a lot of blood, sweat and tears.
Some thoughts:
- For this authorisation part, have you tried copying text from both examples above.
- Are you sure you have entered all your IDs/tokens etc. correct. Check you havenāt changed any of your tokens in the OAuth 2.0 Playground and you thus would need to update what is in PowerBi.
- You can use PowerBi to get it to return the data that is coming back. This may help debug.
Good Luck