Hi
I suspect the answer is no, because I can't find anything through a web search, but is it possible to connect to Xero to pull in transaction data?
Thanks
Mardi
Hi,
you can use their API: https://developer.xero.com/documentation/api/invoices
Based on their docs, you have to build your query, with Get data from web.
That's great, thanks! I'll check it out.
The authorization process for the API is quite complicated and I haven't been able to get it to work in PQ in Excel.
You can use Power BI Service to connect to Xero
https://docs.microsoft.com/en-us/power-bi/connect-data/service-connect-to-xero
but there isn't any native connector made for Power BI Desktop.
Phil
Philip Treacy said
The authorization process for the API is quite complicated and I haven't been able to get it to work in PQ in Excel.You can use Power BI Service to connect to Xero
https://docs.microsoft.com/en-us/power-bi/connect-data/service-connect-to-xero
but there isn't any native connector made for Power BI Desktop.
Phil
Agree, but it is possible though.
A while ago, I had to connect to and read files from a OneDrive folder, that requires a similar process: first, you need to configure an app to collect user consent to access his account, display the login and consent screen, get an authentication code, send the code to get a token, use the token to read user files and create share links for those files.
Displaying the consent screen was solved with vba, there is no OAuth2 connector yet in excel PQ.
If you have already the xero application created, you can adjust the code below to display the consent screen and get the authorization code: (the token can be requested using power query using the authorization code)
Dim GetNewIE As SHDocVw.InternetExplorer, Counter As Long, start As Double, current As Double, Code As String
'create new IE instance
Set GetNewIE = New SHDocVw.InternetExplorer
start = Time
GetNewIE.Navigate2 "https://login.live.com/oauth20_authorize.srf?client_id=" & Range("AppID") & "&scope=onedrive.readwrite&response_type=code&redirect_uri= https://login.live.com/oauth20_desktop.sr f"
GetNewIE.Visible = True
On Error Resume Next
Do Until InStr(GetNewIE.LocationURL, "?code=") > 0
Application.Wait Now + TimeValue("0:00:01")
current = Time
If Round(current - start, 6) > Round(1 / 1440, 6) Then Exit Do 'wait for 30 sec only
Loop
If InStr(GetNewIE.LocationURL, "?code=") > 0 Then
Code = GetNewIE.LocationURL
Code = Right(Code, Len(Code) - InStr(Code, "?code=") - 5)
ThisWorkbook.Worksheets("Authentication").ListObjects("Code").Range.Cells(2) = Code
ThisWorkbook.Connections("Query - Token").Refresh
Else
ThisWorkbook.Worksheets("Authentication").ListObjects("Code").Range.Cells(2).ClearContents
End If
GetNewIE.Quit
Set GetNewIE = Nothing
End Sub
Getting the Token with PQ:
let
Code = Excel.CurrentWorkbook(){[Name="Code"]}[Content]{0}
,
ClientID= Excel.CurrentWorkbook(){[Name="AppID"]}[Content]{0}[Column1],
FolderPath= Excel.CurrentWorkbook(){[Name="FolderPath"]}[Content]{0}[Column1],
GetJson = Json.Document(Web.Contents("https://login.live.com",
[RelativePath="/oauth20_token.srf",
Headers = [#"Content-Type"="application/x-www-form-urlencoded"],
Content = Text.ToBinary("client_id=" & ClientID & "&redirect_uri= https://login.live.com/oauth20_desktop.srf&code =" & Code & "&grant_type=authorization_code")
])),Token = GetJson[access_token]
in
Token
