Month: May 2017

Next Year’s Course Lists for Students and Course Rosters for Teachers from PowerSchool

I use a MacBook to do most of my work. This should work on a PC too, though navigating in Terminal might be a little different. Feel free to leave a message below to let me know if you run into problems and I'll see if I can help.

This spring, our principals want to give students their course lists for next school year with courses listed in alphabetical order. This is so that students can confirm their course enrollments without knowing what period or teacher they are assigned to. This is to help preclude students requesting changes to be with a particular teacher or friend.

I wrote a Python script to do this. <– Note: Change .txt to .py before running

We also wanted to provide teachers with class rosters so that they could look for any potential issues including gender balance.

Another Python script handles this. <– Note: Change .txt to .py before running

To get the data, use DDE to select records from the ScheduleCC table for the next school year. In my case, I set TermID >= 2700 since TermIDs for next year (2017-2018) are 2700, 2701 and 2702

Then I export the data using the fields below. The first 5 come from the ScheduleCC table. Other tables are: 2 Courses, 1 Students, 183 CustomText, 5 Teachers and 3 Sections. Note that for your PowerSchool setup, the Student_Email record may be located elsewhere.

Exported fields:
[1]Student_Number
[2]Course_Name
[1]first_name
[1]middle_name
[1]last_name
[1]Sched_NextYearGrade
TermID
[183]Student_EMail
Course_Number
SectionID
Expression
[5]First_Name
[5]Last_Name
[5]Email_Addr
[1]Gender
[1]Father_Email
[1]Mother_Email

Sorting of the data file is essential!

For student course lists, sort the data in this order: Sched_NextYearGrade, StudentID, Course_Name. If you just want to print course lists for just MS or HS,  remove the excess lines or refine your export from DDE. Save the file with the name sched_data.csv in the same folder as this script. Save this file as a csv file named sched_data.csv in the same folder as the shed_alpha.py script.

For teacher rosters, sort the data in this order: [5]Last_Name, [5]First_Name, TermID, Expression, SectionID, [1]Last_Name, [1]First_Name. Save the file as a csv file with the name roster_data.csv in the same folder as the class_rosters.py script.

To run the script, open Terminal and navigate to the folder containing the script and data file. For instance, if you saved them in a file called rosters in your Documents folder,

cd ~/Documents/rosters

and then run the script:

python class_rosters.py

The python scripts create an .rtf file for each student or teacher. Note that if you open the rtf file in preview, it may not show or print the page breaks that are in the documents. If you open them in MS Word, you should see them properly.

The scripts also create a handy Terminal command to bulk convert the rtf files to pdf format. This command requires LibreOffice available here. After running the the python script to create the rtf files, assuming all went well, you’ll see a folder with the current date and time in its name. Open the folder and find the file named convert_to_pdf.txt. Open this file, copy its contents, paste it into Terminal and hit Enter. In several seconds you’ll have your rtf files converted to pdfs. If you get an error message, Terminal probably isn’t in the correct folder. cd to the folder holding the rtf files and try again.

Oh, another little bonus is a file that gets created called 1mail_merge_info.csv. This has the three columns needed to use Amit Agarwal’s Mail Merge with Attachments to send them out to students and staff.

 

 

Creating individual corporate email signatures for staff

Our staff email signatures are a varied mix of good and bad. When I need to respond to someone who has just emailed me, I’d like to be able to look at the signature to see his or her position/title, room/office number and phone extension. For external email recipients, additional contact information should be included including a link to our website and to our Twitter feed. Personal badges and other clutter should be avoided for a clean, professional looking email. I want it to look like this:

Email Signature
Sample of desired email signature

Google Apps Manager or GAM (https://github.com/jay0lee/GAM/wiki) allows a Google Admin to set signatures for accounts in the domain. So, I use a Python script to create an html signature for each staff person as well as a batch file that tells GAM which signature file to upload to each account.

It all starts with a spreadsheet. The one I use is the Form Responses sheet from a Google Form I use to collect the necessary data from staff. I do this so that individuals are able to decide on how their names show and to ensure that we have accurate information about room numbers and phone extensions. This also allows the individual to decide whether or not to include a personal cell phone number in the signature.

The column headers in the spreadsheet are:
Timestamp, email, Name, Position, Room, Extension, Mobile, Include_Mobile

The script then processes the data in the spreadsheet to create a signature file for each row and staff member as well as a batch file that I run in terminal with the command:

python gam.py batch batchfile.txt

This creates the individualized email signature seen above. KIS social media links like official KIS Twitter feeds, Facebook and Instagram links will be added in the fall of 2017.