Custom spreadsheet to track students missing an ID

assignCons.gs

/**
  * @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");
          }
        }
      }
    }
}

dailyGroups.gs

/**
  * 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");
  }
}

findDates.gs

/** 
  * 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);
}

globalsAndUi.gs

/**
  * 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');
}