Hi, so I have this JSON code that pulls metrics from a work website. However, it pulls different numbers than I am looking for. There are metrics on the web page in 60 minute increments and 15 minute increments. I need the 15 ones but I cannot figure out how to tweak this code to do it. Here is a sample of the code and the web data. Any help would be greatly appreciated. Been working on this for hours with no luck. Thanks in advance!
Web page page source. I need the metric name “IND” and there is a “Sorted” one also I need. Same format. The value I need is the metricValue.
{"timeSampledFloMetricsMap":{"15":[{"metricName":"IND","taskCountType":"Actual","dataPointList":[{"timeStampVal":"2021-10-03T14:15","metricValue":141},{"timeStampVal":"2021-10-03T14:30","metricValue":653},{"timeStampVal":"2021-10-03T14:45","metricValue":614},{"timeStampVal":"2021-10-03T15:00","metricValue":503},{"timeStampVal":"2021-10-03T15:15","metricValue":3},{"timeStampVal":"2021-10-03T15:30","metricValue":0},{"timeStampVal":"2021-10-03T15:45","metricValue":0},{"timeStampVal":"2021-10-03T16:00","metricValue":65},{"timeStampVal":"2021-10-03T16:15","metricValue":36},{"timeStampVal":"2021-10-03T16:30","metricValue":0},{"timeStampVal":"2021-10-03T16:45","metricValue":0},{"timeStampVal":"2021-10-03T17:00"
And here is the part of the code that needs to be edited however all edits I have made have gone no where.
Web page page source. I need the metric name “IND” and there is a “Sorted” one also I need. Same format. The value I need is the metricValue.
{"timeSampledFloMetricsMap":{"15":[{"metricName":"IND","taskCountType":"Actual","dataPointList":[{"timeStampVal":"2021-10-03T14:15","metricValue":141},{"timeStampVal":"2021-10-03T14:30","metricValue":653},{"timeStampVal":"2021-10-03T14:45","metricValue":614},{"timeStampVal":"2021-10-03T15:00","metricValue":503},{"timeStampVal":"2021-10-03T15:15","metricValue":3},{"timeStampVal":"2021-10-03T15:30","metricValue":0},{"timeStampVal":"2021-10-03T15:45","metricValue":0},{"timeStampVal":"2021-10-03T16:00","metricValue":65},{"timeStampVal":"2021-10-03T16:15","metricValue":36},{"timeStampVal":"2021-10-03T16:30","metricValue":0},{"timeStampVal":"2021-10-03T16:45","metricValue":0},{"timeStampVal":"2021-10-03T17:00"
And here is the part of the code that needs to be edited however all edits I have made have gone no where.
VBA Code:
obj_http.Open "GET", log_URL
obj_http.SetAutoLogonPolicy 0
obj_http.setRequestHeader "Bypass", BypassHeader
obj_http.send
obj_http.WaitForResponse
Set JSON2 = S.Eval("(" & obj_http.responseText & ")")
Set JSON1 = CallByName(CallByName(JSON2, "timeSampledFloMetricsMap", VbGet), "60", VbGet)
With Sheets("Raw Data")
'Input-Output Actual
For x = 1 To 2
Set JSON = CallByName(CallByName(JSON1, path_var(x), VbGet), "dataPointList", VbGet)
Set keyring = S.Run("keys", JSON)
For Each key In keyring
data_slice_date = CDate(Left(CallByName(CallByName(JSON, key, VbGet), "timeStampVal", VbGet), 10) & " " & Right(CallByName(CallByName(JSON, key, VbGet), "timeStampVal", VbGet), 5)) - (1 / 24)
If data_slice_date >= shift_start And data_slice_date<= shift_end Then
'slice_hour = Hour((CDate(Left(CallByName(CallByName(JSON, key, VbGet), "timeStampVal", VbGet), 10) & " " & Right(CallByName(CallByName(JSON, key, VbGet), "timeStampVal", VbGet), 5))) - (1 / 24))
hour_cell = Hour((CDate(Left(CallByName(CallByName(JSON, key, VbGet), "timeStampVal", VbGet), 10) & " " & Right(CallByName(CallByName(JSON, key, VbGet), "timeStampVal", VbGet), 5))) - (1 / 24)) & ":00 - " & Hour((CDate(Left(CallByName(CallByName(JSON, key, VbGet), "timeStampVal", VbGet), 10) & " " & Right(CallByName(CallByName(JSON, key, VbGet), "timeStampVal", VbGet), 5)))) & ":00"
'.Rows(2).Find(hour_cell).Offset(1 + x, 0).Select
.Rows(2).Find(hour_cell).Offset(1 + x, 0) = CallByName(CallByName(JSON, key, VbGet), "metricValue", VbGet)
End If
Next key
Next x
Last edited: