API extraction to SQL Data Base

Hi MAC221267 , Andrew999 , when AccountRight Live was first introduced, MYOB attempted to provide an ODBC equivalent, but they realised that it had significant limitations, and developed the API instead, which is far superior.

To extract data to Excel from a cloud file, whatever process or code you use needs to be able to authenticate via oauth2, and navigate the 2FA requirement. 

An easier approach is to make a backup of your cloud file, and restore it to your local library, where all you will need is a username and password.

For example, these are the URL parts of my data source which returns a list of all suppliers:

http://localhost:8080/AccountRight/<your file id>/Contact/Supplier 

?x-myobapi-version="v2"

&Authorisation="Basic QWRtaW5pc3RyYXRvcjo="

Notes:

FileID can be found by entering http://localhost:8080/AccountRight/ into your browser and search for your company file

The second part starts with a ?

The third (and subsequent, if any) parts starts with a &

The phrase QWRtaW5pc3RyYXRvcjo= is the encoded form of <username>=<password>, in this case Administrator=, you can get this from base64encode.org.

I specify the file to be opened as json.

The full text of my query in Excel 2016 is as follows:

let
Source = Json.Document(Web.Contents("http://localhost:8080/AccountRight/<my file id>/Contact/Supplier" & "?x-myobapi-version=""v2""" & "&Authorisation=""Basic QWRtaW5pc3RyYXRvcjo=""")),
#"Converted to Table" = Record.ToTable(Source),
Value = #"Converted to Table"{0}[Value],
#"Converted to Table1" = Table.FromList(Value, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table1", "Column1", {"ABN", "ABNBranch", "CreditLimit", "VolumeDiscount", "ExpenseAccountId", "UseSupplierTaxCode", "Memo", "PurchaseLayoutId", "HourlyBillingRate", "PrintedForm", "PurchaseComment", "ShippingMethod", "TaxIdNumber", "TermsId", "TaxCodeId", "FreightTaxCodeId", "CostPerHour", "IsReportable", "CoLastName", "FirstName", "IsIndividual", "EnteredId", "IsActive", "Addresses", "Description", "Tags", "CustomLists", "CustomFields", "CurrentBalance", "Uri", "Id", "RowVersion"}, {"Column1.ABN", "Column1.ABNBranch", "Column1.CreditLimit", "Column1.VolumeDiscount", "Column1.ExpenseAccountId", "Column1.UseSupplierTaxCode", "Column1.Memo", "Column1.PurchaseLayoutId", "Column1.HourlyBillingRate", "Column1.PrintedForm", "Column1.PurchaseComment", "Column1.ShippingMethod", "Column1.TaxIdNumber", "Column1.TermsId", "Column1.TaxCodeId", "Column1.FreightTaxCodeId", "Column1.CostPerHour", "Column1.IsReportable", "Column1.CoLastName", "Column1.FirstName", "Column1.IsIndividual", "Column1.EnteredId", "Column1.IsActive", "Column1.Addresses", "Column1.Description", "Column1.Tags", "Column1.CustomLists", "Column1.CustomFields", "Column1.CurrentBalance", "Column1.Uri", "Column1.Id", "Column1.RowVersion"}),
#"Reordered Columns" = Table.ReorderColumns(#"Expanded Column1",{"Column1.CoLastName", "Column1.Addresses", "Column1.ABN", "Column1.ABNBranch", "Column1.CreditLimit", "Column1.VolumeDiscount", "Column1.ExpenseAccountId", "Column1.UseSupplierTaxCode", "Column1.Memo", "Column1.PurchaseLayoutId", "Column1.HourlyBillingRate", "Column1.PrintedForm", "Column1.PurchaseComment", "Column1.ShippingMethod", "Column1.TaxIdNumber", "Column1.TermsId", "Column1.TaxCodeId", "Column1.FreightTaxCodeId", "Column1.CostPerHour", "Column1.IsReportable", "Column1.FirstName", "Column1.IsIndividual", "Column1.EnteredId", "Column1.IsActive", "Column1.Description", "Column1.Tags", "Column1.CustomLists", "Column1.CustomFields", "Column1.CurrentBalance", "Column1.Uri", "Column1.Id", "Column1.RowVersion"}),
#"Expanded Column1.Addresses" = Table.ExpandListColumn(#"Reordered Columns", "Column1.Addresses"),
#"Expanded Column1.Addresses1" = Table.ExpandRecordColumn(#"Expanded Column1.Addresses", "Column1.Addresses", {"Index", "Street", "City", "State", "PostCode", "Country", "Phone1", "Email"}, {"Column1.Addresses.Index", "Column1.Addresses.Street", "Column1.Addresses.City", "Column1.Addresses.State", "Column1.Addresses.PostCode", "Column1.Addresses.Country", "Column1.Addresses.Phone1", "Column1.Addresses.Email"}),
#"Filtered Rows" = Table.SelectRows(#"Expanded Column1.Addresses1", each ([Column1.Addresses.Index] = 1)),
#"Reordered Columns1" = Table.ReorderColumns(#"Filtered Rows",{"Column1.CoLastName", "Column1.Id", "Column1.Addresses.Index", "Column1.Addresses.Street", "Column1.Addresses.City", "Column1.Addresses.State", "Column1.Addresses.PostCode", "Column1.Addresses.Country", "Column1.Addresses.Phone1", "Column1.Addresses.Email", "Column1.ABN", "Column1.ABNBranch", "Column1.CreditLimit", "Column1.VolumeDiscount", "Column1.ExpenseAccountId", "Column1.UseSupplierTaxCode", "Column1.Memo", "Column1.PurchaseLayoutId", "Column1.HourlyBillingRate", "Column1.PrintedForm", "Column1.PurchaseComment", "Column1.ShippingMethod", "Column1.TaxIdNumber", "Column1.TermsId", "Column1.TaxCodeId", "Column1.FreightTaxCodeId", "Column1.CostPerHour", "Column1.IsReportable", "Column1.FirstName", "Column1.IsIndividual", "Column1.EnteredId", "Column1.IsActive", "Column1.Description", "Column1.Tags", "Column1.CustomLists", "Column1.CustomFields", "Column1.CurrentBalance", "Column1.Uri", "Column1.RowVersion"})
in
#"Reordered Columns1"

The query expands various lists eg addresses, and filters on address 1. 

I hope this is enough to get you started. I don't do much reporting directly into Excel from AccountRight Live as I have our own excellent report writer to work with :).