How to create JSON arrays from Excel Cell

[drkite]

Hi,

I am struggling with creating a JSON array of chartjs data with VBA-Web to be send to a dashing dashboard.

I am using following VBA-code:

Sub SendCHARTJSdata()
  Dim Body As New Dictionary

  Body.Add "auth_token", "YOUR_AUTH_TOKEN"
  Body.Add "labels", Array("CW12", "CW13") ' works fine
  ' Contents of Cells B23, C23: CW12 & CW13 respectively
  Body.Add "labels", Array(Worksheets("Sheet1").Range("B23:C23").Value) 'does not work
  ' Contents of Cell B24: <<[CW12, CW13]>>
  Body.Add "labels", Worksheets("Sheet1").Range("B24").Value 'does not work
  ' Contents of Cell B25: <<"CW12, CW13">>
  Body.Add "labels", Worksheets("Sheet1").Range("B25").Value 'does not work
  Body.Add "title", "metric" 'works fine
  Body.Add "status", "ok" ' works fine

  Dim Client As New WebClient
  Dim Response As WebResponse

  Set Response = Client.PostJson("http://somewebaddress:3030/widgets/test10", Body)
End Sub

Whatever I try to create a JSON array from content in an Excel cell will always result in a "-quoted string, e.g. labels : "[CW12, CW13]" instead of just labels: [CW12, CW13]

Any idea would be very much appreciated.

Thanks.

Bernhard

(edit: code formatting)

[zgrose]

The JSON ... "labels": [CW12, CW13], ... isn't valid JSON. You can have an arrays of:

objects, arrays

"labels":[ {...}, {...} ]
"labels":[ [], [] ]

strings, numbers, booleans, nulls

"labels":[ "", "" ]
"labels":[ 1, 2, 3  ]
"labels":[ true, false ]
"labels":[ null, null ]

So I'm going to guess that you actually want a label of strings "labels":[ "CW12", "CW13" ] which is why your Array("CW12", "CW13") both worked correct and is actually the correct way as well.

Assuming you want to make it more dynamic, depending on the particulars of your solution, you might do something like:

Public Sub ArrayOfAddressesInRange()
    Dim r As Range
    Set r = ActiveSheet.Range("CW12:CW13")
    Dim c As Range
    Dim cellRefCollection As New Collection
    For Each c In r.Cells
        cellRefCollection.Add c.Address(False, False)
    Next c
    Dim o As New Dictionary
    o.Add "labels", cellRefCollection
    Debug.Print ConvertToJson(o)
End Sub

which will give you:

{"labels":["CW12","CW13"]}

[drkite]

Hi,

wow this already helps a lot.

Just for further explanation, this is how my ruby code looks like:

data = [
    { type: 'line', label: 'Actuals',
    data: [1500,1200,1100,900,500,500,600,400,350,300,200,200,210],
   }, 
    {
    type: 'line', label: 'Plan',
    data: [{x:"2017-CW19", y:100},{x:"2018-CW24", y:100}],
   }, 
    {
    type: 'line',label: 'Minimum',
    data: [{x:"2017-CW19", y:400},{x:"2017-CW24", y:400}],
   }, 
]

label = ["2017-CW19", "2017-KW21", "2017-CW22", "2017-CW23", "2017-CW24"]

send_event('test10', { labels: label, datasets: data, title: 'test10', options: option, status: 'ok' })

With your macro, I have successfully created the labels thing. As the data does not change a lot right now, I'ld be happy to edit them in some Excel cell as one string

{ type: 'line', label: 'Actuals', data: [1500,1200,1100,900,500,500,600,400,350,300,200,200,210],},

but then again, if I add this cell, output looks like:

{"data":["{ type: 'line', label: 'Actuals', ...

(edit: code formatting)

[zgrose]

Not 100% sure what you mean but replace the inside of the For loop with cellRefCollection.Add c.Value and you’ll get the cell contents into an array.

[timhall]

If you want to write it in the cell as JSON you'll either need to convert it from JSON to Dictionary/Collection or pass the JSON directly as the body without automatic conversion:

WebHelpers.JsonOptions.AllowUnquotedKeys = True

Dim Data As New Collection
Dim Row As Variant
For Each Row In ThisWorkbook.Sheets("Sheet1").Range("A6:A8").Value
    Data.Add WebHelpers.ParseJson(Row)
Next Row

Body.Add "data", Data

Alternatively (although this isn't necessarily recommended):

Request.Body = "{""data"":[" & ActiveSheet.Range("A1").Value & "]}"