I’m currently evaluating a few different accounting platforms. I’m really the simplest of all use cases- no invoicing, customers, sales, etc. I just want to use the chart of accounts, and import my various statements. I’ve figured it all out via the GUI (I think) and it works great. I’ve even got the API exporting the transactions after I’ve “coded” them- or assigned my internal account(s) to the real transaction- I’m not entirely certain what the proper accounting terminology is.
Anyhow, during the process, I may have confused myself a little bit- I can’t seem to wrap my mind around the data structure of the elements I’m using. Maybe its just the API abstraction of it that has me confused.
I’ll use the API URLs to explain.
/receipts and /payments appear to contain records of the individual transactions, along with the real money accounts. These records are “keyed” with a UUID or similar.
/receipt-lines and /payment-lines appear to be duplicates of their associated URLs, but have no key with which to identify them or reference them back to the receipt itself. These records indicate my internal account name associated with the transaction.
As it stands, I’m just comparing the two API responses to determine which records belong together, but I imagine that is the “wrong way” and will not stand up to things like split transactions. Some sort of common key between the two records would be helpful.
I can’t seem to figure out what method to call to get a list of my internal accounts at all. I’ve done a fair amount of trial and error, but I feel like I’m missing some key piece of the puzzle here, and hope you all can help pinpoint it
Once you know UUID of payment or receipt, you can use API endpoints /api2/receipt-form or /api2/payment-form to retrieve complete payment or receipt object.
What kind of accounts? If you refer to Chart of Accounts, then this is not yet covered by API but I agree it should be supported for completeness.
I used an Excel macro to get all the UUIDs from the Manager Business Database.
This is just generic code, but I can get all the UUIDs from this.
Maybe you are interested in trying:
Make sure you add
Microsoft WinHTTP Services version 5.1 and Microsoft Scripting Runtime in Project Reference. and added VBA Json Module from
Dim username As String
Dim password As String
Sub GetMasterFromManager()
Dim baseUrl As String
Dim dataDict As Object
username = "administrator"
password = "Mypassword"
Set dataDict = CreateObject("Scripting.Dictionary")
GetDataFromAPI "https://MySubdomain.manager.io/api", "MyBusinessUUID", dataDict
WriteDataToWorksheet dataDict
End Sub
Sub WriteDataToWorksheet(dataDict As Object)
' Menyimpan referensi ke worksheet yang aktif
Dim ws As Worksheet
Set ws = ThisWorkbook.ActiveSheet
' Menentukan baris dan kolom pertama di mana data akan ditulis
Dim startRow As Long
Dim startCol As Long
startRow = 2 ' Ganti dengan baris awal yang Anda inginkan
startCol = 1 ' Ganti dengan kolom awal yang Anda inginkan
' Menghitung jumlah baris dan kolom berdasarkan ukuran dataDict
Dim numRows As Long
Dim numCols As Long
numRows = dataDict.Count
numCols = 3 ' Anda memiliki dua kolom (key dan value)
' Membuat 2D array untuk menyimpan data
Dim dataArr() As Variant
ReDim dataArr(1 To numRows, 1 To numCols)
' Mengisi 2D array dengan data dari dataDict
Dim key As Variant
Dim rowIndex As Long
Dim dt As Variant
rowIndex = 1
For Each key In dataDict.Keys
dt = Split(dataDict(key), "—")
dataArr(rowIndex, 1) = dt(0)
dataArr(rowIndex, 2) = dt(1)
dataArr(rowIndex, 3) = dt(2)
rowIndex = rowIndex + 1
Next key
' Menuliskan 2D array ke range di worksheet
ws.Cells(startRow, startCol).Resize(numRows, numCols).value = dataArr
End Sub
Sub GetDataFromAPI(baseUrl As String, targetKey As String, dataDict As Object, Optional level As Integer = 1)
Dim sUrl As String
sUrl = baseUrl & "/" & targetKey & ".json"
' Buat objek WinHttpRequest
Dim xhr As Object
Set xhr = CreateObject("WinHttp.WinHttpRequest.5.1")
' Set the authentication header
Dim credentials As String
credentials = username & ":" & password
xhr.Open "GET", sUrl, False
xhr.SetRequestHeader "Authorization", "Basic " & EncodeBase64(credentials)
' Kirim permintaan GET ke API
xhr.Send
' Periksa status respons
If xhr.Status = 200 Then
' Respons sukses, Anda dapat memproses data di sini
Dim responseData As String
responseData = xhr.ResponseText
'Debug.Print responseData
' Periksa apakah respons adalah null
If responseData <> "null" Then
' Parse respons JSON menggunakan modul VBA-JSON
Dim jsonArray As Object
Set jsonArray = JsonConverter.ParseJson(responseData)
' Mengekstrak "Key" dan "Name" dan menyimpannya ke dalam dictionary
ExtractDataToDictionary jsonArray, dataDict, level
' Panggil sub untuk mengekstrak data berdasarkan "Key" secara rekursif jika level masih di bawah 2
'If level <= 1 Then
ExtractDataRecursively baseUrl, jsonArray, targetKey, dataDict, level
'End If
Else
' Respons adalah null, lakukan penanganan sesuai kebutuhan
'MsgBox "Respons API null"
End If
Else
' Respons gagal
MsgBox "Gagal mengakses API. Kode Status: " & xhr.Status
End If
End Sub
' Fungsi untuk mengakses data secara rekursif berdasarkan "Key"
Sub ExtractDataRecursively(baseUrl As String, jsonData As Object, targetKey As String, dataDict As Object, level As Integer)
Dim item
Dim key
Dim name As String
If TypeName(jsonData) = "Scripting.Dictionary" Then
For Each key In jsonData.Keys
If key = targetKey Then
MsgBox "Key: " & targetKey & ", Value: " & jsonData(key)
End If
If level <= 1 Then
ExtractDataRecursively baseUrl, jsonData(key), targetKey, dataDict, level + 1
End If
Next
ElseIf TypeName(jsonData) = "Collection" Then
For Each item In jsonData
GetDataFromAPI baseUrl, targetKey & "/" & item("Key"), dataDict, level + 1
Next
End If
End Sub
' Fungsi untuk mengenkripsi string ke Base64
Function EncodeBase64(sText As String) As String
Dim arrData() As Byte
arrData = StrConv(sText, vbFromUnicode)
Dim objXML As Object
Set objXML = CreateObject("MSXML2.DOMDocument.6.0")
Dim objNode As Object
Set objNode = objXML.createElement("b64")
objNode.DataType = "bin.base64"
objNode.nodeTypedValue = arrData
EncodeBase64 = objNode.Text
End Function
' Fungsi untuk mengekstrak "Key" dan "Name" dan menyimpannya ke dalam dictionary
Sub ExtractDataToDictionary(jsonData As Object, dataDict As Object, level As Integer)
If TypeName(jsonData) = "Collection" Then
Dim item
For Each item In jsonData
Dim key As String
key = item("Key")
Dim name As String
name = item("Name")
' Menambahkan "Key" dan "Name" ke dalam dictionary
dataDict(key) = level & "—" & key & "—" & name
Next
End If
End Sub
Or you can also see the logic of this code, and apply it to your code.
Any old code still hanging around comes with at least an emotional cost to the developer, lol.
But yeah, it’d be nice to keep it around until all the api2 equivalent endpoints are finished. Having to tweak scripts for new endpoints is better than having to tweak your business processes if something becomes unavailable.
Here I’ve successfully retrieved a payment form into the $response variable. I’m attempting to change a value and then turn around and post to the same endpoint. I’m doing var_dumps along the way to try to detect any unexpected changes- but the JSON that finally gets sent validates just find, and at least looks identical (minus the pretty printing) to the received record with the exception of the value I’m changing. I have also tried to not change any value at all, set the JSON to various points in the process, etc. Everything gives me the same result:
PHP Fatal error: Uncaught GuzzleHttp\Exception\ServerException: Server error: `POST http://localhost:55667/api2/payment-form/0a05e9c2-c13c-4d37-bcbf-3c25ac80b354` resulted in a `500 Internal Server Error` response:
{
"error": "Newtonsoft.Json.JsonSerializationException: Error converting value \"{\n \"Date\": \"2023-11-08T00:00:0 (truncated...)
in /home/chris/Downloads/mgrworking/vendor/guzzlehttp/guzzle/src/Exception/RequestException.php:113
Stack trace:
#0 /home/chris/Downloads/mgrworking/vendor/guzzlehttp/guzzle/src/Middleware.php(72): GuzzleHttp\Exception\RequestException::create()
Has anyone dealt with this, see the error of my ways, or know this is a bug?
@bucke if you get 500 Internal Server Error which is this case, then the issue is at my end.
By the way, the method should be PUT, not POST. POST is equivalent to Create button in Manager. PUT is equivalent to Update button. I believe you are updating, not creating a record based on your endpoint URL.
