Excel and API
Has anyone figured out how to get Excel configured (I'm using O365) to pull data from web using the API?
-
The odds of my getting programming resources approved for simply pulling data from the API are virtually null. So I'd also like to use Excel's "PowerQuery" (Get & Transform) ability to collect data for import into our ERM software. But I haven't figured out how to get the authorization to work since it's apparently not designed to be included in the URL.
-
This took some research and a lot of trial and error, but I have VBA code for connecting to KnowBe4 using the MS Visual Basic Editor in Excel, and dumping the json information into a single cell. This will get you the format of which ever endpoint you are attempting to receive, i.e. in this case it is "users".
To use the VBA in Excel, you must first turn on the "Developer" tab. In Excel 2016, this is done by going to File/Home/Options/Customize Ribbon and in the right window pane, place a check mark in "Developer".
NOTE: Remember to replace YourAPIKeyGoesHere with your own API key, and there is a space before the word "Bearer" and your API key.
To access the other endpoints, simply replace the kb4endpoint = "users" variable with the endpoint variable that you want, for example; kb4endpoint = "groups"
I am working on finding a json parser that works with Excel, I found one, but there is a lot of work involved in getting things formatted well, so will continue this quest. and will post the results once I find them.
====================================================================================
Public Sub kb4UsersDataDump()
Dim xmlhttp As New MSXML2.ServerXMLHTTP60, myurl As String, kb4url As String, kb4endpoint As String
kb4url = "https://us.api.knowbe4.com/v1/"
kb4endpoint = "users"
myurl = kb4url$ & kb4endpoint$
xmlhttp.Open "GET", myurl, False
xmlhttp.setRequestHeader "Authorization", "Bearer YourAPIKeyGoesHere"
xmlhttp.setRequestHeader "Accept", "application/json"
xmlhttp.send
MsgBox (xmlhttp.responseText)
Range("A1").Value = "Users"
Range("B1").Value = xmlhttp.responseText
End Sub====================================================================================
-
As promised, below is a full set of code that will connect to KB4 using their APIs, and download the Json user data, then parse it into a worksheet. Remember to replace YourAPIKeyGoesHere with your own API key, and there is a space before the word "Bearer" and your API key.
The code begins below
================================================================================================
Public Sub parsekb4Users()
Dim myurl As String, kb4url As String, kb4endpoint As String
Dim http As Object, Json As Object, item As Object, i As Long
Dim ws As Worksheet' SET THE 'WS' variable TO THE WORKSEETS NAME, IN THIS CASE I NAMED THE WORKSHEET 'Users".
Set ws = Worksheets("Users")
Set http = CreateObject("MSXML2.XMLHTTP")
kb4url = "https://us.api.knowbe4.com/v1/"
kb4endpoint = "users"
myurl = kb4url$ & kb4endpoint$
http.Open "GET", myurl, False
http.setRequestHeader "Authorization", "Bearer YourAPIKeyGoesHere"
http.setRequestHeader "Accept", "application/json"
http.sendSet Json = JsonConverter.ParseJson(http.responseText)
' SET UP THE FIRST ROW FOR HEADERS
ws.Cells(1, 1).Value = "ID"
ws.Cells(1, 2).Value = "First Name"
ws.Cells(1, 3).Value = "Last Name"
ws.Cells(1, 4).Value = "Job Title"
ws.Cells(1, 5).Value = "Email"
ws.Cells(1, 6).Value = "Phish Prone Percentage"
ws.Cells(1, 7).Value = "Groups"
ws.Cells(1, 8).Value = "Current Risk Score"' DATA BEGINS ON SECOND ROW AND INCREMENTS FROM THERE
i = 2
For Each item In Json '("value")
ws.Cells(i, 1).Value = item("id")
ws.Cells(i, 2).Value = item("first_name")
ws.Cells(i, 3).Value = item("last_name")
ws.Cells(i, 4).Value = item("job_title")
ws.Cells(i, 5).Value = item("email")
ws.Cells(i, 6).Value = item("phish_prone_percentage")' BECAUSE THIS ARRAY FOR "GROUPS" CAN BE EMPTY, AND I COULD NOT FIGURE OUT HOW TO PARSE THE EMPTY ARRAY WITHOUT THROWING AN ERROR,
' I USED THE "ON ERROR RESUME NEXT" TO SKIRT THIS ISSUE.
' WE HAVE ONLY FIVE GROUPS AT THIS TIME, BUT IF YOU HAVE MORE, THEN YOU WILL NEED TO ADD EXTRA [ & " - " & item("groups")(N) ] STATEMENTS FOR EACH GROUP YOU
' WANT RETURNED AFTER THE FIFTH ONE.On Error Resume Next
If Not IsNull(item("groups")(1)) Then
ws.Cells(i, 7).Value = item("groups")(1) & " - " & item("groups")(2) & " - " & item("groups")(3) & " - " & item("groups")(4) & " - " & item("groups")(5)
End If
ws.Cells(i, 8).Value = item("current_risk_score")
i = i + 1
Next
' LET ME KNOW WHEN IT IS DONEMsgBox ("complete")
End Sub
==============================================================================================
-
One more thing that I forgot to tell you is that you will need the VBA-JSON converter, which is available on GitHub, (https://github.com/VBA-tools/VBA-JSON)_, or if you don’t like to click on links, then just Google “vbaconverter for Excel” and follow the link to GitHub. This library will do most of the hard work of parsing the JSON string to a dictionary for you and then you can write the values to an excel worksheet. When you download the latest release, you’ll need to import the “JsonConverter.bas” file to your VBA project. To do this, in the Excel VBA Editor, right click on the “VBAProject” in the left window pane, and select “Import file”. Navigate to where you unzipped the VBA-JSON file and select the “JsonConverter.bas” file.
You will also need to add a reference to the “Microsoft Scripting Runtime” by clicking the Tools drop-down list from the ribbon, selecting References and checking the box for Microsoft Scripting Runtime.
That should get you everything you need.
Kirjaudu sisään jättääksesi kommentin.
Kommentit
4 kommenttia