Behavior Referral System

A client needed a Behavior Referral system that allowed for the following:

  • Teachers needed to be able to fill out the referral form
  • The form needed to be automatically sent to the administrator
  • The administrator needed to be able to edit the referral document, add the follow up information and consequence, and share it to the teacher
  • We wanted all the data to be in the form spreadsheet.
  • So we built a system where all this happens with the form, so that all data is on the sheet.

Here is what we built:

  • A google form with the behavior referral information
    • The google form branches at the end with a question (Are you an administrator? If no, continue on and submit form. The section that comes after a Yes answer is used by the administrator to add the follow up data.)
  • Form Publisher creates a document from the template
  • A script to add the Form Edit URL to the form response sheet, so the admin can go back into the form, change the 'Are you an administrator question' to yes, and continue on to answer the followup questions which will then be both on the google doc as well as the spreadsheet.
  • Form Publisher then automatically republishes the document with the updated information.
  • Then we build a data studio project to share the document URL so staff can see what they have submitted, and what the Administrator has done in the followup.

This is the script that will add the Form Edit URL to the Form Response sheet. Once you have added this to your sheet with the script editor, you have to add a trigger for the script From spreadsheet/On form submit so that this URL gets added automatically to the sheet each time.

function assignEditUrls() {
  var form = FormApp.openById('1EKBWFwkn0gshRwxYo-HOqn83ObPK_mQgxdMQhH0BS5o'); //enter form ID here

  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('data'); //Change the sheet name as appropriate

  var data = sheet.getDataRange().getValues();
  var urlCol = 29; // column number where URL's should be populated; A = 1, B = 2 etc
  var responses = form.getResponses();
  var timestamps = [], urls = [], resultUrls = [];
  
  for (var i = 0; i < responses.length; i++) {
    timestamps.push(responses[i].getTimestamp().setMilliseconds(0));
    urls.push(responses[i].getEditResponseUrl());
  }
  for (var j = 1; j < data.length; j++) {

    resultUrls.push([data[j][0]?urls[timestamps.indexOf(data[j][0].setMilliseconds(0))]:'']);
  }
  sheet.getRange(2, urlCol, resultUrls.length).setValues(resultUrls);  
}

Form

Template

Behavior Support Request Template Redtail

Form Response Spreadsheet

Behavior Support Request (Responses)

As more data comes into the sheet, more metrics of use can be added to the data studio project, so that you can filter on students, schools, locations, etc. Scorecards and charts can add more data as well. In this example, we are showing the form edit URL, but in a live environment that will only be shared with the admin. In this example, as the form is locked to my domain, you cannot edit the form, but you can see the finished doc.