Learn new Google Workspace Skills
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.
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.
//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();
}
}