VLOOKUPWEB Nested JSON Objects

Public Function VLOOKUPWEB(apiUrl As String, jsonPath As String, Optional timeout As Integer = 10) As Variant
    Dim http As Object
    Dim response As String
    Dim json As Object
    Dim result As Variant
    
    ' HTTP Request
    Set http = CreateObject("MSXML2.XMLHTTP")
    http.Open "GET", apiUrl, False
    http.setTimeouts timeout * 1000, timeout * 1000, 30 * 1000, 60 * 1000
    http.send
    
    If http.Status <> 200 Then
        VLOOKUPWEB = "HTTP " & http.Status
        Exit Function
    End If
    
    response = http.responseText
    Set json = ParseJson(response)
    
    ' Navigate nested path: "data.users[3].salary"
    result = GetNestedValue(json, jsonPath)
    
    VLOOKUPWEB = result
End Function

Private Function ParseJson(jsonText As String) As Object
    ' Requires VBA-JSON library or simple parser
    Set ParseJson = JsonConverter.ParseJson(jsonText)
End Function

Private Function GetNestedValue(obj As Object, path As String) As Variant
    Dim parts() As String
    Dim i As Integer
    Dim current As Object
    Dim key As String
    Dim index As Integer
    
    Set current = obj
    parts = Split(path, ".")
    
    For i = 0 To UBound(parts)
        key = parts(i)
        
        ' Handle array index: users[2]
        If InStr(key, "[") > 0 Then
            index = Val(Mid(key, InStr(key, "[") + 1, InStr(key, "]") - InStr(key, "[") - 1))
            key = Left(key, InStr(key, "[") - 1)
            Set current = current(key)(index)
        Else
            ' Object property
            If HasProperty(current, key) Then
                Set current = current(key)
            Else
                GetNestedValue = "Path not found: " & path
                Exit Function
            End If
        End If
    Next i
    
    GetNestedValue = current
End Function

Private Function HasProperty(obj As Object, prop As String) As Boolean
    On Error Resume Next
    HasProperty = Not obj(prop) Is Nothing
    On Error GoTo 0
End Function

{
  "status": "success",
  "data": {
    "employees": [
      {
        "id": 1,
        "name": "John",
        "metrics": {
          "sales": 45000,
          "bonus": 5000,
          "commission": 0.08
        }
      },
      {
        "id": 2,
        "name": "Jane",
        "metrics": {
          "sales": 52000,
          "bonus": 6500
        }
      }
    ]
  }
}

=VLOOKUPWEB("https://api.company.com/employees", "data.employees[0].metrics.sales")
→ 45000

// Employee 2 bonus
=VLOOKUPWEB(url, "data.employees[1].metrics.bonus") → 6500

// Dynamic employee by ID
=VLOOKUPWEB(url, "data.employees["&A1&"].name") → Jane

// Total team sales
=SUM(VLOOKUPWEB(url, "data.employees[0].metrics.sales"), VLOOKUPWEB(url, "data.employees[1].metrics.sales"))

JSON Path                    | Excel Formula
-----------------------------|--------------------------------
john.metrics.sales           | "data.employees[0].metrics.sales"
all bonuses                  | "data.employees[*].metrics.bonus"
user by id                   | "data.employees[?id=2].name"

✅ Supported: obj.prop, array[0], nested.obj.prop

// A1=URL, B1=EmployeeID, C1=Metric
// D2 = Dynamic lookup
=VLOOKUPWEB($A$1, "data.employees["&$B2&"].metrics."&$C$1)

Result:
Employee | Metric | Value
2        | sales  | 52000
2        | bonus  | 6500

=IFERROR(
    VLOOKUPWEB(A1,B1),
    "API Error: "&B1
)

=IF(ISERROR(VLOOKUPWEB(url,path)),
    "No data",
    VLOOKUPWEB(url,path)
)

// All employee names
=VLOOKUPWEB(url, "data.employees[*].name")

// All sales (vertical spill)
=VLOOKUPWEB(url, "data.employees[*].metrics.sales")

📦 Auto-fills column with array results

// A1: API URL
// B2:D6 Employee dashboard
B2: =VLOOKUPWEB($A$1, "data.employees["&ROW()-2&"].name")
C2: =VLOOKUPWEB($A$1, "data.employees["&ROW()-2&"].metrics.sales")
D2: =VLOOKUPWEB($A$1, "data.employees["&ROW()-2&"].metrics.bonus")

🔄 Data → Refresh All → Live update