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