How to edit this JSON code to pull different data from web?

Indominus

Board Regular
Joined
Jul 11, 2020
Messages
160
Office Version
  1. 2016
Platform
  1. Windows
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.

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:

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,214,983
Messages
6,122,583
Members
449,089
Latest member
Motoracer88

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top