GET advanced list as CSV?

Dave Levin

Hi all,

I'm a complete newbie when it comes to API, and wonder if anyone might point me in the right direction for the following challenge:

I have two advanced lists in Blackbaud EMS that I'd like to export daily to a local virtual server here on my campus. The CSVs are used by a script that automates the creation/suspension/movement of our employee and student Google accounts. We're getting by by manually exporting the advanced lists, brining them over to the VM, and executing the script manually.

I've toyed around with Postman and have successfully followed a tutorial to make a SkyAPI call with postman, "https://api.sky.blackbaud.com/school/v1/lists/advanced/XXXXX?page=1"

This succesfully returns a JSON file, and I wonder what steps I might take next to convert the JSON into a CSV....and then to figure out how I automate that process over on the server. Am I on the right track?

Thanks for considering,
Dave

Brian Gray

@Dave Levin - I have programs that do similar things. There are libraries available for many languages to convert JSON to CSV.

Your program could also iterate through the JSON data to extract the fields you need and write it to a CSV yourself.

If you're working in Visual Studio, take a look at SKYLib from Protege (https://protege.com.au/skylib). The library will present the JSON data as VS objects to make manipulation easier. It's worth your time to look at the demo program that comes with the download - just to get a feel for how it accesses and manipulates the data.

The download is free and it runs against your data (with a performance throttle). There is a fee for licensing it if you're going to use it for production, but it's worth every penny. (The licensing fee is less than the cost of the beer and aspirin required to do the same work without it.) The author - @Steven Cinquegrana - is an active participant in this community.

Steven Cinquegrana

@Brian Gray

The licensing fee is less that the cost of the beer and aspirin required to do the same work without it.

Wow! Very high praise indeed!

@Dave Levin let me know if you need anything.

Cheers, Steve

Michael Panagos

@Dave Levin
I've also found that the way this is returned is wonky. I'm not sure what language you are using, but with my SKYAPI PowerShell module (that I have been developing on-and-off for a while) I was able to convert the list into an array of PowerShell objects that can easily be exported out to CSV (via the Get-SchoolList cmdlet). Feel free to try the module as PowerShell is easy to use for automations (you can even use WinSCP to upload via SFTP within PowerShell).

You can also do it on your own and replicate what the module does by coding something like the following in PowerShell (I apologize about the formatting):

$JSONResponse = “your JSON data”
$ListCollection = $JSONResponse | ConvertFrom-Json

$Array = foreach ($listItem in $ListCollection)
{
# Get the column headers.
$ColumnHeaders = $listItem | Select-Object -ExpandProperty "columns" | Select-Object -ExpandProperty name

# Build the list item object.
$ArrayItem = New-Object System.Object
foreach ($columnHeader in $ColumnHeaders)
{
[string]$HeaderValue = $listItem | Select-Object -ExpandProperty "columns" | Where-Object {$_.name -eq $columnHeader} | Select-Object -ExpandProperty value
$ArrayItem | Add-Member -MemberType NoteProperty -Name $columnHeader -Value $HeaderValue
}
# Output list item object.
$ArrayItem
}

$Array | Export-Csv -Path "C:\\ScriptExports\\school_list.csv" -NoTypeInformation

Brian Pugh

@Dave Levin
A lot of fine answers here. Here is one more. We use the extension called API Connector to make calls to EMS. It's easy to set up and schedule even by the hour. For example we make a call to get all adult users in Core, then import the updated list using importrange to an Awesome Table app, and embed this for Faculty in a resource board. Support is great too, by the developer.

Lucien Piekut

@Dave Levin

Surprisingly you don't actually need to use Postman for any of these calls. Way back when I started learning I fell into that trap, and looking back it's a great tool for testing but largely not important to working with data.

Ideally what you're looking to combine is a couple of skills: Handling HTTP requests, understanding Refresh & Access tokens, understanding JSON, conversion of JSON to CSV.

Also, Advanced lists changed on Jan 1, 2023. I ended up making a fork of my code to handle their new pagination method.

You can use a lot of languages + tools to get there: Node, Python, Power Automate, SQLite.

These days I prefer to do things with zero cost tools outside of whatever reporting tool I use (PowerBI for ex.)

Feel free to message if you want some pointers!