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.