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.
Form Publisher now includes the Form Edit URL if the Form is setup to edit after submission, so this is no longer needed for this project. But it can be useful to include in other forms that you want to manage.
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);
}
Data Studio View
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.