Using the Zoom API to deal with webinar panelists

In response to the pandemic my school is using Zoom Webinars for various performances. Our student Improv group has done a number of performances. The cast all need to be “panelists” in Zoom parlance, and when you duplicate a Webinar it does not duplicate the panelists. That’s a pain since there are a lot of panelists, and for each you need a name and e-mail address.

I finally got tired of dealing with it and spent a few minutes figuring out the Zoom API. If I ever have a little more time, I’ll make a Google Apps Manager (GAM) knock off that I’ll call ZAM! In the meantime, perhaps someone else will find this helpful.

First, Zoom has good documentation of its API. To start, you will need to build an app to get credentials. For my scripts, I’m using JSON Web Tokens (JWT) for authentication. I found this post in the Zoom Dev Forums helpful as a starting point.

Get information on a Zoom user

Here is a short Python script to get information on a user to give a little idea on how to use the API.

import jwt   # pip3 install pyjwt --user
import http.client
import datetime
import json
import sys

api_key = '********' # replace with credentials from
api_sec = '********' # your app in Zoom Marketplace

# generate JWT
payload = {
'iss': api_key,
'exp': datetime.datetime.utcnow() + datetime.timedelta(hours=2)
}

jwt_encoded = str(jwt.encode(payload, api_sec), 'utf-8')


# call API: get user list
conn = http.client.HTTPSConnection("api.zoom.us")
headers = {
'authorization': "Bearer %s" % jwt_encoded,
'content-type': "application/json"
}
email = str(sys.argv[1])
conn.request("GET", "/v2/users/" +  email, headers=headers)
res = conn.getresponse()
response_string = res.read().decode('utf-8')

user_json = json.loads(response_string)
json_formatted_str = json.dumps(user_json, indent=2)
print(json_formatted_str)

You can use this like this:

# python3.7 zoom-user.py user@domain.com

{
  "id": "xxxxxxxxxxx",
  "first_name": "John",
  "last_name": "Smith",
  "email": "user@domain.com",
  "type": 2,
  "role_name": "Admin",
  "pmi": 111111111,
  "use_pmi": false,
  "personal_meeting_url": "https://myschool.zoom.us/j/111111111?pwd=UmVTASDFc3pXUTNpOFZNb34A09",
  "timezone": "America/Chicago",
  "verified": 0,
  "dept": "",
  "created_at": "2020-04-13T02:42:31Z",
  "last_login_time": "2020-05-22T19:38:06Z",
  "last_client_version": "4.6.20561.0413(mac)",
  "pic_url": "https://lh3.googleusercontent.com/a-/AOh14GgQadfagrA8l4U9fhsbZ1hOTMSvDxE3gGQ",
  "host_key": "111861",
  "jid": "3timqwabrvs00ayurhg_ha@xmpp.zoom.us",
  "group_ids": [
    "qCpDwoo5T1qS4cn8RSy8rQ"
  ],
  "im_group_ids": [],
  "account_id": "fhsbZ1cOTMSvDxE3g",
  "language": "en-US",
  "phone_country": "",
  "phone_number": "",
  "status": "active",
  "job_title": "",
  "location": ""
}

Note: I’ve redacted/change all of the ID and info above for obvious reasons.

Getting a list of all webinars panelists

This Python script lists all of the panelist names and email addresses.

import jwt   # pip3 install pyjwt --user
import http.client
import datetime
import json
import sys

api_key = '********' # replace with credentials from
api_sec = '********' # your app in Zoom Marketplace

# generate JWT
payload = {
'iss': api_key,
'exp': datetime.datetime.utcnow() + datetime.timedelta(hours=2)
}

jwt_encoded = str(jwt.encode(payload, api_sec), 'utf-8')

# call API: get webinar panelists
conn = http.client.HTTPSConnection("api.zoom.us")
headers = {
'authorization': "Bearer %s" % jwt_encoded,
'content-type': "application/json"
}
webinarid = str(sys.argv[1])
conn.request("GET", "/v2/webinars/" + webinarid + "/panelists", headers=headers)
res = conn.getresponse()
response_string = res.read().decode('utf-8')
response_json = json.loads(response_string)

for panelist in response_json["panelists"]:
    print(panelist["name"],  panelist["email"])

Here is an example of the script in action listing panelists for webinar id 91119118113:

# python3.7 zoom-webinar-panelists.py 91119118113
John Smith jsmith@domain.com
Jane Smith jane.smith@anotherdomain.com

Adding Panelists to a Webinar

Next is a Python script to add a panelist to a webinar. It takes the webinar ID, panelist name and email address as parameters.

import jwt   # pip3 install pyjwt --user
import http.client
import datetime
import json
import sys

api_key = '********' # replace with credentials from
api_sec = '********' # your app in Zoom Marketplace

# generate JWT
payload = {
'iss': api_key,
'exp': datetime.datetime.utcnow() + datetime.timedelta(hours=2)
}

jwt_encoded = str(jwt.encode(payload, api_sec), 'utf-8')

# call API: add panelist
conn = http.client.HTTPSConnection("api.zoom.us")
headers = {
'authorization': "Bearer %s" % jwt_encoded,
'content-type': "application/json"
}

num_arguments = len(sys.argv)-1
webinar = str(sys.argv[1])
email = sys.argv[num_arguments]
name = ""
i = 2
while i < num_arguments:
    name = name + sys.argv[i] + " "
    i = i+1
name = name.rstrip()
print(name, email,webinar)
body = "{\"panelists\":[{\"name\":\"" + name + "\",\"email\":\"" + email + "\"}]}"

webinar = str(sys.argv[1])
conn.request("POST", "/v2/webinars/" + webinar + "/panelists", body, headers=headers)
res = conn.getresponse()
response_string = res.read().decode('utf-8')

Here is an exampling of using the script to add “John Q. Public” john.q.public@domain.com to webinar id 91119118113:

python3.7 zoom-webinar-panelists-add.py 91119118113 John Q. Public john.q.public@domain.com

{"id":"91119118113","updated_at":"2020-05-25T23:25:22Z"}

Closing Thoughts

Using these ideas in these two scripts, I can easily copy panelists from one webinar to another. Hopefully this is enough to get you started using the Zoom API. As I mentioned at the start, I think it would be awesome to have a tool like GAM but for Zoom. I’d much rather manage Zoom from the command line.

Cropping PDFs with AppleScript

Due to Coronavirus the printing of our yearbooks was delayed, so we decided to distribute it digitally until the print version was available. There are lots of tools, like ISSUU, Uberflip, or even Google Drive that can do a decent job of displaying a PDF, password protecting it, and preventing it from being downloaded. The challenge was to get the galley proofs with crop marks, etc. into the right format. Further complicating things were that the odd/even pages had crop markets that were offset, so they had to be cropped differently.

Here is an example of a page with the dotted crop marks and the metadata on the bottom.

Fortunately Adobe Acrobat is scriptable. Normally, I tend to avoid installing Acrobat, in favor of using Preview, but its support for scripting is surprisingly good. The follow AppleScript crops odd/even pages differently and solved my problem. (based on https://macscripter.net/viewtopic.php?id=32586)

tell application "Adobe Acrobat"
activate tell active doc repeat with i from 1 to count of pages tell page i set {L, T, R, B} to media box if ((i mod 2) = 0) then set crop box to {L + 37, T - 39, R, B + 156} else set crop box to {L, T - 39, R - 37, B + 156} end if end tell end repeat end tell end tell

Virtual Graduations & End of Year Events in the time or Coronavirus: Stream Deck

In a previous post, I mentioned that I used Wirecast to do live video production of events I broadcast using Zoom. Normally, I’d like to have someone helping me, but that’s much harder when everyone is remote. Fortunately, I found the Elgato StreamDeck to help. It’s a USB hardware device with a bunch of buttons. Each button can trigger an action. Each button acts as a little screen that can be customized. There is a rich ecosystem of plugins for Strem Deck that make it work with other tools.

Here is what it looks like from a recent production:

Most of the blue buttons are clips in Wirecast and the bottom left button is the “play” button for Wirecast. The little red dot on the top left clip shows that it is live. A green dot on another clip shows that it is “next.” I really like the Wirecast plugin for Stream Deck since it gives feedback on what is playing (the red/green dots).

On the bottom right are five buttons that control Zoom (focus/ turn on/off webcam, turn on/off mic, show participants window, show chat window). They are based on the work of LostDomain and make use of Keyboard Maestro, and it’s plug-in for StreamDeck.

The black buttons are for when we went live and I muted the virtual webcam so that the active speaker became the live speaker.

What is nice is that I can control Wirecast while it is in the background and I’m managing Zoom.

Here is another picture of the StreamDeck from another production:

For this production I didn’t use the virtual webcam and instead played videos in QuickTime. The video quality seemed to be a bit better when sharing my screen rather than using a virtual webcam, and video quality was really important for this production.

The two columns of buttons on the right are mostly to control Zoom (except the bottom two). I added a shortcut to the “Share” button and “Record” (again, making use of Keyboard Maestro). The bottom right is a clock that shows the time (including seconds).

All of the other buttons are AppleScripts to control QuickTime Player and Preview. One of the challenges of using QuickTime to play videos in front of an audience (Zoom or in-person) is that they can see you futzing around opening the video and going full-screen. Using AppleScript can really minimize this, especially when triggering it from the StreamDeck.

Here is a little AppleScript to open and play fullscreen a Quicktime video (based on https://gist.github.com/biojazzard/2829190):

set unixpath to "/Users/ssimon/Desktop/PA Night Long.mp4"
set macfile to (POSIX file unixpath)
tell application "QuickTime Player"
	activate
	delay 0.5
	open file macfile
	set looping of document 1 to false
	--FullScreen
	--FullScreen
	--set presenting of document 1 to true
	--GetBounds
	present document 1
	play document 1
	
end tell

I also wanted to be able to pause the video:

tell application "QuickTime Player"
	activate
	delay 0.5
	pause document 1
end tell

And to play:

tell application "QuickTime Player"
	activate
	delay 0.5
	present document 1
	play document 1
end tell

I also wanted to close a video (QuickTime Player will open additional videos in tabs when in fullscreen):

tell application "QuickTime Player"
	activate
	close document 4
end tell

You can also open a document in Preview in fullscreen:

set unixpath to "/Users/ssimon/Desktop/roman.jpg"
set macfile to (POSIX file unixpath)

tell application "Preview"
	activate
	open file macfile
	tell application "System Events"
		keystroke "f" using {control down, command down}
	end tell
end tell

The end result is that nobody sees me moving my mouse around to control QuickTime Player or Zoom and I can rapidly play a video.

For the show, I launched the video (via a StreamDeck button / AppleScript), immediately paused it (via a StreamDeck button / AppleScript), used the Stream Deck “Share” button for Zoom to open the screen sharing window, picked the QuickTime Player window, checked “Optimize for full-screen video clip” and “Share computer sound”, and then the “Share Screen” button. Then I hit “play” (via a StreamDeck button / AppleScript). When the video was over, I stopped sharing my screen and let the speaker talk, and when he was done, I repeated the process all over for the next video.

In the future, I’ll probably change the AppleScript not to play the video and leave it paused, to simplify the process.

I haven’t had a chance to play with it, but there is a mobile version of the StreamDeck software for Android and iOS which looks like it could be a cheaper alternative to buying a StreamDeck.

 

Virtual Graduations & End of Year Events in the time or Coronavirus: Zoom

I’m the tech guy for a private K-12 school and have had to do a lot of work to pull off the various end of year events. Many other people have shared content that I’ve found helpful, and I hope in posting this someone else might find it useful. Mostly, I write these blogs for me, to document stuff and help get my thoughts together. I’m going to do a series of posts on the tools and techniques I’ve used. I’ll start with Zoom.

Despite privacy and security concerns, we have been using Zoom for many of our meetings and classes. Many of those concerns are addressed by paying for a Zoom for Education account which has a better privacy policy and security features.

Our licensed Zoom accounts support 300 attendees, but that isn’t enough for end-of-year events. I have upgraded my account to support 1,000 users and we also have a 500 user Webinar account. Zoom can also stream to an outside service like YouTube Live. We have invited students to join an event via Zoom, and parents, and other adults to use the YouTube Live stream.

Webinar vs. Meeting

The main differences between the more expensive Webinar version of Zoom and the regular version:

  1. Meeting attendees cannot share video or audio. You can enable users to unmute themselves, but by default attendees are passive participants.
  2. Webinars have a new role of “Panelist” who can share their audio, video (with permission), or screen. They can also use the chat feature to chat with all panelists, or all panelists and attendees.
  3. Webinar supports a Q&A function that enables attendees to ask questions and for panelists to answers those questions either publicly or privately.
  4. Chat in webinar has some different options, including allowing attendees to only post to attendees or everyone. Direct chat between individual participants is not allowed.
  5. Webinar allows you to force what view attendees see (host view, active speaker view, gallery/grid view).

We use the webinar version for our various performing arts shows. By setting “Set video layout for attendees” to “Follow host view mode” and setting “Hide Non-Video Participants” in the host’s Zoom application settings. This allows us to rapidly hide/show webcams of people we want on stage. Our improv groups used this to great effect, constantly changing who was on stage throughout the show.

For end of year events, if you want everyone to see each other then you should use the standard version of Zoom. If you want to control what attendees see, you should use Webinar.

Virtual Webcams

One very useful trick is to use virtual webcam software. This basically adds a fake webcam to your computer that you can use in Zoom. You can use the virtual webcam to play recorded video or output from live video production software. I’ve used this to mix recorded video into a Zoom without resorting to screen sharing (which has drawbacks, including really slow transitions when starting/stopping sharing, and that it takes over the entire screen).

I primarily use Telestream Wirecast for both live video production and to act as a virtual webcam. They have a tutorial on “Using Virtual Camera Out in a Zoom Meeting” which gives a pretty good overview of the process. I’ve also experimented with using ManyCam.

I’ve used Wirecast and a virtual webcam to have slides and video side-by-side in some end of year events. This allows me to make the speaker (in the video) the same size or larger than the slides.

WARNING: security features in macOS Catalina and newer versions of Zoom broke virtual webcam support. Apparently, there is a new version of Zoom that is supposed to come out today (5/22) that fixes this. Prior to that, your only choices were to downgrade to Zoom 4.6.x or to run the following from the command line:

codesign --remove-signature /Applications/zoom.us.app/

Streaming to YouTube Live

Sometimes it is really helpful to stream to more people than your Zoom account can support, provide an option for those who don’t want to use Zoom to attend, or to be able to embed a stream within a webpage. Zoom has documentation on how to “Streaming a Meeting or Webinar on YouTube Live.”

Note: you will have to enable your YouTube account for live streaming and it can take 24 hours for the change to go into effect.

For an event, you’ll probably want to know the URL of the stream ahead of time. For that you’ll have to enable Custom Live Streaming instead of using the built-in support for YouTube Live within Zoom.

WARNING: be careful in testing your setup between Zoom and YouTube Live. If you start streaming and then stop, you won’t be able to use that URL again. I now send out links to a landing page on our website instead of the direct link to YouTube. That way I can change the link at the last second if I need to.

 

Using Microsoft PowerBI with the Blackbaud API

At the recent Blackbaud K-12 Conference, I spent some time with Grahm Getty who did a presentation on using PowerBI with the Blackbaud ON products.  The ability to visualize your data is really powerful and can be used to find insights and problems in your data.  Grahm and I spent some time figuring out how to use the Blackbaud API to get the data in real-time into PowerBI so that it is always up-to-date and doesn’t require exporting and using CSV files.  We were able to get this to work with the desktop version of PowerBI, but it doesn’t seem to work with the web version.  I haven’t given up on that, but for now I thought I would share a tutorial on how to get it to work with the desktop version of PowerBI.  What follows is a tutorial that assume you know nothing of the Blackbaud API nor Microsoft PowerBI.

Prerequisites
This tutorial requires:

  1. You have the Blackbaud API enabled.  There is an annual fee for this, so you need to contact your account manager if you do not already have it.  It comes bundled with ODBC access, so if you have that, then you have API access.
  2. A machine running some modern version of Windows.  It can be emulated in VMware or Parallels if your a Mac user.  PowerBI requires Windows.

Setup within the Blackbaud “ON” products

  1. Create a new user within the “ON” products to use to make API calls.  It is best practice to use a separate account to make API calls.
    1. As a Platform Manager, go to Core>Users/Access>Profile
    2. Click “Add New User”
    3. Fill out the Last Name, First Name, Username and pick and confirm a temporary password.  I used “PowerBI” as the First Name, “API” as the Last Name and “powerbi-api” as the username.
    4. In the screen that follows, edit the user.
    5. Under “System Information” for the user we just created, click “Role Membership”.
    6. Click “Edit”.
    7. Enable “Web Services API Manager” and click on “Save & Exit”.
    8. Sign Out of the ON products and login with your newly created account.
    9. When you login, you will be forced to change your password.  Change it.
  2. Sign Out of the ON products and login to your normal account.
  3. Go to lists (Faculty>My Day>Schedule & Performance>Lists>View All).
  4. At this point, we need to create the list that we want to use in PowerBI.  For the purposes of this demo, we will use one based on a list template.
    1. Click on List Templates.
    2. Under “Template Category” pick “Constituent Information”
    3. Find “Students by Current Grade” and click on “View/Copy”
    4. Under name, give it a name (I used “PowerBI student information”)
    5. Let’s add Ethnicity.
    6. Click on the “Select Objects” tab.
    7. Under “Constituent Information”, click “User Citizenship/Residency”
    8. Click on the “Display Fields” tab.
    9. At the bottom of the screen, click on “Select Fields…”
    10. Expand “User Base” by clicking on the +
    11. Expand “User Citizenship/Residency”
    12. Check the checkbox next to “Ethnicity” and “Citizenship” and then click the “Select” button.
    13. Click the “Preview” button on the bottom right of the screen to preview the list.
    14. Click “Save & Exit” on the top right.
  5. Next we need to get the list ID number so that we can use it in an API call.  This ID number is somewhat hidden.
    1. Control-click on the “Run” link next to the list we just created.
    2. Select “Copy Link” from the pop-up menu that appears.
    3. Go to the text editor of your choice and paste the link.  (note: if you only see “Run” when you paste you’ll need to paste it as unformatted text (on a Mac: command-option-shift-v).
    4. You should see something like: javascript:__pdL(‘52586′,’Advanced%20List:%20API%20-%20list%20example’,%20’1′,%20’~slid=49748~ml=False~sln=API%20-%20list%20example’,%20”,%20’False’,%20’0′,%20”,%20’default.aspx’)
    5. after “slid=” there is a number (in the example 49748).  This is the list ID.  Make a note of your list ID.
    6. Next we have to grant the API account we setup earlier with access to the list we want to use.
      1. Click on the “User Access” link to the right of the link. 
      2. Click on “Add User(s)”.
      3. Search for the API user you created.  (I searched for last name of “API). 
      4. To the left of the API user in the search results, click on the blue “>>” link to move it to “Added Users” and then click on “Save & Exit”.
      5. Leave it with the defaults of “Run” and press “Save & Exit” again.

Microsoft PowerBI Setup

  1. If you do not have the desktop version of Microsoft PowerBI installed on your Windows machine, go to https://powerbi.microsoft.com and click on “Sign up for free” in the top right.  Then click “download free”.  Install it on your machine.
  2. Launch PowerBI Desktop (this takes a while on my machine).
  3. Cancel out of the splash screen. 
  4. Press the “Edit Queries” button in the tool ribbon. 
  5. Press the “New Source” button and then select “Blank Query” and press the “Connect” button.
  6. Right click on the new query that was created and select “Advanced Editor”.
  7. PowerBI makes use of a query language that can be used for advanced queries.  Because of how the Blackbaud ON API handles authentication, we need to use this advanced query language.  Copy the code below into the advanced editor, replacing the code in red with your school’s ON website address, API username/password, and the list ID.  When you edit the text, make sure you don’t use smart quotes.

    let
    Source = Json.Document(Web.Contents(“https://myschool.myschoolapp.com/api/authentication/login?username=apiusername&password=apipassword&format=json”)),T = Source[Token],
    GetList = Function.InvokeAfter(()=>Json.Document(Web.Contents(“https://myschool.myschoolapp.com/api/list/listIDnumber/?t=” & T & “&format=json”)), #duration(0,0,0,5))
    in
    GetList

  8. Click on the “Done” button in the Advanced Editor. 
  9. At this point there are a few setting changes related to the PowerBI security model that I don’t quite understand. Next to “Please specify how to connect” press “Edit Credentials.”
  10. Pick “Anonymous” from the list on the left and press “Connect”. 
  11. Next, you should see an alert “Information is required about data Privacy.”  Press the “Continue button. 
  12. In the pop-up to the right of your website URL pick “Public” then press “Save”.
  13. You should then see results as a column of “Records”.  We will need to expand those. 
  14. Right click on the column header and select “To Table”.
  15. Then an icon will appear on the right side of the column header.  Click on it and you will be able to pick what columns of data you want to view from the record.  Uncheck “Use original column name as prefix” and then click “OK”.
  16. Press the “Close & Apply” button on the left side of the tool ribbon.
  17. Now it’s time for us to build our data visualization!
  18. On the right side of the screen, click on the first icon “Stacked bar chart”.
  19. From the right side of the screen, drag the “GradYear” field to the Axis box, and drag “Gender” to “Value” and then drag it again to “Legend”.
  20. We should now see a chart.  My sample data is pretty boring.
  21. Make sure you’ve clicked on the chart and then click on the paint roller icon.
  22. Click on “Data colors” on the right and pick a light pink for “F” and a light blue for “M”. 
  23. Turn on “Data Labels”, change the font to a larger size and switch the position to “Inside Center”.
  24. Resize the box around the chart to make it bigger.
  25. Now click on the stacked bar chart icon again, and put “GradYear” in the axis and “User ID” in the value field.  Note that it automatically switches “User ID” to “Count of User ID”.
  26. Next click on the “Card” icon (it has the 123 on it).  Drag User ID to it’s value box and click on the triangle next to it to switch it to “Count of User ID (Distinct)”.
  27. Now try clicking on one of the graduation years in the chart of them and notice how the count we just added changed and what it did to the other chart.
  28. Finally save your file.

Photo Directory Example

Let’s look at a more complicated example of using the Blackbaud K12 API with the “ON” products. Recently, I had to develop a photo directory, with pictures of faculty/staff and some information (job title, room number, phone number, e-mail, etc.) about them.  There isn’t a built-in report in the Blackbaud products for this, nor is it easy to export this data along with photos. To solve this, I wrote a simple PHP script, the extracts the data/photos using the API, formats them, and creates a web page that can easily be printed to PDF or sent to a printer.

At the bottom of this post, I’ll include the full source to this solution, but first I’ll walk you through the code below.

First, a little HTML/CSS to try to make the HTML table deal with page breaks, etc. a little better:

<html>
 <head>
 <style type="text/css">
 table { page-break-inside:auto }
 tr { page-break-inside:avoid; page-break-after:auto }
 thead { display:table-header-group }
 tfoot { display:table-footer-group }
 </style>
 </head>

Next, I need to set the execution timeout to something a little longer.  This script is going to make a lot of API calls, resize a ton of photos, and generally take a long time to run.  I’ll also keep track of how long it takes for the script to run and load up the Httpful library I mention in my last post.

error_reporting( error_reporting() & ~E_NOTICE );
ini_set('max_execution_time', 300); # set to five minutes (300 seconds)
$time_start = microtime(true); # start timing how long this script takes
include_once ('./httpful.phar'); # use Httpful http://phphttpclient.com/

Next, we get an authentication token:

$schoolWebsite = "https://latinschool.myschoolapp.com"; # website used to login to the "ON" products
$apiUser = "my_username";
$apiPassword = "my_password";

// Get authentication token for the Blackbaud K12 API
$uri = "$schoolWebsite/api/authentication/login/?username=". $apiUser . "&password=" . $apiPassword . "&format=json";
$response = \Httpful\Request::get($uri)->expectsJson() ->send();
$token = $response->body->{"Token"};

We will set some variables determine how many columns we want, the height of the table cells and image, and an image to use as a generic image when the user doesn’t have a profile photo.

#Some parameters for the photo directory
$numColums = 4; # How many columns do we want?
$tdHeight = 210; # Height of the table cell that holds the entry
$imgHeight = 170; # Image height
$genericPhotoURL = "http://vignette4.wikia.nocookie.net/detectiveconan96/images/7/72/Generic_Male_Profile.jpg/revision/latest?cb=20140709000724"; # This photo will show if we don't have a user profile 

Next, we’ll do a little error checking to see if our login effort worked and then start up our table:

if (strpos($token, 'Invalid') !== false) {
 echo "Invalid Login.<br>";
} else {
 echo "<table border=0>";
 $currentColumn = 1;

We are going to use the List API to gather the users we want. If you want to use this script, you’ll have to create your own list, and use it’s list ID in the API call. The comments have tips on how this list is setup.

 # Use Blackbaud K12 list API to gather nonteaching staff and teachers.
 # You can get the listid by hovering over edit and look for slid= in the link
 # on the website. Using lists is faster than doing individual API calls, 
 # and they can be edited by end users.
 #
 # If you'd like to generate a photo directory of something else, you can
 # change this list.
 #
 # The following objects were selected for this list:
 # + User Base
 # + User School Defined Fields
 # + User Role
 # + User Detail
 #
 # The following fields were selected for "display" (Display As)
 # + User Base.User ID (UserID)
 # + User Base.First Name (FirstName)
 # + User Base.Last Name (LastName)
 # + User Base.E-Mail (email)
 # + User Base.Host ID (HostID)
 # + User School Defined Fields. Defined 2 (latinid)
 #
 # The list has the following filter:
 # User Role.Role any of Non-Teaching Staff,Teacher
 #
 # The list is ordered by: (Change, to change sort order for photo directory)
 #
 # User Base.Last Name Ascending
 # Then By
 # User Base.First Name Ascending
 #
 
 $uri = $schoolWebsite . "/api/list/46815/?t=" . $token . "&format=json";
 $response = \Httpful\Request::get($uri)-> send();
 $employees = $response->body;

Now, we will loop through the list results and grab information about each person in the list:

 $i = 0;
 
 foreach ($employees as $employee) {
 $i++;
 $fname = $employee->{"FirstName"};
 $lname = $employee->{"LastName"};
 $whsid = $employee->{"UserID"};
 $hostid = $employee->{"HostID"};

Next, we will use the /user/extended API call to get more details about each user, including a URL to where their profile photo is stored.

 # get details on this employee via the Blackbaud K12 API
 # /user/extended uses the system ID or "UserID" in the call
 # to get detailed information on an individual user. It can
 # give us access to data we cannot otherwise see, such as the 
 # URL to their profile photo.
 $uri = $schoolWebsite . "/api/user/extended/" . $whsid . "/?t=" . $token . "&format=json";
 $response = \Httpful\Request::get($uri)-> send();
 
 # Let's set some variables with the data from user/extended:
 $employeeDetail = $response->body; 
 $uname = $employeeDetail->{"UserName"};
 $employeeLatinID = $employeeDetail->{"CustomField2"}; # My school stores our ID number in this custom field
 $employeePhotobookCode = $employeeDetail->{"CustomField10"}; # Code to use to omit people from the directory
 $employeeProfilePhoto = $employeeDetail->{"ProfilePhoto"};
 $employeePhotoURL = $employeeProfilePhoto->{"LargeFilenameUrl"};
 if($employeePhotoURL !== "") {
 $employeePhotoURL = $schoolWebsite . $employeePhotoURL; # The photo URL needs the school website added to it
 }
 
 # Job Titles are harder. They are contained within an array called OccupationList.
 # A single person can have multiple occupations. We are going to go through the
 # array and look at each occupation. We don't have a great well to tell which is 
 # the right one to use. In my example, I'm going to look for a business name 
 # matching my school name and use the title found there. Obviously, if there are
 # more then one occupation listed with the same employer name, it will overwrite
 # the data, but hopefully, we don't have the same person listed more than once.
 # Finally, as a fail safe, it will set it to the last title found, if we haven't
 # set the job title yet.
 
 $employeeOccupationList = $employeeDetail->{"OccupationList"};
 $employeeTitle = "";
 foreach ($employeeOccupationList as $employeeOccupation) {
 $employeeBusinessName = $employeeOccupation->{"BusinessName"};
 $employeeOccupationTitle = $employeeOccupation->{"JobTitle"};
 
 if (($employeeBusinessName = "Latin School of Chicago") || ($employeeBusinessName = "The Latin School of Chicago")) {
 $employeeTitle = $employeeOccupationTitle;
 } else {
 if ($employeeTitle == "") { 
 // If we haven't set something yet for the user, let's try setting this title, 
 // even though the business name isn't set right
 $employeeTitle = $employeeOccupationTitle;
 }
 }
 }
 
 # Let's grab their work address. It's stored in an array, but we never
 # have more than one address. This will loop through it and grab the
 # last (hopefully only) value. If you have multiple values here, you'll
 # have to come up with a better way of dealing with this.
 $employeeOffice = "";
 $employeeAddressList = $employeeDetail->{"AddressList"};
 foreach ($employeeAddressList as $employeeAddress) {
 $employeeAddressType = $employeeAddress->{"address_type"};
 $employeeAddressLn1 = $employeeAddress->{"AddressLine1"};
 
 if($employeeAddressType == "Business/College") {
 $employeeOffice = $employeeAddressLn1;
 }
 }
 
 # Now let's grab their work phone number. Same deal as address.
 $employeeOfficePhone = "";
 $employeeOfficePhoneList = $employeeDetail->{"PhoneList"};
 foreach ($employeeOfficePhoneList as $employeeOfficePhoneItem) {
 $employeeOfficePhoneType = $employeeOfficePhoneItem->{"Type"};
 $employeeOfficePhoneNumber = $employeeOfficePhoneItem->{"PhoneNumber"};
 
 if($employeeOfficePhoneType == "Business/College") {
 $employeeOfficePhone = $employeeOfficePhoneNumber;
 $employeeOfficePhone = str_replace(' ', '', $employeeOfficePhone);
 $employeeOfficePhone = str_replace(')', '.', $employeeOfficePhone);
 $employeeOfficePhone = str_replace('(', '', $employeeOfficePhone); 
 $employeeOfficePhone = str_replace('-', '.', $employeeOfficePhone); 
 }
 }

You’ll notice we have to jump through some hoops with phones, addresses and titles, since the API returns a list (array) of responses for each.

We’ve made use of user defined field 10 to exclude certain people from the directory. If there is a value in the field, we skip that person.

 if($employeePhotobookCode == "") { 
 # We use CustomField10 as a place to exclude folks from the photo directory
 
 if($currentColumn == 1) {
 echo "<tr>";
 }

 echo "<td valign=top align=center height=$tdHeight width=25%>";
 # This is the table cell with all of the user data

Here is the code block that produces the content for each directory entry. We run the photo through another script (img.php) which resizes and crops them.

 # First the photo: 
 if($employeePhotoURL !== "") {
 echo "<a href=\"$uri\"><img src=\"img.php?url=$employeePhotoURL\" height=\"$imgHeight\"></a><br>\n";
 # img.php is a script that crops and resizes the images to a standard size
 } else {
 echo "<a href=\"$uri\"><img src=\"img.php?url=$genericPhotoURL\"></a><br>\n";
 }
 echo "<font size=-1>$fname $lname<br></font>\n";
 echo "<font size=-2>$employeeTitle<br></font>\n";
 echo "<font size=-2>$employeeOffice &nbsp; &nbsp; $employeeOfficePhone<br></font>\n";

A little cleanup of our HTML table and the end of our script:

 if($currentColumn == $numColums) {
 echo "</td></tr>";
 $currentColumn = 1; 
 } else {
 echo "</td>";
 $currentColumn++;
 }

 }
 
 if($i>2500) { break;} # Stop if we have way too many results. Can decrease number to debug 
 } 
 
 if ($currentColumn == $numColums) {
 echo "</table>";
 } else {
 echo "</tr></table>";

 }
}

echo "<br><br>Employees Found: $i<br>";
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "Run time: " . round($time,2) . " s";
?>

</html>

 

Here is the script in it’s entirety.

<html>
 <head>
 <style type="text/css">
 table { page-break-inside:auto }
 tr { page-break-inside:avoid; page-break-after:auto }
 thead { display:table-header-group }
 tfoot { display:table-footer-group }
 </style>
 </head>

<?php
#------------------------------------------------------------------------------ 
# Copyright 2016 Shandor Simon (shandor at gmail dot com) 
# http://duff.io 
# 
# This work is licenced under the Creative Commons 
# Attribution-NonCommercial-ShareAlike 2.5 License. 
# To view a copy of this licence, visit 
# http://creativecommons.org/licenses/by-nc-sa/2.5/ 
# or send a letter to Creative Commons, 559 Nathan Abbott Way, Stanford, 
# California 94305, USA. 
#
# This code is provided as-is with no warranty expressed or implied.
# It is possible to change or delete data using this API. You should
# make use of it at your own discretion/risk. You are solely responsible 
# for its use.
#
#------------------------------------------------------------------------------ 
# Version 1.0.1 - 2016-07-01
#------------------------------------------------------------------------------ 
# Blackbaud K12 "ON" products API example in PHP to generate a photo directory
# that is intended to be printed

error_reporting( error_reporting() & ~E_NOTICE );
ini_set('max_execution_time', 300); # set to five minutes (300 seconds)
$time_start = microtime(true); # start timing how long this script takes
include_once ('./httpful.phar'); # use Httpful http://phphttpclient.com/

$schoolWebsite = "https://latinschool.myschoolapp.com"; # website used to login to the "ON" products
$apiUser = "my_username";
$apiPassword = "my_password";
$debug = true;
$genericPhotoURL = "http://vignette4.wikia.nocookie.net/detectiveconan96/images/7/72/Generic_Male_Profile.jpg/revision/latest?cb=20140709000724"; # This photo will show if we don't have a user profile 

#Some parameters for the photo directory
$numColums = 4; # How many columns do we want?
$tdHeight = 210; # Height of the table cell that holds the entry
$imgHeight = 170; # Image height

// Get authentication token for the Blackbaud K12 API
$uri = "$schoolWebsite/api/authentication/login/?username=". $apiUser . "&password=" . $apiPassword . "&format=json";
$response = \Httpful\Request::get($uri)->expectsJson() ->send();
$token = $response->body->{"Token"};

if (strpos($token, 'Invalid') !== false) {
 echo "Invalid Login.<br>";
} else {
 echo "<table border=0>";
 $currentColumn = 1;
 
 # Use Blackbaud K12 list API to gather nonteaching staff and teachers.
 # You can get the listid by hovering over edit and look for slid= in the link
 # on the website. Using lists is faster than doing individual API calls, 
 # and they can be edited by end users.
 #
 # If you'd like to generate a photo directory of something else, you can
 # change this list.
 #
 # The following objects were selected for this list:
 # + User Base
 # + User School Defined Fields
 # + User Role
 # + User Detail
 #
 # The following fields were selected for "display" (Display As)
 # + User Base.User ID (UserID)
 # + User Base.First Name (FirstName)
 # + User Base.Last Name (LastName)
 # + User Base.E-Mail (email)
 # + User Base.Host ID (HostID)
 # + User School Defined Fields. Defined 2 (latinid)
 #
 # The list has the following filter:
 # User Role.Role any of Non-Teaching Staff,Teacher
 #
 # The list is ordered by: (Change, to change sort order for photo directory)
 #
 # User Base.Last Name Ascending
 # Then By
 # User Base.First Name Ascending
 #
 
 $uri = $schoolWebsite . "/api/list/46815/?t=" . $token . "&format=json";
 $response = \Httpful\Request::get($uri)-> send();
 $employees = $response->body;
 
 $i = 0;
 
 foreach ($employees as $employee) {
 $i++;
 $fname = $employee->{"FirstName"};
 $lname = $employee->{"LastName"};
 $whsid = $employee->{"UserID"};
 $hostid = $employee->{"HostID"};
 
 # get details on this employee via the Blackbaud K12 API
 # /user/extended uses the system ID or "UserID" in the call
 # to get detailed information on an individual user. It can
 # give us access to data we cannot otherwise see, such as the 
 # URL to their profile photo.
 $uri = $schoolWebsite . "/api/user/extended/" . $whsid . "/?t=" . $token . "&format=json";
 $response = \Httpful\Request::get($uri)-> send();
 
 # Let's set some variables with the data from user/extended:
 $employeeDetail = $response->body; 
 $uname = $employeeDetail->{"UserName"};
 $employeeLatinID = $employeeDetail->{"CustomField2"}; # My school stores our ID number in this custom field
 $employeePhotobookCode = $employeeDetail->{"CustomField10"}; # Code to use to omit people from the directory
 $employeeProfilePhoto = $employeeDetail->{"ProfilePhoto"};
 $employeePhotoURL = $employeeProfilePhoto->{"LargeFilenameUrl"};
 if($employeePhotoURL !== "") {
 $employeePhotoURL = $schoolWebsite . $employeePhotoURL; # The photo URL needs the school website added to it
 }
 
 # Job Titles are harder. They are contained within an array called OccupationList.
 # A single person can have multiple occupations. We are going to go through the
 # array and look at each occupation. We don't have a great well to tell which is 
 # the right one to use. In my example, I'm going to look for a business name 
 # matching my school name and use the title found there. Obviously, if there are
 # more then one occupation listed with the same employer name, it will overwrite
 # the data, but hopefully, we don't have the same person listed more than once.
 # Finally, as a fail safe, it will set it to the last title found, if we haven't
 # set the job title yet.
 
 $employeeOccupationList = $employeeDetail->{"OccupationList"};
 $employeeTitle = "";
 foreach ($employeeOccupationList as $employeeOccupation) {
 $employeeBusinessName = $employeeOccupation->{"BusinessName"};
 $employeeOccupationTitle = $employeeOccupation->{"JobTitle"};
 
 if (($employeeBusinessName = "Latin School of Chicago") || ($employeeBusinessName = "The Latin School of Chicago")) {
 $employeeTitle = $employeeOccupationTitle;
 } else {
 if ($employeeTitle == "") { 
 // If we haven't set something yet for the user, let's try setting this title, 
 // even though the business name isn't set right
 $employeeTitle = $employeeOccupationTitle;
 }
 }
 }
 
 # Let's grab their work address. It's stored in an array, but we never
 # have more than one address. This will loop through it and grab the
 # last (hopefully only) value. If you have multiple values here, you'll
 # have to come up with a better way of dealing with this.
 $employeeOffice = "";
 $employeeAddressList = $employeeDetail->{"AddressList"};
 foreach ($employeeAddressList as $employeeAddress) {
 $employeeAddressType = $employeeAddress->{"address_type"};
 $employeeAddressLn1 = $employeeAddress->{"AddressLine1"};
 
 if($employeeAddressType == "Business/College") {
 $employeeOffice = $employeeAddressLn1;
 }
 }
 
 # Now let's grab their work phone number. Same deal as address.
 $employeeOfficePhone = "";
 $employeeOfficePhoneList = $employeeDetail->{"PhoneList"};
 foreach ($employeeOfficePhoneList as $employeeOfficePhoneItem) {
 $employeeOfficePhoneType = $employeeOfficePhoneItem->{"Type"};
 $employeeOfficePhoneNumber = $employeeOfficePhoneItem->{"PhoneNumber"};
 
 if($employeeOfficePhoneType == "Business/College") {
 $employeeOfficePhone = $employeeOfficePhoneNumber;
 $employeeOfficePhone = str_replace(' ', '', $employeeOfficePhone);
 $employeeOfficePhone = str_replace(')', '.', $employeeOfficePhone);
 $employeeOfficePhone = str_replace('(', '', $employeeOfficePhone); 
 $employeeOfficePhone = str_replace('-', '.', $employeeOfficePhone); 
 }
 }
 
 if($employeePhotobookCode == "") { 
 # We use CustomField10 as a place to exclude folks from the photo directory
 
 if($currentColumn == 1) {
 echo "<tr>";
 }

 echo "<td valign=top align=center height=$tdHeight width=25%>";
 # This is the table cell with all of the user data
 
 # First the photo: 
 if($employeePhotoURL !== "") {
 echo "<a href=\"$uri\"><img src=\"img.php?url=$employeePhotoURL\" height=\"$imgHeight\"></a><br>\n";
 # img.php is a script that crops and resizes the images to a standard size
 } else {
 echo "<a href=\"$uri\"><img src=\"img.php?url=$genericPhotoURL\"></a><br>\n";
 }
 echo "<font size=-1>$fname $lname<br></font>\n";
 echo "<font size=-2>$employeeTitle<br></font>\n";
 echo "<font size=-2>$employeeOffice &nbsp; &nbsp; $employeeOfficePhone<br></font>\n";
 
 if($currentColumn == $numColums) {
 echo "</td></tr>";
 $currentColumn = 1; 
 } else {
 echo "</td>";
 $currentColumn++;
 }

 }
 
 if($i>2500) { break;} # Stop if we have way too many results. Can decrease number to debug 
 } 
 
 if ($currentColumn == $numColums) {
 echo "</table>";
 } else {
 echo "</tr></table>";

 }
}

echo "<br><br>Employees Found: $i<br>";
$time_end = microtime(true);
$time = $time_end - $time_start;
echo "Run time: " . round($time,2) . " s";
?>

</html>

Here is the img.php script that is used to crop and resize the images:

asd
<?php

# https://github.com/Nimrod007/PHP_image_resize
# http://www.nimrodstech.com/php-image-resize/

include_once('php_image_resize.php');

$imgURL = $_GET["url"];
$resizeResult = smart_resize_image($imgURL,'',180,170,false,'browser',flase,false,100,false);

?>

Using PHP with the Blackbaud K12 API

In an earlier post, I showed how to use the Blackbaud K12 API interface for their “ON” products using PowerShell. Now, I’ll show you how to use PHP to do the same thing. I use PHP for all of my web applications.

There are a lot of ways to access a REST based API using PHP, but they can depend on which version of PHP, how it is installed, what security limitations your host has, and who knows what else. To make my life simple, I use the Httpful PHP library. To use it, download the PHP archive file to your web server. Then “include” the archive and you’re good to go.

include_once ('./httpful.phar'); # This loads the Httpful client

$schoolWebsite = "https://my_school.myschoolapp.com"  # website used to login to the "ON" products
$apiUser = "my_username";
$apiPassword = "my_password";

# Authenticate to the API, and get a token to use for further calls
$uri = "$schoolWebsite/api/authentication/login/?username=". $apiUser . "&password=" . $apiPassword . "&format=json";
$response = \Httpful\Request::get($uri)->expectsJson() ->send();

# Store the token as a variable so it's handy
$token = $response->body->{"Token"};

# Call the list API
$uri = $schoolWebsite . "/api/list/49748/?t=" . $token . "&format=json";
$response = \Httpful\Request::get($uri)-> send();
$listResults = $response->body;

var_dump($listResults);

Leveraging Open Application Programming Interface (API) in the “ON” Products #BBK12UC Presentation

Thanks to everyone who attended Julian’s and my presentation on that API at the Blackbaud K12 User Conference. As I mentioned at the end of the session, I think we will be “Better Together” if as users of the API we work together, collaborate and share how we are using the API.

Several people have asked for a copy of the presentation from the conference. Blackbaud recorded all the sessions today and will make them available.  In the meantime, here is a copy of the slides:

 

PowerShell and REST APIs

I really like being able to use the PowerShell ISE to debug my PowerShell scripts.  By default, it splits the screen showing my script and an interactive shell that I can directly type commands into.  In my previous post, I had a simple script that used the Blackbaud API to retrieve a large list of data, and send it off to Excel.  In this post, I’ll show how I would go about extended that script to actually do something with the data it retrieved.

The only output from my sample script was the Excel file.  However, we can use the PowerShell ISE to explore the data and figure out how to extend the script.  In PowerShell you can simply type a variable name into the shell and it will display it’s value:

Screenshot 2016-07-13 01.20.25

If we try that with $response from our script, a ton of data flies by.  How are we going to deal with all of this data in our variable?  What is going on?  Let’s look closer at some of the data that is scrolling by:

Screenshot 2016-07-13 01.29.17

Without getting too far into a discussion about data structures and arrays, we can see that there is a lot of data in our $response variable.  How can we work with it?

We might recognize that we’ve gotten back an array of data.  The classic programing approach would be to loop through it and do something with each element:

foreach ($person in $response) {
    echo $person
}

Running that basically produces the same results as simply typing $response in the shell.  However, we can use this as a starting point to do something with the data.  Imagine we really only care about the first name.  We can modify our code like this:

foreach ($person in $response) {
    echo $person.FirstName
}

Which generates a long list of first names:

Screenshot 2016-07-13 01.51.11

It turns out that PowerShell is pretty savvy when dealing with objects.  We can actually get the same output by typing $response.FirstName  It will grab each FirstName from every item in the array.

Screenshot 2016-07-13 01.54.06

We might also want to know how many results (or array elements) we got.  We can use the .Count method:

Screenshot 2016-07-13 01.56.04

Let’s assume for a moment that we want to find anyone in the list that has the EmailBad property set to true.  We can loop through all of the values and check each one:

foreach ($person in $response) {
    if ($person.EmailBad -eq "Yes") {
        echo "Found a bad e-mail:" 
        echo $person.EMail
    }
}

Which results in:

Screenshot 2016-07-13 02.07.34

Now, I cheated a little in my code to avoid something that caused me frustration initally with PowerShell.

foreach ($person in $response) {
    if ($person.EmailBad -eq "Yes") {
        echo "Found a bad e-mail: $person.EMail"  # this does not do what is expected
    }
}

It turns out that within quotes, PowerShell will not grab fields using the dotted notation.  You have to use the crazy format of $($person.EMail) within your string and it works.  Hopefully this will save you some time.  So the following code works as expected:

foreach ($person in $response) {
    if ($person.EmailBad -eq "Yes") {
        echo "Found a bad e-mail: $($person.EMail)" 
    }
}

I really like use the interactive features of the PowerShell ISE to figure out how to get and work with the data I need.  Hopefully this post shows you the process I use to work through how to access the data I need.

Blackbaud K12 “ON” API

On Thursday, I’m co-presenting at the Blackbaud K12 User Conference on using their API. Simply put, an API (Application Programing Interface) is a way for code to interact with something—in this case the website my school uses. One does not need particularly advanced knowledge of how to code in order to write useful solutions.

Recently, I wrote code to:

  • Detect inconsistencies and errors between different systems. I check if all our students in one system exist in another.  Are all their names spelled the same?
  • Automate when a student is enrolled in a class in our Student Information System to add them to an Active Directory group on our servers.
  • Compare three different lists of people to see how they overlap.
  • Generate a print directory of all employees with their photos.

For most of this, I’ve used PowerShell, a modern scripting language from Microsoft that is popular with system administrators. It makes writing code to work with an API really easy.

For example, I use the Blackbaud K12 API to retrieve a list I created within their software and open it up in Excel. From there I can analyze, chart or merge the data.

# replace my_school, my_username and my_password with values from your school
$schoolWebsite = "https://my_school.myschoolapp.com"
$apiUser = "my_username"
$apiPassword = "my_password"

# Authenticate to the API, and get a token to use for further calls
$response = Invoke-RestMethod "$schoolWebsite/api/authentication/login?username=$apiUser&password=$apiPassword&format=json"

# Store the token as a variable so it's handy
$token = $response.Token

# Call the list API with the ID# of the list we want
$response = Invoke-RestMethod "$schoolWebsite/api/list/49748/?t=$token&format=json"

# Convert the response (an array) to a CSV file, and write it to disk
$response | Export-CSV -NoTypeInformation -Encoding ascii -Path "C:/tmp/list.csv"

# Open up the CSV file in Excel
Invoke-Item "C:\tmp\list.csv"

I’m putting together a GitHub repository of sample code for the Blackbaud K12 API. It can be a lot easier to modify existing code rather than build something from scratch. I will post a link on this blog when it is available.