SST Referral System

Student Success Team

UPDATED!!!

After hearing back from a school that is using this system, I have updated the template so that there is a lot more flexibility for the school to craft each intervention sheet for the needs of each student.

  1. There is room on the template for 10 modifiable interventions
  2. Room for up to 7 teachers
  3. Now each Intervention can be decided upon by the team instead of specific interventions, Change the Intervention title on the Summary sheet and it changes everywhere else.

Recently a client needed help building a Student Success Team Tracking System. Initially we build a form and connected it to a google doc to view and share the information. As I thought more about the workflow we decided to use a spreadsheet as the merge document to leverage the ability to build dashboards to display the data, as well as to simplify data input and provide calculations.

Click the image of the sheet to make your own copy

This shows how to authorize the script to show/hide the interventions tabs based on the yes/no question. It needs to be authorized by each user on first run.

The Form

Go ahead and try it! Fill out the form, you will receive a copy of the sheet that does the summary of the data.

In this infographic, we show the workflow on how the system flows.

  1. Teacher identifies problem
  2. Teacher submits form
  3. Form generates Spreadsheet
  4. Team meets and discusses the student to identify if interventions will help
  5. Teachers collect data on the intervention successes
  6. The sheet calculates the outcomes.

On this sheet is a script that shows and hides the sheets, depending on whtether the sheet is used with the YES/NO popup menu. The script would need to be run from the script editor in order to authorize it. Once authorized, a timer can be set on the script to run (see the script trigger below)

For the administrative team, we will then build a data studio report which will summarize in one place all the referrals and allow the admins to easily find the sheets that contain the student reports.

The script

//This creates a menu that allows you to show/hide the interventions based on yes or no

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Interventions')
      .addItem('Show/Hide Sheets', 'selectSheets')
      .addToUi();
}


function selectSheets() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1 = ss.getSheetByName("Summary");
  var sheet2 = ss.getSheetByName("Int1");
  var sheet3 = ss.getSheetByName("Int2");
  var sheet4 = ss.getSheetByName("Int3");
  var sheet5 = ss.getSheetByName("Int4");
  var sheet6 = ss.getSheetByName("Int5");
  var sheet7 = ss.getSheetByName("Int6");
  var sheet8 = ss.getSheetByName("Int7");
  var sheet9 = ss.getSheetByName("Int8");
  var sheet10 = ss.getSheetByName("Int9");
  var sheet11 = ss.getSheetByName("Int10");

  var cell1 = sheet1.getRange('B23');
  var cell2 = sheet1.getRange('B24');
  var cell3 = sheet1.getRange('B25');
  var cell4 = sheet1.getRange('B26');
  var cell5 = sheet1.getRange('B27');
  var cell6 = sheet1.getRange('B28');
  var cell7 = sheet1.getRange('B29');
  var cell8 = sheet1.getRange('B30');
  var cell9 = sheet1.getRange('B31');
  var cell10 = sheet1.getRange('B32');


  if (cell1.getValue() == "NO") {
    sheet2.hideSheet();
  }

  if (cell1.getValue() == "YES") {
    sheet2.showSheet();
  }
  
  if (cell2.getValue() == "NO") {
    sheet3.hideSheet();
  }

  if (cell2.getValue() == "YES") {
    sheet3.showSheet();
  }
  
  if (cell3.getValue() == "NO") {
    sheet4.hideSheet();
  }

  if (cell3.getValue() == "YES") {
    sheet4.showSheet();
  }
  
  if (cell4.getValue() == "NO") {
    sheet5.hideSheet();
  }

  if (cell4.getValue() == "YES") {
    sheet5.showSheet();
  }
  
  if (cell5.getValue() == "NO") {
    sheet6.hideSheet();
  }

  if (cell5.getValue() == "YES") {
    sheet6.showSheet();
  }
  
  if (cell6.getValue() == "NO") {
    sheet7.hideSheet();
  }

  if (cell6.getValue() == "YES") {
    sheet7.showSheet();
  }
  
  if (cell7.getValue() == "NO") {
    sheet8.hideSheet();
  }

  if (cell7.getValue() == "YES") {
    sheet8.showSheet();
  }
  
  if (cell8.getValue() == "NO") {
    sheet9.hideSheet();
  }

  if (cell8.getValue() == "YES") {
    sheet9.showSheet();
  }
  
  
  if (cell9.getValue() == "NO") {
    sheet10.hideSheet();
  }

  if (cell9.getValue() == "YES") {
    sheet10.showSheet();
  }
  
   
  if (cell10.getValue() == "NO") {
    sheet11.hideSheet();
  }

  if (cell10.getValue() == "YES") {
    sheet11.showSheet();
  }
  
 

}

This video provides an overview of the SST referral System.