Speed Blogging: Detention Tracking System

I saw a post on one of my Google+ communities where +Patrick Reid was asking about a script for a Detention Tracking system based on students showing up for tutoring.  I just learned about how to write to a cell while writing a different script (that I will blog about later) so I whipped up a quick script to do what he was asking.

Here is the spreadsheet (you can make a copy): http://goo.gl/NCgXQY

When students don't show up for tutoring, the teacher will mark an "n" in the column. These students need to be assigned detention.

The script is run from the detentionTracker menu

The script will assign the student detention on the Detention List tab and will fill in the column to make sure they are only assigned 1 time (otherwise, they would be assigned detention every time the script was run).

Here is the script:

// The onOpen function is executed automatically every time a Spreadsheet is loaded
 function onOpen() {
   var ss = SpreadsheetApp.getActiveSpreadsheet();
   var menuEntries = [];
   menuEntries.push({name: "Assign Detention to students who have not shown up for tutoring", functionName: "detentionTracker"});
   menuEntries.push(null); // line separator
   // add more entries to the menu by using the method: menuEntries.push({name: "Menu Entry 2", functionName: "function2"});

   ss.addMenu("detentionTracker menu", menuEntries);
 }



function detentionTracker() {
  //Get the data, taken from:  https://developers.google.com/apps-script/guides/sheets#reading
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];
  var detentionSheet = ss.getSheets()[1];


  // Get the range of cells from that sheet
  var studentInformationRange = sheet.getRange(2,1,sheet.getMaxRows()-1,sheet.getMaxColumns());

  // For every row of student data, generate a student object.
  var studentObjects = getRowsData(sheet, studentInformationRange);
  for (var i = 0; i < studentObjects.length; ++i)
  {
   var currentStudentObject = studentObjects[i];  //get the ith row of data from the studentObjects array
   var currentStudentAttendTutoring = currentStudentObject.didTheyShowUp;

   if (currentStudentAttendTutoring =="n") //if they did not show up (there is an "n" in the column) then put them on the detention list, otherwise continue on to the next student
   {
     if (currentStudentObject.detentionAssigned !== 'Yes, detention has been assigned.') //check to make sure they have not been assigned yet
   {
      //Find the first empty row, thanks to Don Kirkby at Stack Overflow at http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script
    var column = detentionSheet.getRange('A:A');
    var values = column.getValues(); // get all data in one call
    var ct = 0;
    while ( values[ct][0] != "" ) {
     ct++;
    };
    var firstEmptyRow = ct+1;
    // firstEmptyRow is the first empty row

     //Fill the detention list starting with the first Empty Row in the detention Sheet.
     var detentionFirstNameFillCell = String('A'+ firstEmptyRow ); // you can change the column it writes by changing the 'A'
     var detentionFirstNameFill = detentionSheet.getRange(detentionFirstNameFillCell).setValue(currentStudentObject.firstName);
     var detentionLastNameFillCell = String('B'+ firstEmptyRow );
     var detentionLastNameFill = detentionSheet.getRange(detentionLastNameFillCell).setValue(currentStudentObject.lastName);
     var detentionEmailAddressFillCell = String('C'+ firstEmptyRow );
     var detentionEmailAddressFill = detentionSheet.getRange(detentionEmailAddressFillCell).setValue(currentStudentObject.emailAddress);
     //fill Detention Assigned Column on the tutoring list so the student is only added 1 time
     var studentDetentionAssignedFillCell = String('E'+ (i+2) );
     var studentDetentionAssignedFill = sheet.getRange(studentDetentionAssignedFillCell).setValue('Yes, detention has been assigned.');
      }
    else{
    continue; //this is the else part of the if loop continue on to the next student, used when they need detention but have already been assigned.
    };
  }
    continue; //this makes the else part of my if loop continue on to the next student, used when the student Attend Tutoring is blank
  };
 
  }