If you need to understand the basics and the setup, or you have no idea what Google Apps Script is, go back to Part 1.
Step 1: Extract the Zip File Contents
You should have downloaded the file, “PowerQuery_Example_1_0.zip”.
Extract the contents to a folder, or to your Desktop.
Here is the structure:
We are going to install this, and then test them in PowerSchool. You can upload it as is, and it will work, but, long term you need to learn to edit the structure. One of the biggest edits when using data externally is in the “plugin.xml” file.
Step 2": Open the the *._queries.xml File
You will find many plugin files online to test out. If you want to see what data they are accessing, and what they are doing, open the file inside the “queries_root” folder. In this case it is named, “example.named_queries.xml”.
<queries> <query name="org.psugcal.example.students.birthdays" coreTable="students" flattened="false"> <description>Birthday info for all Active Students - District Wide</description> <columns><column column="students.student_number">id</column><column column="students.lastfirst">name</column><column column="students.grade_level">grade_level</column><column column="students.dob">birthdate</column><column column="students.dob">age_years</column><column column="students.dob">month_day</column> </columns> <sql><![CDATA[SELECTstudent_number,lastfirst,grade_level,TO_CHAR(dob,'MM/DD/YYYY') as birthdate,floor(months_between(sysdate, dob) /12) AS age_years,TO_CHAR(dob,'mm/dd') AS month_dayFROMstudentsWHEREenroll_status = 0ORDER BY lastfirst]]> </sql> </query></queries>Normally for the query name, org.psugcal.example.students.birthdays, I edit this to the domain I am working on. If your server is at “theschool.org”, you would modify this to “org.theschool.example.students.birthdays”. Whenever you see a domain reference, you should do this.
This SQL query retrieves specific information about students from a database. It selects the student number, full name, grade level, birthdate (formatted as MM/DD/YYYY), age in years, and birth month and day from the students table. The query only includes students who are currently enrolled (where enroll_status equals 0) and orders the results alphabetically by the students' full names (lastfirst).
Example:
| student_number | lastfirst | grade_level | birthdate | age_years | month_day |
|----------------|-----------------|-------------|-----------|-----------|-----------|
| 12345 | Doe, John | 10 | 05/15/2005| 19 | 05/15 |
| 67890 | Smith, Jane | 9 | 08/22/2006| 18 | 08/22 |
| 23456 | Brown, Charlie | 11 | 12/01/2004| 20 | 12/01 |
| 78901 | Johnson, Emily | 12 | 03/30/2003| 21 | 03/30 |
Step 3: Permissions folder and the XML file
Open the Permissions folder and XML file.
<permission_mappings> <permission name='/admin/home.html'><implies allow="post">/ws/schema/query/org.psugcal.example.students.birthdays</implies> </permission></permission_mappings>All you need to know here is that anyone with access to “/admin/home.html” can run this plugin. Go ahead and update your domain.
Step 4: Plugin.XML
Open “plugin.xml”.
<?xml version="1.0" encoding="UTF-8"?><plugin xmlns="http://plugin.powerschool.pearson.com" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"description="Powerquery Example"name="PowerQuery Example (Birthdays)"version="1.0"xsi:schemaLocation="http://plugin.powerschool.pearson.com plugin.xsd"><publisher name="Jim Parsons"><contact email="jparsons@vcschools.org"/></publisher></plugin>For now leave this alone. When you finally link this to Google, it will need to contain many more fields. This file holds the attributes to the author, so if you find this useful send him an email :).
If you want to change the name or description you can do that here. The version contol is useful and it is recommended that you develop a versioning system.
I often will create, edit, upload, and revise one of these 10-15 times before it is ready for production.
Step 5: Install the Plugin
Installing a Plugin in PowerSchool
Start by selecting "System" under "Setup" in the main menu. This will take you to the System Administrator page.
Under the "Server" section, click on "System Settings" to access the System Settings page.
On the System Settings page, click on "Plugin Management Configuration" to access the Plugin Management Dashboard.
Click on "Install" to access the Plugin Install page.
You can either click on "Choose File" to select the plugin installation file or "Browse..." to search for the plugin. This is your ZIP file.
Once you have selected the plugin file, click on "Install". A confirmation message will appear.
Enabling a Plugin in PowerSchool
Start by selecting "System" under "Setup" in the main menu. This will take you to the System Administrator page.
Under the "Server" section, click on "System Settings" to access the System Settings page.
On the System Settings page, click on "Plugin Management Configuration" to access the Plugin Management Dashboard.
Search for the plugin that you recently installed above.
Check the box under "Enable/Disable" to enable the plugin.
Step 6: Use the Plugin
Go to “Page and Data Management”
Find the “Data Export” section
Click, “Export Data”
Choose, “Additional Data Sets”
Next, in the next drop down box type “NQ”, this will show a list of user installed PowerQueries
Choose the one that says “org.psugcal.example.students.birthdays”
Select all the boxes on the left, and hit, “Next”, in the bottom right
On the next page, click “Show Records” and check the data
This is the end of Part 2.
Take Me to Part 3Copyright © Domain Seven LLC. All rights reserved.
For permissions to use or share any content behind our paywall, please email us at: tonydeprato@domain7.tech .Get more from Domain Seven Code Community in the Substack app
Available for iOS and Android
Leave a comment
Share Domain Seven Code Community by Tony DePrato
