Using Excel to pull data from API

问题:

I am a complete novice when it comes to coding and would really appreciate your help in a project.

I want to pull data in Excel from an API offered by a website (resource URL: http://api.opensignal.com/v2/networkrank.json).

Can you please advice how should I go about it. Or could you please help with a sample code.

Many thanks

回答1:

I made VBA-Web (Excel-REST) for accessing webservices and APIs with Excel. While I encourage you to look into tutorials on how to perform web requests with Excel (look for XMLHTTPRequest), I've found it to be a little tricky to get started, especially if you're new to programming, so here is some sample code based on OpenSignal's example:

Sub GetNetworkRank(Latitude As Double, Longitude As Double)
    ' Create client for executing requests
    Dim Client As New WebClient
    Client.BaseUrl = "http://api.opensignal.com/v1/"

    ' Create specific request
    Dim Request As New WebRequest
    Request.Resource = "networkrank.json"
    ' Request.Method = WebMethod.HttpGet is default
    ' Request.Format = WebFormat.Json is default

    Request.AddQuerystringParam "lat", Latitude
    Request.AddQuerystringParam "lng", Longitude

    ' distance=20 -> 20 km around lat-lng -> 40km x 40km bounding box
    Request.AddQuerystringParam "distance", 20

    ' network_id=3 -> 3G networks
    Request.AddQuerystringParam "network_id", 3

    Request.AddQuerystringParam "apikey", "YOUR_API_KEY"

    ' Get response from request
    Set Response = Client.Execute(Request)
    ' -> GET http://api.opensignal.com/v1/networkrank.json?lat=...&lng=...&...

    If Response.StatusCode = 200 Then
        ' Get network rank
        ' (json response is automatically parsed)
        Response.Data("networkRank")("...")
    Else
        Debug.Print "Error: " & Response.StatusCode & " " & Response.Content
    End If
End Sub

回答2:

First pick a langage. If you are a novice in programming, you can give Python a try. It is not that hard to get started. Just follow a good Getting Started Guide.

Then find the libraries you need to connect to your systems. For example:

• Requests to do some REST API calls
• Openpyxl to connect to XL

Try basic things (simple GET on the API, simple write in the Excel document). Make it work. Iterate.