PowerSchool API Backup.

Project Description.

This is a Python script that pull data for student schedules, contact information, and transportation information and then saves it to shared drives as Google Sheets, Excel files and PDFs.

Challenges.

Unlocking the power of using PowerSchool's API was something that the district wasn't able to do. I don't think they realized how powerful building something like that could be. I did a lot of searching and piecing together ideas to help me through the process. I found a helpful tutorial that laid out the basic ideas.

The first big challenge was to develop the queries that would get me all the information we would need for the backup. Getting transportation information was fairly straightforward, but the schedule and contact information would be a bit more complicated.

There was a wide variety of challenges in converting Oracle SQL to get things to work in PowerQuery. I needed to use CTEs, which work a little differently in PowerQuery, which I finally found a simple trick to make it work. Another blocker was that you couldn't use the built-in views that PowerSchool provides, which makes getting contact information much easier. So I had to rebuild the whole query to actually make it work with PowerQuery.

Once I did figure our how to get all the queries to work, it was time to start connecting to the API and actually make something. I used Google Apps script to call the API and then write the queries to Google Sheets. I was able to then copy those Sheets as other formats to shared drives, so it was easy to share it to build administrators as well as save the files in offline mode.

The next challenge came with using triggers and the poor reliability of Google Apps Script. I started working on various methods of error detection, however, there never seemed a good way to execute the scripts through Google Apps Script.

Iterations.

Once I got tired of trying to wrangle Google Apps Script to work, I thought it might be better to just use Python to do everything. So I converted everything over to run on a Python script. This included figuring out how to call the PowerSchool API, how to use the Google API, and make appropriate error handling along the way.

Overall, the project works really well. It's kind of sad that it wouldn't get a lot of attention since it wouldn't be used every day, but in the case when the internet goes out or PowerSchool goes down, we have a reliable backup available for schools to use. It was a huge learning experience for me.