Skip to content

Instantly share code, notes, and snippets.

@hinklefoxmail hinklefoxmail/StudentData.gs Secret
Last active Mar 9, 2017

Embed
What would you like to do?
Updates student records, calculates GPAs, and provides an overview sheet for school admin
function updateStudentData(){
//Opens the student grades files
var file = DriveApp.getFileById('1GK_h7felFYckgK4J7bdpMhJE_CTO1wS_1_120fnQIXA');
var studentDataTemplate = DriveApp.getFileById('1COq76_rQzGUvme5FPKj_Rkf4c461P7MRozM6TuMZrUw');
//Opens the folder to save the data in or creates the folder
var folderIt = DriveApp.getFoldersByName('Student Data from Parse');
if (folderIt.hasNext()){
var folder = folderIt.next();
}
else{
var folder = DriveApp.createFolder('Student Data from Parse');
}
var todayDate = new Date();
var today = Utilities.formatDate(todayDate, 'UTC+01:00', "yyyy-MM-dd");
//Gets a template file with pre-configured pie chart
var newFile = file.makeCopy(today+' Student Data', folder);
var newSheet = SpreadsheetApp.open(newFile).getSheets()[0];
//Gets student data
var sheet = SpreadsheetApp.getActiveSheet();
var studentData = sheet.getDataRange();
//Set up varables
var grade = '';
var gradeSum = 0;
var totalGrades = 0
var className = '';
var percentGrade = 0;
var classNames = [];
var percentGrades = [];
var totalRows = sheet.getMaxRows();
var currentStudent = sheet.getRange(2, 2).getValue();
//Goes through the sheet
for (var i = 2; i < totalRows+1; i++){
var currentRow = sheet.getRange(i, 2, 1, 8).getValues();
//If the student name is the same, adds the class name and percent grade to the array
if (currentStudent == currentRow[0][0]){
grade = currentRow[0][1];
className = currentRow[0][4];
classNames.push(className);
percentGrade = currentRow[0][3];
percentGrades.push(percentGrade);
//Adds to the grade total and counts number of grades given to calculate the current grade
var numberGrade = checkGrade(currentRow[0][2]);
if (numberGrade < 100){
gradeSum = gradeSum + numberGrade
totalGrades++
}
}
//If the student name is differnet because the file has moved to the next student
else{
if (i > 2){
//Calculates the GPA and adds it to the GPA tracking sheet
var GPA = (Math.round((gradeSum / totalGrades)*100)/100);
newSheet.appendRow([currentStudent,grade,GPA]);
//Opens the student records and adds the grades to them
var studentFileIt = DriveApp.getFilesByName('Student Data for '+currentStudent);
if (studentFileIt.hasNext()){
var studentFile = studentFileIt.next();
var studentDataSS = SpreadsheetApp.open(studentFile);
}
else{
var studentFile = studentDataTemplate.makeCopy('Student Data for '+currentStudent, folder);
var studentDataSS = SpreadsheetApp.open(studentFile);
studentDataSS.appendRow(classNames);
}
studentDataSS.getSheets()[0].appendRow(percentGrades);
Logger.log(studentDataSS.getUrl());
//and then resets the varables
currentStudent = currentRow[0][0]
gradeSum = 0
totalGrades = 0
var classNames = [];
var percentGrades = [];
className = currentRow[0][4];
classNames.push(className);
percentGrade = currentRow[0][3];
percentGrades.push(percentGrade);
var numberGrade = checkGrade(currentRow[0][2]);
if (numberGrade < 100){
gradeSum = gradeSum + numberGrade
totalGrades++
}
}
}
}
}
function checkGrade(grade){
if (grade == 'A'){
return 4
}
if (grade == 'B'){
return 3
}
if (grade == 'C'){
return 2
}
if (grade == 'D'){
return 1
}
if (grade == 'F'){
return 0
}
else{
return 100
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.