Why is Contact logging important?

Schools are houses of invisible complexity. There are so many moving pieces that without strong systems it is nearly impossible for a school leader to create consistent adult behaviors that yield predictable and positive results with students. A contact logging system is one example of a tool school leaders can use to promote staff-wide action around parent and family communication.

In terms of monitoring the consistent implementation of school policy, a contact logging system has two primary functions. First, they capture which families are getting phone calls and second, they log which staff are making phone calls. Understanding which students are getting the most outreach is helpful for planning more significant interventions and supports. Knowing which staff are making calls is necessary for maintaining high expectations in a school. For example, imagine a school where there is an expectation that if a student cuts class it is the responsibility of the teacher to call the family of the student regarding the missed class. Without a place to capture whether or not that phone call is made, there is very little school leaders can do to understand whether or not teachers are consistently meeting the outreach expectation.

Contact logs are not just good for school leadership. As long as they are designed smartly, they can make the work of family outreach more collaborative and transparent for all school staff. For example, contact log systems should make it easy for teachers to find contact information for their students. It should also be simple for teachers to get help when they find a bad number. In order to avoid multiple teachers calling the same family multiple times in one day, it should always be clear which students have been called, what the subjects of those calls were, and which staff member made the contact.

In my previous post, Student Contact Log: How To Guide (Part I), we looked at displaying student contact information using an Awesome Table, which helps solve the problem of making family contact information easy to find for staff. For the rest of this post, I want to show how to build a Google Form which will:

  1. Load in student data to save the user from having to type in that information
  2. Categorize the type or purpose of the contact, which will help with goal setting and strategic planning

In the next post I will share how to include an email notification to a designated staff member if a contact number is incorrect

How to create a free contact logging system

Step 1: Create a Google Form

When creating a Google Form for staff use, always require domain login to view the form and also always collect respondent's domain username. This solves two security concerns. First, it stops people without email addresses on your school's domain from accessing the form. Second, collecting usernames gives some degree of certainty of who completed the form, which makes it easier to spot bad form entries and perform lookups between username and other important data. In other words, once the form creator has a username that they are reasonably sure is representative of the actual user, they also know the user's name, what classes they teach, which students they see, what other responsibilities they have in the building etc because the username can be used to look up all of that additional information. New Visions Cloud Lab has a very good spreadsheet fundamentals free online course, which goes into greater detail on the above notion of looking up information on a user when a form is submitted. It is good practice to avoid asking for information from a user that you already know.

Below is an example of what a school contact logging form can look like. In general, with Google Forms, less is more. "Student Name" is a drop down question type to ensure that each student has a standard name in the form. If name were a text field, no matter how clear the question and help text, form users will enter first name, last name, first and last name, middle name, nick name, misspelled first name, etc. Non-standard names makes analysis much more time consuming and basically defeats the purpose of a Google Form. Form validation is the idea of limiting form response options to standard entries and it is very common place on the web today. The same could be said for the student Id and phone number fields. However, in this case, these fields will come pre-filled, thereby mitigating the technical dangers of unrestricted text field entry.

STEP 3: GET FormRanger

With all this attention to form field validation, it is also important to use a consistent list as the source for form validation. Andrew Stillman wrote formRanger for exactly this purpose. FormRanger allows users to grab a list of items, like student names, from a column in a Google Spreadsheet, and use them as options in a Google form question. If the list in the spreadsheet changes, the dropdown list can be optionally auto-updated as well.

Once installed from the Add-ons gallery, open the formRanger sidebar. Choose "Student Name" as the question to be populated from a values list in a sheet.

Find the spreadsheet in Drive containing the full roster of students in school. Ideally this is the same list used in the contact information sharing spreadsheet created in the previous post.

Choose the sheet containing the list of students to use in the drop down list. Then, select the column header containing the list. In this example I have selected the Spreadsheet Student Contact Sharing: Demonstration, the "Values for Form Ranger" sheet, and the column with header "Students". 

Untitled image (29).png

FormRanger wisely asks for a list name to make it easy to recycle lists across multiple forms. Once I've created the list of students, I can use the same list in any other form I can edit.

Click "Refresh question" and formRanger will grab the list of student names and load them into the dropdown for the "Student Name" question. Optionally, set a trigger to automatically sync the form's student name dropdown list with the spreadsheet, which means that every time a new student is admitted or a student leaves the school one doesn't have to go through an existential, multi-form rebuilding process.

STEP 3: Get a Pre-filled GOOGLE FORM URL

Google Forms pre-filled URL feature was fantastic when it first came out, but now after a significant user interface upgrade, it is simply phenomenal. Just click the "Responses" menu and select "Get pre-filled URL".

Complete the form by picking a student and typing some filler text in the remaining two questions.

Untitled image (30).png

Click "Submit" and Google will create a URL that will open the form in a pre-filled state.

Lets look at the URL created by Google below. Notice the bolded values that have been added for emphasis correspond to the values entered in the form during the process of creating the link. Replacing those bolded values with any other value will pre-fill the form with the new value. For example, if one replaces "STUDENT+ID+HERE" with the number "1234" and then enter the URL into a browser, the Google Form will open and the student id question will be pre-filled with the value "1234".


STEP 4: Create an individual link for every student

For the purpose of demonstration it is clearer to show how to create an individual link to a pre-filled Google Form for each student in a separate spreadsheet tab. This is not a necessary step as the entire concatenation of text and form URL can be done in a single cell, which is shown below, but splitting this step out will make the task more approachable.

Concatenation is the process of combining multiple strings of text. Google sheets will concatenate text using the following syntax:

Which will output:

It is possible to concatenate both text and cell references or any combination thereof. To create a pre-filled Google Form URL for each student I will concatenate the form URL and the student first name, last name, Id, and phone number from the spreadsheet.

Notice that I replaced each form value from the pre-filled form url with a cell reference. Student Id is replaced by D2 and phone number is replaced by E2. Name is replaced by B2 & "+" & C2 because I want to include both the first name (B2) and the last name (C2) and I want them separated by a space (+). URLs cannot contain spaces so any space between words gets encoded as a plus sign.  I made a video about creating pre-filled form urls, which you can watch below.

The final step is to just copy the formula down to the end of the list, which will shift the cell references and create customized form links for logging contact for each student.

In the example from the previous post the link to the pre-filled URL was housed in a nice looking grey button on the staff intranet site. The purpose here is to make it easy for staff to find these links by not keeping them only in a spreadsheet in Drive. Below is the same exact pre-filled URL that I created above, but styled to look like a button in an Awesome Table.

Here is the full formula:

="<center><a style=align:center;color:black!important;text-decoration:none; href=" & char(34) & "https://docs.google.com/forms/d/1D8e_j0k_79i_4DOy39f_fWipGmqbYzsy8eWua3GK41I/viewform?entry.2004092810=" & B3 & "+" & C3 & "&entry.633433424=" & A3 & "&entry.725347293=" &E3 & "&entry.1666707491" & 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>"

Here is a closer look at the formula. The highlighted section is the actual pre-filled URL. The rest of the formula is concatenated HTML that makes the link look like a clickable button.

The final product is the Awesome Table on a staff Google Site which contains clickable buttons that are customized for each student.


When I was a teacher and in many of the schools I work with now, finding accurate contact information was a real headache. When I was an assistant principal and as is the case with many school leaders, I had no real way of knowing which teachers were calling which student families. Creating the above contact logging system solves both of these problems by combining free, flexible web technologies.

Please share your questions and what you put together in the Apps Scripts for Education - Builders and Users Google Plus community.

I will be presenting work and ideas about systems as preconditions for transformational instruction in a talk called, "Climbing Maslow's Paradox: 'Systems Design' a Core Leadership Practice" at this year's InnEdco conference in Keystone Colorado on June 22 and 23. I hope you will join me for one or both of the conversations!