Extracting a simple value from a JSON response in VBA

Question

I am trying to get a simple response from a website and want to return a value into a field.

the json looks similar to this: https://api.sunrise-sunset.org/json?lat=38.725&lng=-9.355&date=2023-08-07

{"results":{"sunrise":"5:42:35 AM","sunset":"7:43:53 PM","solar_noon":"12:43:14 PM","day_length":"14:01:18","civil_twilight_begin":"5:14:59 AM","civil_twilight_end":"8:11:30 PM","nautical_twilight_begin":"4:39:34 AM","nautical_twilight_end":"8:46:54 PM","astronomical_twilight_begin":"4:01:24 AM","astronomical_twilight_end":"9:25:04 PM"},"status":"OK"}

I have written (found here) a function to get this data:

Function GetSun(lat,lon As Double, dateinput As Date) As String
Dim url as String
url = "https://api.sunrise-sunset.org/json?lat=" & lat & "&lng=" & lon & "&date=" & dateinput

On Error GoTo ErrorHandler

Dim funtionAccess As Object
functionAccess = createUnoService("com.sun.star.sheet.FunctionAccess")

GetSun = functionAccess.callFunction("WEBSERVICE",Array(url))

Exit Function
ErrorHandler:
GetSun = "Error " & Err
End Function

In the cell it now has:

C12 = the latitude information

C13 = the longitude information

B6 = the date

=GETSUN(C12,C13,B6)

But what I would like is: (where I can define the returned field)

=GETSUN(C12,C13,B6,sunrise)

to return:

5:42:35 AM

And

=GETSUN(C12,C13,B6,sunset)

to return:

7:43:53 PM

If it matters: libreoffice calc on a linux mint laptop

Answer 1

Pass your Json string to two helper functions that use Split to extract the values:

Public Function GetSunrise(ByVal SunData As String) As String

    GetSunrise = Split(Split(SunData, """sunrise"":""", 2)(1), """,""", 2)(0)

End Function

Public Function GetSunset(ByVal SunData As String) As String

    GetSunset = Split(Split(SunData, """sunset"":""", 2)(1), """,""", 2)(0)

End Function

Test/output:

Sunrise = GetSunrise(s)
? Sunrise
5:42:35 AM

Sunset = GetSunset(s)
? Sunset
7:43:53 PM

Addendum:

For returning true date values:

Public Function GetSunrise(ByVal SunData As String) As Date

    GetSunrise = CDate(Split(Split(SunData, """sunrise"":""", 2)(1), """,""", 2)(0))

End Function

Public Function GetSunset(ByVal SunData As String) As Date

    GetSunset = CDate(Split(Split(SunData, """sunset"":""", 2)(1), """,""", 2)(0))

End Function

Test/output:

Sunrise = GetSunrise(s)
? Sunrise
05:42:35 

Sunset = GetSunset(s)
? Sunset
19:43:53