Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Custom spreadsheet to track students missing an ID
/**
* @param {Obj} a - array from daily list built
**/
function assignCons(a) {
// These rely on the global variables set at the top of the script
var sheet = ss.getSheets()[2];
var data = sheet.getDataRange().getValues();
var target = ss.getSheets()[3].getRange("A2").getValue();
// Loop through the students needing consequences
for(var i=0; i<a.length;i++) {
// Loop through the data in the noID sheet to find that student
for(var j=1;j<data.length;j++) {
if(a[i][0] === data[j][0]) {
// Match the target and set the appropriate consequence in the correct column.
if(target === 5) {
sheet.getRange(j+1,5).setValue("1/2 day ISS");
} else if (target === 8) {
sheet.getRange(j+1,6).setValue("Full day ISS");
} else if(target > 11) {
sheet.getRange(j+1,7).setValue("Notify principal");
}
}
}
}
}
/**
* Creates an array of students needing action based on the number of missing IDs
* @param {String} target - Checks the number of missing IDs and returns only
* names that are >= the target
* @return {Obj} array - Creates an object to send to the range to populate.
**/
function buildDailyList() {
// Remember these are based on the global variables set at the top of the script editor.
var sheet = ss.getSheets()[2];
var data = sheet.getDataRange().getValues();
var target = ss.getSheets()[3].getRange("A2").getValue();
// initialize an empty array
var array = new Array();
// Check the target and the assigned consequence. If already assigned discipline, skip.
for(var i=0;i<data.length;i++) {
// Build the object to push to the array
var stu = [data[i][0], data[i][1], data[i][2]];
// Check the benchmarks and push the student name only if the consequence for the target has not been assigned.
if(target === 5) {
if(data[i][3] >= 5 && data[i][4] == 0) {
array.push(stu);
}
} else if(target === 8) {
if(data[i][3] >= 8 && data[i][5] == 0) {
array.push(stu);
}
} else if(target === 11) {
if(data[i][3] === 11 && data[i][6] == 0) {
array.push(stu);
}
} else if(target >= 12) {
if(data[i][3] >= target) {
array.push(stu);
}
}
}
dailyList(array);
}
/**
* @param {Obj} a - Data built by buildDailyList()
* @param {Error} e - Catch empty array errors and display pop up notice
* @return {Obj} a - Pass the array to assignCons()
**/
function dailyList(a) {
try {
ss.getSheets()[3].getRange(3,2,ss.getSheets()[3].getLastRow(),3).clearContent().clearNote();
Logger.log(a.length);
var range = ss.getSheets()[3].getRange(3,2,a.length,3);
range.setValues(a);
assignCons(a);
} catch(e) {
ui.alert("No students matched");
}
}
/**
* Find the dates listed for a selected ID number.
* @param {ID} range value
* @return [array] dates matched to ID
**/
// Loop through the dates to find the ID match
function findDates() {
sheet.getActiveCell().clearNote();
var idToMatch = sheet.getActiveCell().getValue();
var range = ss.getSheetByName("raw").getDataRange();
var ids = range.getValues();
// Initialize an empty array to hold returned dates from the loop
var dates = [];
for(var i=0;i<ids.length;i++) {
// If the ID in the range matches your selected ID, push it into the array
if(ids[i][1] == idToMatch) {
var date = dates.push(" " + Utilities.formatDate(ids[i][0], "EDT", "MM-dd-yy"));
}
}
// Send the array with dates to add the note to the cell
addNote(dates);
}
/**
* @param {Array} e - returned from findDates()
* @return {Object} e - pushed to Google Sheet and added as a note to selected cell
**/
function addNote(e) {
var cell = sheet.getActiveCell();
cell.setNote(e);
}
/**
* Define the global variables to be used by all functions
* Call the UI library and create menus
**/
// Globals
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getActiveSheet();
var ui = SpreadsheetApp.getUi();
// Ui
function onOpen() {
ui.createMenu("Sheet Functions")
.addItem("Find No ID Dates","findDates")
.addItem("Daily List","buildDailyList")
.addSeparator()
.addItem("Help","helpText")
.addToUi();
}
function helpText() {
var htmlOutput = HtmlService
.createHtmlOutput('<p style="font-family:sans-serif">Highlight a student\'s ID and select <b>Run</b> from the <b>Get Dates</b> menu above. The dates a student is logged will be added as a note to the cell.</p><br /><p style="font-family:sans-serif">Look at the dates by hovering over the cell with the note.</p>')
.setWidth(250)
.setHeight(300);
ui.showModalDialog(htmlOutput, 'Help');
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment