Connecting PowerSchool Data to Google Sheets Part 1: Set Up the PowerQuery

Google Apps Script, PowerSchool, PSUGS, PowerQueries, API, TheKitchenSink

This tutorial uses Google Apps Script. If you need to review the Google Apps Script Primer, here it is: Take Me To The Primer

This is a Part 1 of a series that will explain how to link your PowerSchool data to Google Sheets. If you are using another type of data storage, the technique will also work with some modication.

Stay Calm :)

In order to make all this work, we need to install a PowerQuery Plugin. You can learn to do that here:

https://www.psugcal.org/index.php?title=PowerQueries

Now, most people who are new to this feel overwhelemed initially. Here is what you need to know:

1. You can learn this without writing information back to PowerSchool. You are posing no risk to your data.

2. No one can see these plugins unless they have administrative access and know how to use the Data Export Manager. Your end-users are not going to notice anything.

3. There are three files in the demo, in reality you only need two, but we will learn the long version of the process first.

4. You have to ZIP your work to upload it, and if you use an Apple or Linux machine you cannot use the defauly ZIP process. Pay attention when we get there.

5. Once you get the process down it is very easy to update and refresh your plugin.

Get Ready

1. Download the example we will learn from, The Birthday Plugin from California PSUG

2. On your personal computer, create a folder called, “PowerQueries”, and move that ZIP file to that folder. We will work in this folder from now on.

3. You need a decent text editor. I am going to recommend one right now, I know some people are going to lose their minds and rage quit, but this is for people who have not join an tribe yet. If you have no idea what that meant, you need to download Visual Studio Code (Free Edition)

   

4. Install it, and get that out of the way. Then open it, and save a fake/sample file called “hello.txt” to your PowerQueries folder.
   

5. If you are an Apple user, you need ZIP software that will let you exclude the .ds_store file. I use Keka, https://www.keka.io/en/. If you are a Linux user, and I am 90% of the time, you need to use the command line. The command and flags zip -r - D. Linux users, if you are out there and need specific help, message me on the comment thread. I believe Windows default ZIP is fine. If you have issues let me know in the comment thread, or get a better OS :).

6. Optional but highly recommended, SQL Studio from Aurora Educational Technology This is my personal favorite way to test PowerQuery SQL before completing the plugin.

7. In PowerSchool make certain you have access to:

   1. Plugin Management Dashboard: ~/admin/pluginconsole/plugInConsole.action

   2. Page and Data Management: ~/admin/tech/pageanddatamanagement.html

   3. The Data Export Manager: ~/admin/datamgmt/exportmanager.action

This is the end of Part 1.
Take Me to Part 2



Copyright © Domain Seven LLC. All rights reserved.
For permissions to use or share any content behind our paywall, please email us at: tonydeprato@domain7.tech .