(Excel) Quickbooks Query an Invoice

Demonstrates how to query for invoices matching a SELECT statement via the Quickbooks REST API.

For more information, see https://www.developer.intuit.com/app/developer/qbo/docs/api/accounting/most-commonly-used/invoice#query-an-invoice

Download Excel Class Modules Chilkat Excel Class Modules

' This example requires the Chilkat API to have been previously unlocked.
' See Global Unlock Sample for sample code.

' First get our previously obtained OAuth2 access token.
Dim jsonToken As Chilkat.JsonObject
Set jsonToken = Chilkat.NewJsonObject

success = jsonToken.LoadFile("qa_data/tokens/qb-access-token.json")

Dim rest As Chilkat.Rest
Set rest = Chilkat.NewRest


bTls = True

port = 443

bAutoReconnect = True

success = rest.Connect("sandbox-quickbooks.api.intuit.com",port,bTls,bAutoReconnect)

Dim sbAuth As Chilkat.StringBuilder
Set sbAuth = Chilkat.NewStringBuilder
Dim success As Boolean
success = sbAuth.Append("Bearer ")
success = sbAuth.Append(jsonToken.StringOf("access_token"))
rest.Authorization = sbAuth.GetAsString()

' --------------------------------------------------------------------------
' Note: The above REST connection and setup of the AWS credentials
' can be done once.  After connecting, any number of REST calls can be made.
' The "auto reconnect" property passed to rest.Connect indicates that if
' the connection is lost, a REST method call will automatically reconnect
' if needed.
' --------------------------------------------------------------------------

' This is a GET request, so there should be no Content-Type
' This line of code is just to make sure..
success = rest.RemoveHeader("Content-Type")

success = rest.AddHeader("Accept","application/json")
rest.AllowHeaderFolding = False

' Add a SELECT statement 
success = rest.AddQueryParam("query","select * from Invoice where id = '239'")

Dim sbResponseBody As Chilkat.StringBuilder
Set sbResponseBody = Chilkat.NewStringBuilder
success = rest.FullRequestNoBodySb("GET","/v3/company/<realmID>/invoice",sbResponseBody)
If (success <> True) Then
    Debug.Print rest.LastErrorText
    Exit Sub
End If


respStatusCode = rest.ResponseStatusCode
If (respStatusCode >= 400) Then
    Debug.Print "Response Status Code = "; respStatusCode
    Debug.Print "Response Header:"
    Debug.Print rest.ResponseHeader
    Debug.Print "Response Body:"
    Debug.Print sbResponseBody.GetAsString()
    Exit Sub
End If

' Success is indicated by a 200 response status.
Debug.Print "response status code = "; respStatusCode

Dim json As Chilkat.JsonObject
Set json = Chilkat.NewJsonObject
success = json.LoadSb(sbResponseBody)
json.EmitCompact = False
Debug.Print json.Emit()

' Sample output:
' Use the this online tool to generate parsing code from sample JSON: 
' Generate Parsing Code from JSON

' {
'   "QueryResponse": {
'     "startPosition": 1, 
'     "totalCount": 1, 
'     "maxResults": 1, 
'     "Invoice": [
'       {
'         "DocNumber": "1070", 
'         "SyncToken": "0", 
'         "domain": "QBO", 
'         "Balance": 150.0, 
'         "BillAddr": {
'           "City": "Bayshore", 
'           "Line1": "4581 Finch St.", 
'           "PostalCode": "94326", 
'           "Lat": "INVALID", 
'           "Long": "INVALID", 
'           "CountrySubDivisionCode": "CA", 
'           "Id": "2"
'         }, 
'         "TxnDate": "2015-07-24", 
'         "TotalAmt": 150.0, 
'         "CustomerRef": {
'           "name": "Amy's Bird Sanctuary", 
'           "value": "1"
'         }, 
'         "ShipAddr": {
'           "City": "Bayshore", 
'           "Line1": "4581 Finch St.", 
'           "PostalCode": "94326", 
'           "Lat": "INVALID", 
'           "Long": "INVALID", 
'           "CountrySubDivisionCode": "CA", 
'           "Id": "109"
'         }, 
'         "LinkedTxn": [], 
'         "DueDate": "2015-08-23", 
'         "PrintStatus": "NeedToPrint", 
'         "Deposit": 0, 
'         "sparse": false, 
'         "EmailStatus": "NotSet", 
'         "Line": [
'           {
'             "LineNum": 1, 
'             "Amount": 150.0, 
'             "SalesItemLineDetail": {
'               "TaxCodeRef": {
'                 "value": "NON"
'               }, 
'               "ItemRef": {
'                 "name": "Services", 
'                 "value": "1"
'               }
'             }, 
'             "Id": "1", 
'             "DetailType": "SalesItemLineDetail"
'           }, 
'           {
'             "DetailType": "SubTotalLineDetail", 
'             "Amount": 150.0, 
'             "SubTotalLineDetail": {}
'           }
'         ], 
'         "ApplyTaxAfterDiscount": false, 
'         "CustomField": [
'           {
'             "DefinitionId": "1", 
'             "Type": "StringType", 
'             "Name": "Crew #"
'           }
'         ], 
'         "Id": "239", 
'         "TxnTaxDetail": {
'           "TotalTax": 0
'         }, 
'         "MetaData": {
'           "CreateTime": "2015-07-24T10:35:08-07:00", 
'           "LastUpdatedTime": "2015-07-24T10:35:08-07:00"
'         }
'       }
'     ]
'   }, 
'   "time": "2015-07-24T10:38:50.01-07:00"
' }


QueryResponseStartPosition = json.IntOf("QueryResponse.startPosition")

QueryResponseTotalCount = json.IntOf("QueryResponse.totalCount")

QueryResponseMaxResults = json.IntOf("QueryResponse.maxResults")

time = json.StringOf("time")

i = 0

count_i = json.SizeOfArray("QueryResponse.Invoice")
Do While i < count_i
    json.I = i
    DocNumber = json.StringOf("QueryResponse.Invoice[i].DocNumber")
    SyncToken = json.StringOf("QueryResponse.Invoice[i].SyncToken")
    domain = json.StringOf("QueryResponse.Invoice[i].domain")
    Balance = json.StringOf("QueryResponse.Invoice[i].Balance")
    BillAddrCity = json.StringOf("QueryResponse.Invoice[i].BillAddr.City")
    BillAddrLine1 = json.StringOf("QueryResponse.Invoice[i].BillAddr.Line1")
    BillAddrPostalCode = json.StringOf("QueryResponse.Invoice[i].BillAddr.PostalCode")
    BillAddrLat = json.StringOf("QueryResponse.Invoice[i].BillAddr.Lat")
    BillAddrLong = json.StringOf("QueryResponse.Invoice[i].BillAddr.Long")
    BillAddrCountrySubDivisionCode = json.StringOf("QueryResponse.Invoice[i].BillAddr.CountrySubDivisionCode")
    BillAddrId = json.StringOf("QueryResponse.Invoice[i].BillAddr.Id")
    TxnDate = json.StringOf("QueryResponse.Invoice[i].TxnDate")
    TotalAmt = json.StringOf("QueryResponse.Invoice[i].TotalAmt")
    CustomerRefName = json.StringOf("QueryResponse.Invoice[i].CustomerRef.name")
    CustomerRefValue = json.StringOf("QueryResponse.Invoice[i].CustomerRef.value")
    ShipAddrCity = json.StringOf("QueryResponse.Invoice[i].ShipAddr.City")
    ShipAddrLine1 = json.StringOf("QueryResponse.Invoice[i].ShipAddr.Line1")
    ShipAddrPostalCode = json.StringOf("QueryResponse.Invoice[i].ShipAddr.PostalCode")
    ShipAddrLat = json.StringOf("QueryResponse.Invoice[i].ShipAddr.Lat")
    ShipAddrLong = json.StringOf("QueryResponse.Invoice[i].ShipAddr.Long")
    ShipAddrCountrySubDivisionCode = json.StringOf("QueryResponse.Invoice[i].ShipAddr.CountrySubDivisionCode")
    ShipAddrId = json.StringOf("QueryResponse.Invoice[i].ShipAddr.Id")
    DueDate = json.StringOf("QueryResponse.Invoice[i].DueDate")
    PrintStatus = json.StringOf("QueryResponse.Invoice[i].PrintStatus")
    Deposit = json.IntOf("QueryResponse.Invoice[i].Deposit")
    sparse = json.BoolOf("QueryResponse.Invoice[i].sparse")
    EmailStatus = json.StringOf("QueryResponse.Invoice[i].EmailStatus")
    ApplyTaxAfterDiscount = json.BoolOf("QueryResponse.Invoice[i].ApplyTaxAfterDiscount")
    Id = json.StringOf("QueryResponse.Invoice[i].Id")
    TxnTaxDetailTotalTax = json.IntOf("QueryResponse.Invoice[i].TxnTaxDetail.TotalTax")
    MetaDataCreateTime = json.StringOf("QueryResponse.Invoice[i].MetaData.CreateTime")
    MetaDataLastUpdatedTime = json.StringOf("QueryResponse.Invoice[i].MetaData.LastUpdatedTime")
    j = 0
    count_j = json.SizeOfArray("QueryResponse.Invoice[i].LinkedTxn")
    Do While j < count_j
        json.J = j
        j = j + 1
    Loop
    j = 0
    count_j = json.SizeOfArray("QueryResponse.Invoice[i].Line")
    Do While j < count_j
        json.J = j
        LineNum = json.IntOf("QueryResponse.Invoice[i].Line[j].LineNum")
        Amount = json.StringOf("QueryResponse.Invoice[i].Line[j].Amount")
        SalesItemLineDetailTaxCodeRefValue = json.StringOf("QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.TaxCodeRef.value")
        SalesItemLineDetailItemRefName = json.StringOf("QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.ItemRef.name")
        SalesItemLineDetailItemRefValue = json.StringOf("QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.ItemRef.value")
        Id = json.StringOf("QueryResponse.Invoice[i].Line[j].Id")
        DetailType = json.StringOf("QueryResponse.Invoice[i].Line[j].DetailType")
        j = j + 1
    Loop
    j = 0
    count_j = json.SizeOfArray("QueryResponse.Invoice[i].CustomField")
    Do While j < count_j
        json.J = j
        DefinitionId = json.StringOf("QueryResponse.Invoice[i].CustomField[j].DefinitionId")
        invType = json.StringOf("QueryResponse.Invoice[i].CustomField[j].Type")
        Name = json.StringOf("QueryResponse.Invoice[i].CustomField[j].Name")
        j = j + 1
    Loop
    i = i + 1
Loop