Get array info from JSON in VBA

ellencrss commented 3 years ago

I'm trying to get JSON data and put it into a Excel Worksheet! I got this JSON data sample when use the URL + api token:

{"success":true,
    "data":
    { "id":69,
      "company_id":7695699,
      "owner_id": {
             "id":11743179,
         "name":"Allea",
             "email":"xxxx/2mail.com.br",
             "has_pic":1},
    "org_id":null,
    "name":"Teste_1",
    "first_name":"Teste_1",
    "last_name":null,
    "email_messages_count":0,
    "followers_count":1,
    "active_flag":true,
    "phone":[{
        "label":"work",
        "value":"1140028922",
        "primary":true}],
    "email":[{
        "label":"work",
        "value":"teste@mail.com",
        "primary":true}],
    "profile_id":"999999",
    "profile_type":"11"}}

I can get no-array data (like name or _profiletype) without any problem, but dont know how to get array data like email, for sample. Using this VBA code:

Sub v2()
Dim ws As Worksheet
Set ws = Sheets("ver2")

Dim company_domain As String
    company_domain = "xxx"

Dim data_field_api_key As String
    data_field_api_key = "76cdc98828ab2ada0abd194c1648965274bc9d13"

'Create http get request URL
Dim personUrl As String
    personUrl = "https://" & company_domain & ".pipedrive.com/v1/persons/" & ws.[pd_person_id] & "?api_token=" & ws.[pd_your_api_token]

Set http = CreateObject("WinHttp.WinHttpRequest.5.1")

http.Open "GET", personUrl, False
http.send

'Download data for the existing deal
Dim JSON As Object
Set JSON = JsonConverter.ParseJson(http.responseText)

Dim read_data As String
Dim read_nome As String
Dim read_email As String
Dim read_profile_id As String
Dim read_profile_type As String

read_data = JSON("data")(data_field_api_key)
read_nome = JSON("data")("name")
read_email = JSON("data")("email")
read_profile_id = JSON("data")("profile_id")
read_profile_type = JSON("data")("profile_type")

'Put data in Worksheet
ws.[pd_name].Value = read_nome
ws.[pd_email].Value = read_email
ws.[pd_profile_id].Value = read_profile_id
ws.[pd_profile_type].Value = read_profile_type

End Sub

aholden10 commented 3 years ago

The phone and email information are contained in an array so you need to include the array index which in the example you gave is just (0):('data')('email')(0)('label') => 'work'

(I didn't have the opportunity to test this but it should work...)

On Thursday, December 17, 2020, 12:41:16 PM EST, ellencrss <notifications@github.com> wrote:

I'm trying to get JSON data and put it into a Excel Worksheet! I got this JSON data sample when use the URL + api token: {"success":true, "data": { "id":69, "company_id":7695699, "owner_id": { "id":11743179, "name":"Allea", "email":"xxxx/2mail.com.br", "has_pic":1}, "org_id":null, "name":"Teste_1", "first_name":"Teste_1", "last_name":null, "email_messages_count":0, "followers_count":1, "active_flag":true, "phone":[{ "label":"work", "value":"1140028922", "primary":true}], "email":[{ "label":"work", "value":"teste@mail.com", "primary":true}], "profile_id":"999999", "profile_type":"11"}}

I can get no-array data (like name or profile_type) without any problem, but dont know how to get array data like email, for sample. Using this VBA code: Sub v2() Dim ws As Worksheet Set ws = Sheets("ver2")

Dim company_domain As String company_domain = "xxx"

Dim data_field_api_key As String data_field_api_key = "76cdc98828ab2ada0abd194c1648965274bc9d13"

'Create http get request URL Dim personUrl As String personUrl = "https://" & company_domain & ".pipedrive.com/v1/persons/" & ws.[pd_person_id] & "?api_token=" & ws.[pd_your_api_token]

Set http = CreateObject("WinHttp.WinHttpRequest.5.1")

http.Open "GET", personUrl, False http.send

'Download data for the existing deal Dim JSON As Object Set JSON = JsonConverter.ParseJson(http.responseText)

Dim read_data As String Dim read_nome As String Dim read_email As String Dim read_profile_id As String Dim read_profile_type As String

read_data = JSON("data")(data_field_api_key) read_nome = JSON("data")("name") read_email = JSON("data")("email") read_profile_id = JSON("data")("profile_id") read_profile_type = JSON("data")("profile_type")

'Put data in Worksheet ws.[pd_name].Value = read_nome ws.[pd_email].Value = read_email ws.[pd_profile_id].Value = read_profile_id ws.[pd_profile_type].Value = read_profile_type

End Sub

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub, or unsubscribe.

houghtonap commented 3 years ago

There are multiple issues with your sample code, but these basic issues seem to be a recurring theme when people ask for help with VBA-JSON:

You first need to understand the basics of VBA coding and in this case additionally the Excel VBA Object model.
You need to understand how VBA-JSON maps JSON objects and arrays to VBA objects.
You need to understand how the VBA.Collection object works: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/collection-object
You need to understand how the Scripting.Dictionary object works: https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object
You need to understand that accessing objects in VBA is distinctly different from accessing objects in JavaScript.

VBA-JSON Mapping of JSON

VBA-JSON maps a JSON Object { } to a VBA Scripting.Dictionary object.
VBA-JSON maps a JSON Array [ ] to a VBA VBA.Collection object.

These objects behave differently and have different object models with specific accessors and methods for retrieving values. You cannot intermix their object models. This is quite different from JavaScript that has a more coherent object model. For example using JavaScript you can access the "name" and "email" in the "data" object by:

function v2 ( )
{
  let json = { "success" : true, "data" : { "owner_id" : { "name": "Allea", "email":"xxxx/2mail.com.br" }, "profile_id": "999999", "profile_type": "11" } } ;

  let name1 = json.data.owner_id.name ;
  let name2 = json[ "data" ][ "owner_id" ][ "name" ] ;

  let email1 = json.data.owner_id.email ;
  let email2 = json[ "data" ][ "owner_id" ][ "email" ] ;

  let id1 = json.data.profile_id ;
  let id2 = json[ "data" ][ "profile_id" ] ;

  let type1 = json.data.profile_type ;
  let type2 = json[ "data" ][ "profile_type" ] ;

  console.log( `name1: ${name1}, email1: ${email1}, id1: ${id1}, type1: ${type1}` )
  console.log( `name2: ${name2}, email2: ${email2}, id2: ${id2}, type2: ${type2}` )
  return ;
}

By contrast to retrieve the name and email in VBA using VBA-JSON you must use the accessors and methods on the Scripting.Dictionary object since VBA-JSON maps the initial JSON Object to a Scripting.Dictionary object and it maps the "data" key's value to another Scripting.Dictionary object. For example using VBA and VBA-JSON you can access the "name" and "email" in the "data" object by:

Option Explicit

' Dependency, requires a VBA Reference to "Microsoft Scripting Runtime"
' Dependency, requires the VBA-JSON module to be imported into the VBA Project

Public Sub v2()

  Const mesg As String = "{ ""success"" : true, ""data"" : { ""owner_id"" : { ""name"" : ""Allea"", ""email"" : ""xxxx/2mail.com.br"" }, ""profile_id"" : ""999999"", ""profile_type"" : ""11"" } }"

  Dim ws As Worksheet  ' Reference to Excel Worksheet object that will be used

  Dim json As Scripting.Dictionary   ' Reference to Scripting.Dictionary object for top level JSON Object
  Dim data As Scripting.Dictionary   ' Reference to Scripting.Dictionary object for JSON "data" property's value
  Dim owner As Scripting.Dictionary  ' Reference to Scripting.Dictionary object for JSON "owner_id" property's value

  Dim name As Variant   ' VBA Variant data type that is used so we can distinguish when the JSON Object key is missing, e.g., Null
  Dim email As Variant  ' VBA Variant data type that is used so we can distinguish when the JSON Object key is missing, e.g., Null

  Dim id As Variant     ' VBA Variant data type that is used so we can distinguish when the JSON Object key is missing, e.g., Null
  Dim typ As Variant    ' VBA Variant data type that is used so we can distinguish when the JSON Object key is missing, e.g., Null

  Dim tbl As Excel.ListObject
  Dim row As Excel.ListRow

  ' The way I setup the ver2 Excel Worksheet is to have a four column table named "Profiles"
  '  with the headings "Name", "Email", "Profile ID", "Profile Type" in columns $A:$D.
  Set ws = Worksheets("ver2")           ' Worksheet is named "ver2"
  Set tbl = ws.ListObjects("Profiles")  ' Table on Worksheet "ver2" is named "Profiles"

  Set json = ParseJson(mesg)

  ' First lets make sure there was a successful message.
  ' Note, depending on the API being used checking for a successful message could be much more
  '  complicated since the API could return a JSON Object for success and a JSON Array, Object,
  '  String, Boolean, Number, Null for an error. This could mean that the json variable might
  '  need to be a VBA Variant and you will have to query the VBA Variant to determine exactly
  '  what was returned.
  If json.Exists("success") _
  Then

    ' In VBA we cannot make the following json.Item("success") an AND condition of the enclosing If statement
    '  because of VBA's order of evaluation.
    ' In addition, when using the Scripting.Dictionary object you must test for the presence of the key
    '  *before** using the key, otherwise the Scripting.Dictionary object will **add** the key if it is
    '  not in the dictionary.
    If json.Item("success") = True _
    Then
      ' In VBA we cannot make the following json.Exists("data") an AND condition of the enclosing If statement
      '  because of VBA's order of evaluation.
      ' In addition, when using the Scripting.Dictionary object you must test for the presence of the key
      '  *before** using the key, otherwise the Scripting.Dictionary object will **add** the key if it is
      '  not in the dictionary.
      Set data = VBA.IIf(json.Exists("data"), json.Item("data"), Nothing)

      If Not data Is Nothing _
      Then
        ' In VBA we cannot make the following data.Exists("owner_id") an AND condition of the enclosing If statement
        '  because of VBA's order of evaluation.
        ' In addition, when using the Scripting.Dictionary object you must test for the presence of the key
        '  *before** using the key, otherwise the Scripting.Dictionary object will **add** the key if it is
        '  not in the dictionary.
        Set owner = VBA.IIf(data.Exists("owner_id"), data.Item("owner_id"), Nothing)

        If Not owner Is Nothing _
        Then

          name = VBA.IIf(owner.Exists("name"), owner.Item("name"), Null)
          email = VBA.IIf(owner.Exists("email"), owner.Item("email"), Null)

          id = VBA.IIf(data.Exists("profile_id"), data.Item("profile_id"), Null)
          typ = VBA.IIf(data.Exists("profile_type"), data.Item("profile_type"), Null)

          Set row = tbl.ListRows.Add(, True)  ' Force "Profiels" table to insert row at the end of the table.

          tbl.ListColumns("Name").DataBodyRange(row.Index) = name
          tbl.ListColumns("Email").DataBodyRange(row.Index) = email
          tbl.ListColumns("Profile ID").DataBodyRange(row.Index) = id
          tbl.ListColumns("Profile Type").DataBodyRange(row.Index) = typ

        End If

      End If

    End If

  End If

  Exit Sub
End Sub

Note, that in VBA we cannot just string indices on the end of objects like your sample code is doing:

read_nome = JSON("data")("name")
read_email = JSON("data")("email")
read_profile_id = JSON("data")("profile_id")
read_profile_type = JSON("data")("profile_type")

That is not how the VBA Scripting.Dictionary object works.

Conceptual Issues with Your Code

Here are some other conceptual issues with your code:

read_data = JSON("data")(data_field_api_key)

This uses data_field_api_key which is not Object key of the given JSON.

ws.[pd_name].Value = read_nome
ws.[pd_email].Value = read_email
ws.[pd_profile_id].Value = read_profile_id
ws.[pd_profile_type].Value = read_profile_type

VBA does not use brackets [ ] when using indicies.

VBA Results

Suggestion to Moderator

Feel free to make this comment a sticky and/or use it to build a wiki page to help people use VBA-JSON so they will hopefully stop logging issues that need to be closed. In addition, go to the repository and enable the new discussions tab so things like this can be discussed without opening issues. Hope these suggestions cut down on the clutter found in the Issues.

VBA-tools / VBA-JSON