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.
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
};
}
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
};
}
