PowerSchool Resources

Resources for PowerSchool Developers and Administrators.

Adding block information from PowerSchool to Google calendars

Google Calendar is an excellent tool for keeping track of your schedule as well get timely reminders. What if we can populate students’ schedule in their Google Calendars so they have an electronic record with notifications about the classes?

PowerSchool does not have a tool to enable this directly. However, we do know that we can extract data using SQL and export them as CSV files which can then be imported in Google Calendars by students.

Broadly, the process therefore involves two key steps:

  1. Export the data from PS as CSV
  2. Import into Google Calendars using CSV

Exporting the data from PS

You can use Oracle SQL Developer to execute SQL below and export the file as CSV. Here is the SQL:

SELECT	
  'Period: '||p.Abbreviation as "Subject",
  cd.date_value as "Start Date",
  to_char(to_date(bsi.start_time,'SSSSS'),tf.timeformat) as "Start Time",
  cd.date_value as "End Date",
 	to_char(to_date(bsi.end_time,'SSSSS'),tf.timeformat) as "End Time",
  null as "All Day Event",
  null as "Description",
  null as "Location",
  null as "Private"
    
  FROM Calendar_Day cd 
  INNER JOIN bell_schedule_items bsi ON cd.Bell_Schedule_ID = bsi.Bell_Schedule_ID 
  INNER JOIN Period p ON p.id = bsi.period_id,
  /* Assign a time format based on user locale.
     The replace function changes PS time format to Oracle
     and the fm modifier is to toggle off and on leading zeroes*/
  ( SELECT 'fm' || replace(replace(replace(
    (	SELECT ltf.timeformat
      FROM
        locale loc JOIN 
        localetimeformat ltf
          ON loc.localetimeformatid = ltf.localetimeformatid
      WHERE upper(loc.languageisocode || '_' || loc.countryisocode) = upper('en_US')
    ),'HH','HH24'),'a','AM'),'mm' || chr(58) || 'ss','fmmi') as timeformat FROM dual
  ) tf

  WHERE
  cd.date_value between to_date('08/08/2017', 'MM/DD/YYYY') and to_date('01/06/2018', 'MM/DD/YYYY') 
  AND cd.SchoolID = 200

  ORDER BY
  cd.date_value, 
  bsi.start_time, bsi.Period_ID
  

The SQL is coded for specific school-id and start/end dates, so if you will need to specify those based on which school/dates you need to process for.

Importing the CSV into Google Calendar
Importing the CSV into Google Calendar is pretty straightforward as Google provides an interface for it. Please see these instructions from Google on how to import a CSV into your Google Calendar.
 

2 Comments on “Adding block information from PowerSchool to Google calendars

  1. This is quite a nice solution to streamlining calendars! What’s the workflow for when a schedule changes? Manually update on the individual or do you push a full dump to all each time?

    • Good question. It is expected to be a manual update if individual schedule changes. For wider schedule changes, the re-import is the solution. I do see scope of automating this.