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.

Leave a Reply

Your email address will not be published. Required fields are marked *