When I first started teaching, more veteran teachers and my supervising assistant principal routinely referenced the phone log I should have been keeping to document phone conversations I was having with the families of my students. While now I can recognize the importance of maintaining C.Y.A style records, at the time, the notion of keeping a marble notebook to record my phone calls seemed like a waste of time.

As an assistant principal and then working with many schools from the network office, I still am not sure how useful disconnected records of calls really are for improving student outcomes, however, I am absolutely certain that the best schools have clear routines and systems in place around family communication. The best systems for family communication solve a few problems:

  1. Finding and sharing contact information in many schools involves conflicting, duplicative, and often inaccurate lists of contact information.
  2. Sharing the substance of calls with staff who may have an interest in the communication, but who were not directly involved, often requires additional effort, like writing an email to an advisor, finding another staff member for a face to face conversation, or setting up a meeting.
  3. Identifying longer term trends that can inform the types of student support services and professional development opportunities offered in a school are simply not possible without a communication tracking system.

While my marble notebook from 2005 could not solve the above three issues, a Google Form, Spreadsheet, Site, Awesome Table, and the New Visions Add-on called FormMule definitely can. In keeping with my last post "Climbing Maslow's Paradox" my upcoming talks at InnEdCo and EDXEDNYC education and technology conferences, and the theme of this developing blog, the following will describe how to technically create a free student contact logging system. Without a clear plan or procedure for family communication it is challenging to maintain the type of transformational instruction that should be the goal of our school systems. Creating and maintaining the student contact log represents work at one of the base layers of the Maslovian educator hierarchy; it is a support for transformational instruction.

I will not be discussing what goes into having an effective conversation with the families of students who need additional support. For tips on that, I would recommend the books Crucial Conversations and Teaching with Love & Logic.

Sharing Contact Information

In many schools, contact information is held in the “Student Information System,” (SIS) which can range from PowerSchool, to ATS in New York City, to more home-grown solutions in smaller cities and districts. The problem with these SIS’s is that either:

  1. Not everyone has access. In New York City, only a few members or the school staff have login credentials for ATS, which can only be accessed from certain specially configured computers.
  2. The SIS requires its own login. In cases where everyone has access, SIS’s often require separate logins than email, production, and collaboration tools where work actually happens. In other words, while a teacher may have a login for PowerSchool, it is separate from where the teacher lesson plans or emails, and is therefore not front and center at the times where a teacher might need to reach out to student’s family.
  3. There are limited reporting capabilities. Most call logging features in student SIS’s that I have seen either do not have sufficient aggregate reporting capabilities or aggregate reporting comes at the expense of more granular views, or the reporting is robust, however, it is completely isolated from other student data systems like online gradebooks, schedules, transcripts, testing history, etc.

For most schools on Google Apps for Education, there is a simple solution that involves:

  1. Routinely exporting contact information from the SIS as a CSV file.
  2. Uploading the CSV of contact information into a Google Spreadsheet “container” sheet.
  3. Displaying filterable contact info securely with an Awesome Table, produced by Romaine Vialard, on a staff Intranet.

Here is what we will be building for the remainder of this post:

Click to view a sample staff intranet site with an example of an Awesome Table housed student contact list

Click to view a sample staff intranet site with an example of an Awesome Table housed student contact list

Exporting from an SIS into Google Spreadsheets

There are lots of different SIS’s out there and the best way to find out how to export student contact information is to check the SIS’s documentation. In most cases, getting data out of an SIS means downloading a CSV file (comma separated values). When making decisions about which SIS to purchase for one's school or district, avoid at all costs any SIS without robust data export features.

Uploading the CSV of contact information into a Google Spreadsheet “container” sheeT

The first step is to create a new Google Spreadsheet with two sheets (or make a copy of this template). The first sheet, or the container sheet, is used to house uploaded raw data - like the CSV export from an SIS. At least one other sheet is used to perform calculations or transformations on the raw data. This type of spreadsheet architecture has the benefit of being able to accommodate raw data uploads without requiring the spreadsheet owner to rebuild special functions and reports. The raw data and any type of calculation are happening in two different places and therefore can be changed independently of each other. In this case I've created a tab called "Imported CSV from Student Information System" to hold the raw data. I've created a tab called "Awesome Table Display" to display the data.

Google has excellent documentation on how to upload a CSV into a Google Spreadsheet. The key to remember is to replace the the current sheet. This will replace any data on the current active (open) sheet in the spreadsheet with data from the CSV, without changing any of the formulas referencing data in the current sheet. Make sure to import the CSV file while on the "Import CSV from Student Information System" sheet so as not to replace the functions in the display sheet.

Clicking import will move all the data from the CSV file into the Google Spreadsheet. I applied some formatting to make the raw data more readable in the example below. A few seconds of effort differentiating the header row with color and playing with column spacing can go a long way to making raw data easier to look at.

Creating a Display Sheet

On the second sheet, create a set of headers to label the columns of raw data that will be displayed in the Awesome Table. Notice that there are two sets of headers, but header names are not repeated. The first headers in red are used to sort the raw data alphabetically by student (advisor, grade level, class would work just as well). Some of this unformatted data will be used to populate the filters in the Awesome Table. The second group of headers in orange will use HTML to format the data in the red columns for actual display in Awesome Table. Remember, for an Awesome Table to work properly it is important to have unique header titles.

The next step is to get all of the raw data of interest sorted alphabetically under the red header columns. In cell A3 enter the following formula:

=sort('Imported CSV from Student Information System'!C2:C,'Imported CSV from Student Information System'!$B2:$B,1)

The above formula will sort the data in column C of the 'Imported CSV from Student Information System' sheet, which is student ID numbers, by column B of that sheet, which is student last name. The '1' means that data will be sorted alphabetically by column B.

Repeat the process above for the remaining red column headers. Below the data is sorted alphabetically by last name.

Next it is time to decide which of the red columns contain data which we want our teachers to be able to filter on. Student Id is probably not a useful filter, but name and grade certainly are. Adding filters is as simple as putting in a few keywords from the Awesome Table documentation in the second row. The three keywords below mean:

  • nofilter - do not apply a filter to this column of data
  • hidden - hide this column from view
  • categoryFilter - use a drop down list style filter for this column of data

In columns F through I we will concatenate data from columns A through E along with some handy HTML to create formatted data that the Awesome Table will render. In the spreadsheet, columns F through I will not be human readable, but in the Awesome Table the data will display nicely.

In cell F3 enter the following formula: 

="<div style=" & char(34) & "width:200px" & char(34) & ">" & B3 & " " & C3 & "</div>"

The above formula is taking the "<div>" HTML tag and adding a width style property. A div is a container on a webpage. In this case we want to build a container that is 200 pixels wide and holds the contents of cells B3 and C3, or the students first and last name. Applying an inline style, like the width property, in HTML requires the use of quotation marks. However, spreadsheets use quotation marks to denote strings of text so whenever an actual quotation mark is necessary in the HTML you will see the function "char(34)" which gets evaluated into a quotation mark.

You do not need to know HTML to put this build together, just copy the above formula. If you do not know HTML, but are interested in learning, I recommend the free Code Academy HTML and CSS online course.

The width property may have to be adjusted depending on how wide the data is that is being displayed. To change the width, just change "200px" to a smaller quantity for a narrower column or a larger quantity for a wider column. The process of landing on the correct width is just trial and error, but the values listed are a good place to start.

Copy the formula in F3 down to the last row of data.

In cell G3  enter the following formula:

="<center><div style=" & char(34) & "width:25px" & char(34) & ">" & D3 & "</div></center>"

The above formula is taking the grade of a student and putting it in a div with a width of 25 px. The entire div is centered with a "<center>" tag. Copy cell G3 down to the last row of data as well.

In cell H3 enter the following formula:

="<center><div style=" & char(34) & "width:100px" & char(34) & ">" & E3 & "</div></center>"

The above formula is taking the phone number of a student and putting it in a dive with a width of 100 px. The entire div is centered with a "<center>" tag as well. Copy cell H3 down to the last row of data.

In cell I3 enter the following formula:

="<center><a style=align:center;color:black!important;text-decoration:none; href=" & char(34) & "<<MY PREFILLED FORM LINK GOES HERE>>" & char(34) & "target=" & char(34) & "_blank" & char(34) & "><span style=" & char(34) & "width:100px;background-color:#f5f5f5;background-image:-webkit-linear-gradient(top,#f5f5f5,#f1f1f1),color: #444;border:1px solid rgba(0,0,0,0);border-radius:2px;display:inline-block;font-size:11px;font-weight:bold;height:27px;line-height:27px;padding:0px 20px 0px 20px;text-align:center;text-transform:uppercase;white-space:nowrap;font-color:black" & char(34) & ">Log Contact</span></a></center>"

The above formula is much less complicated than it looks. Basically it is creating an anchor tag, which holds a link, that holds a span, which is just another type of container tag in HTML. The span is styled to look like one of Google's grey buttons. Copy the contents of cell H3 down to the last row of data. Notice, the "<<MY PREFILLED FORM LINK GOES HERE>>" section of the formula. Add a link to a Google Form for logging contact outreach between the quotation marks. In a subsequent post, I will outline how to create a pre-filled form link for exactly this purpose.

Displaying HTML in an Awesome Table

On a Google Site, use the "Edit Page" tool and then click the "Insert". Choose "More Gadgets...". 

Next, search for "Awesome Table".

Click "Select".

Add the URL of the Google Spreadsheet containing the data to display. Next enter the name of the sheet containing the display data, in this case it is "Awesome Table Display". Finally, enter the range of data. In this case, it is A1:I. Remember, columns A through E will be used to create filters. The HTML in columns F through I will actually be displayed. Click the green "Save" button at the top and then the "OK" button.

You now have a filterable table of student contact information along with links for teachers to log calls. 

Stay tuned for the next post on creating the actual Google Form for logging contacts!