API using excel

36

You can access all tabs, report definitions and most settings.

However, using the API setup to retrieve a long list of transactions isn’t recommended since it’s going to be very slow and inefficient. So it’s better be reserved for use with master records like: employees, customers, payslip items … etc.

There are alternative methods to obtain transactional methods like this one:

This is a much faster and more efficient method to retrieve the full transaction list

To do that you can use the following code:

let
    _getGuiJson = (URL as text, Business as text, #"Tab View" as text) => 
        let 
            guiURL = URL & 
                (if #"Tab View" <> null then "/" & #"Tab View" else "") & ".json?FileID=" &
                (if Business <> null then Business else ""),

            webResponse = Web.Contents(guiURL),
            jsonResponse = try Json.Document(webResponse) otherwise null
        in
            if jsonResponse <> null then
                Table.FromRecords(jsonResponse)
            else
                error "Unexpected result. Wrong URL?",

    GetGuiJson = _getGuiJson

in
    GetGuiJson