MYOB EXO API – Simple Test Script in Powershell

This will be primairily of use to Exo Consultants who need to verify what data an endpoint produces, or to Developers just getting started with the API who want to see what a very simple formula might look like.
It’s worth noting that like most long-time Exo consultants, I’m so comfortable with database calls that I never use the API myself to write interactions, preferring to write my own stored procedures and staging tables to handle inserting/updating data, so the below is about as complicated as I need to get.

Things needed to use the below scripts:
Invoke-RestMethod requires Powershell 3.
You can substitute Invoke-Webrequest if using a server with a lower version of Powershell 3.

A valid Developer Key, these are issued by signing up for the Exo API at MYOB’s website. To my knowledge, this Key is free.

A working install of Exo Business Services on your server. I usually install this directly on my SQL server, but really go with whatever your IT/Security peeps are happy with. I recommend against putting it on a general Terminal Server/Remote Desktop Services server because those things get busy, and I want to minimize transmission/processing times wherever possible.
To get Exo Business Services check the official MYOB EXO API forum. (Note, approved partner membership (Developer or Exo Certified) is required to be able to log in to this board.)

 

A valid Exo API key loaded in MYOB Exo. This counts as an extra module, and is a licensed purchase. (At time of writing, it is ALF only.) The Exo Business Partner for the database will be able to arrange purchase and loading of license codes.
While it is free to develop for the API, and there appear to be no charges for usage planned and I would be surprised to see them added, the API does cost the business using it to purchase.

The API requires Base64Encoding of username/password sent through. Numerous websites on the Internet will run Base64Encoding for you, however I use the below SQL Script to convert the strings for me (Because as an Exo consultant, I always have SQL Studio open. Use whatever works for you:

DECLARE @EncodeMe VARCHAR(MAX)='username:password'
,@UnencodeMe VARCHAR(MAX)=''
--Encode them, display.
SELECT
    CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    )   AS Base64Encoding
	,@EncodeMe AS UnencryptedText
FROM (
    SELECT CAST(@EncodeMe AS VARBINARY(MAX)) AS bin
) AS bin_sql_server_temp;
--Actually assign variable.
SELECT
    @UnencodeMe=CAST(N'' AS XML).value(
          'xs:base64Binary(xs:hexBinary(sql:column("bin")))'
        , 'VARCHAR(MAX)'
    )
FROM (
    SELECT CAST(@EncodeMe AS VARBINARY(MAX)) AS bin
) AS bin_sql_server_temp;
--Unencode variable.
SELECT 
    CAST(
        CAST(N'' AS XML).value(
            'xs:base64Binary(sql:variable("@UnencodeMe"))'
          , 'VARBINARY(MAX)'
        ) 
        AS VARCHAR(MAX)
    )   ASCIIEncoding
;

To use the above, simply set EncodeMe to whatever string you need. The format for the Authorization header, is USERNAME:PASSWORD (Note, Case sensitivity for the password.)

You’ll also need to generate an API Access Token (Which I call an ExoToken in my script) to allow for API access. These can be made in ExoConfig, under the Staff table.

Below is my full Power Shell script for accessing the Exo API.

Put the ExoToken from Exo Config into the $ExoToken variable.

Put the encoding Username/Password into the $AuthB64 variable (After the start “Basic “, this is a required part of the header.

If you are using the LOCAL API service, change the $LocalTesting Or $NetworkHost variables to match your servers name/IP Address/port configuration.

If you are using the LIVE RELAY service to interact from another server, you just need to change $URI to match the appropriate service and endpoint.

#Force compliant TLS management.  Must use TLS 1.2 or later when using LiveRelay service, but Invoke-RestMethod/Invoke-WebRequest default to TLS 1.0.

[Net.ServicePointManager]::SecurityProtocol = [Net.SecurityProtocolType]::Tls12

#Determine Log Name for the day.
$Date = Get-Date
$Page=1
$LogName="C:\Temp\APIInteract-" + $Date.Year +"-"+$Date.Month+"-" +$Date.Day +$Page+".txt"
#Assign ExoToken from the profile of the Exo user. 
$ExoToken=''
#'Basic '+ Base64Encode(<Username>:<Password>)
$AuthB64='Basic '
#Developer API Key goes here.
$APIKey='Get your own from my.myob' 
$Date = Get-Date
$LocalTesting='http://localhost:46192/' #Local machine.  Replace with relevant port numbers.
$NetworkHost='http://192.168.10.2:8888/' #To a different machine on your network.  Replace with relevant IP and Port number.
$RelayService='https://exo.api.myob.com/'  #Via MYOB's live relay service.
#Change Below Line to whatever is needed.
$baseuri=$RelayService
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add('Authorization',$AuthB64)
$headers.Add('x-myobapi-key',$APIKey)
$headers.Add('x-myobapi-exotoken',$ExoToken)
$headers.Add('accept','application/json')
$ContentType="XML"
Write-Output $Date | Out-File $LogName -append

    $Endpoint='customtable'        #List all Custom Tables.
    #$Endpoint='customtable?table=X_API_JOBSTOCK&PK=SEQNO' #Interact with table.
    #$Endpoint='discovery'      #List All Endpoints.
    $TestEndPoint='debtor?$filter=a.last_updated+ge+'+"'2019-05-01'"+"&page=$Page&pagesize=50"
    $uri=$baseuri+$Endpoint

    try { $Response= Invoke-RestMethod -Uri $uri -Method Get -Headers $headers  #-SslProtocol TLS12
        } 
    catch {
        $exceptionDetails = $_.Exception
        Write-Output "Error Invoking RestMethod " $uri | Out-File $LogName -append
        Write-Host $exceptionDetails
        Write-Output $exceptionDetails | Out-File $LogName -append
        }
        echo 'hi'
        Write-Output $Response
        Write-Output $Response | Out-File $LogName -append
        echo $Response.ArrayOfDebtor.Debtor
        echo $Reader
        echo 'hi2'

That’s it. No warranties, let me know if you have questions.