Grade Database

//Start time of the code, was never used

var startTime = Date.now()

//Spreadsheet variables

const ss = SpreadsheetApp.getActiveSpreadsheet()

const overviewWS = ss.getSheetByName("Overview")

const dataWS = ss.getSheetByName("Data")

const userInput = ss.getSheetByName("User Input")

//The main cookie variable to log the user in

var cookie = ""

//A variable commonly used across functions

var source = ""

//Parts of the cookie

var _csrf_ = ""

var session_id = ""

var legacy_session = ""

var legacy = ""

var canvas_session = ""

//Gets user inputs

userInputMaxRange = userInput.getRange(1,1,userInput.getMaxRows(),userInput.getMaxColumns())

statusRow = userInputMaxRange.createTextFinder('Status').findNext().getRow()

statusCol = userInputMaxRange.createTextFinder('Status').findNext().getColumn() + 1

statusRange = userInput.getRange(statusRow, statusCol)

//Sets status block to red

statusRange.setBackground('red')

allGrades = userInputValue("All Grades")

var courses = [];

function main() {

  //Gets the cookie thats stored in the sheet

  cookieRow = userInputMaxRange.createTextFinder('Cookie').findNext().getRow()

  cookieCol = userInputMaxRange.createTextFinder('Cookie').findNext().getColumn() + 1

  //Gets the password thats stored in the sheet

  passwordRow = userInputMaxRange.createTextFinder('Password').findNext().getRow()

  passwordCol = userInputMaxRange.createTextFinder('Password').findNext().getColumn() + 1

  range = userInput.getRange(passwordRow, passwordCol)

  //Hides the password by making it a black text on a black background

  range.setBackground('black')

  range.setFontColor('black')

  //Initializes cookie

  cookie = userInputValue('Cookie')

  //Gets course ID's

  getCoursesID();

  //If it failed

  if (courses.length == 0)

  {

    console.log("Request didn't work")

    //Log the user in

    logIn(userInputValue('Username'), userInputValue('Password'))

    console.log("Logged In")

    //Save new cookie

    userInput.getRange(cookieRow, cookieCol).setValue(cookie);

    //Get course ID's

    getCoursesID();

  }

  console.log("Got Id's")

  //Get course Names

  getCoursesName();

  console.log("Got Names")

  //Get course Assignments

  getGrades();

  console.log("Got Grades")

  //Get assignment names

  getAssignmentName();

  console.log("Got Assignment Names")

  //Get dropped input

  dropped = userInputValue("Drop")

  //If drop grades then drop grades

  if (dropped)

  {

    dropGrades();

    console.log("Dropped Grades")

  }

  //Outputs the assignments

  outputData();

  console.log("Outputed Data")

  //Formats User Input sheet

  formatSheet(userInput)

  col = userInput.getRange(1,1,1,userInput.getMaxColumns()).createTextFinder("Function").matchCase(true).matchEntireCell(true).findNext().getColumn()

  userInput.setColumnWidths(col, 1, 600)

  userInput.getRange(1,col, userInput.getMaxRows()).setWrapStrategy(SpreadsheetApp.WrapStrategy.WRAP)

  userInput.getRange(cookieRow,cookieCol, 1).setWrapStrategy(SpreadsheetApp.WrapStrategy.CLIP)

  userInput.setColumnWidths(cookieCol, 1, 100)

  //Calculates Grades

  calculateGrade()

  console.log("Calculated Grade")

  //Outputs Overview sheet

  outputOverview()

  console.log("Outputed Overview")

  //Sets status bar to green

  statusRange.setBackground('green')

}

//Finds the value next to a inputed name for user inputs, Ex. "Drop" returns if it is true or false

function userInputValue(value) {

  userInputMaxRange = userInput.getRange(1,1,userInput.getMaxRows(),userInput.getMaxColumns())

  row = userInputMaxRange.createTextFinder(value).findNext().getRow()

  col = userInputMaxRange.createTextFinder(value).findNext().getColumn() + 1

  return userInput.getSheetValues(row, col, 1, 1)[0][0]

}

//Logs the user into Canvas givin a Username and Password

function logIn(username, password) {

  //Creats the request object

  var request = {

    'method' : 'get',

    'followRedirects' : false

  }

  //Follows first set of redirects to the login page sending the new cookie each time

  headers = UrlFetchApp.fetch("https://canvas.vt.edu/", request).getAllHeaders()

  while (headers['Location'] != undefined)

  {

    url = headers['Location']

    request.headers = {'cookie': headers['Set-Cookie']}

    response = UrlFetchApp.fetch(url, request)

    headers = response.getAllHeaders()

  }

  //Get source code until it gets a succesful attempt

  source = response.getContentText()

  while (source.indexOf("You did something we can't handle. Common causes") > 0)

  {

    source = UrlFetchApp.fetch(url, request).getContentText()

  }

  //Modifies request

  request.method = 'post'

  request.payload = {'j_username' : username, 'j_password' : password, '_eventId_proceed' : "", 'execution' : 'e1s1'}

  //Get headers until it gets a succesful attempt

  headers = UrlFetchApp.fetch(url, request).getAllHeaders()

  while(headers['Location'] == undefined)

  {

    headers = UrlFetchApp.fetch(url, request).getAllHeaders()

  }

  //Modifies request

  request.method = 'get'

  request.payload = {'execution' : 'e1s2'}

  //Get source code until it gets a succesful attempt

  source = UrlFetchApp.fetch(headers['Location'], request).getContentText()

  while (source.indexOf("You did something we can't handle. Common causes") > 0)

  {

    source = UrlFetchApp.fetch(headers['Location'], request).getContentText()

  }

  //Gets data found in the source code

  search = 'data-sig-request="'

  source = source.substring(source.indexOf(search) + search.length)

  tx = source.substring(0, source.indexOf(':APP'))

  app = source.substring(source.indexOf(':APP'), source.indexOf('"'))

  //Modifies request

  request.method = 'post'

  request.payload = {'tx' : tx, 'parent' : url}

  //Gets sid for Duo Authentication

  headers = UrlFetchApp.fetch('https://api-dc4a4e89.duosecurity.com/frame/web/v1/auth?', request).getAllHeaders()

  sid = headers['Location']

  sid = sid.substring(sid.indexOf('=') + 1)

  sid = sid.replace(/%7C/g, "|")

  index = sid.indexOf('|')

  sid = sid.replace('%3D', '=')

  //Modifies request

  request.payload = {'sid' : sid, 'device' : 'phone1', 'factor' : 'Duo Push'}

  //Send user Duo authentication prompt

  txid = JSON.parse(UrlFetchApp.fetch('https://api-dc4a4e89.duosecurity.com/frame/prompt', request).getContentText()).response.txid

  //Modifies request

  request.method = 'get'

  request.payload = {'sid' : sid, 'txid' : txid}

  //Sets status bar to yellow

  statusRange.setBackground('yellow')

  SpreadsheetApp.flush()

  console.log("Duo Authenticate")

  //Waits for the user to accept

  status = JSON.parse(UrlFetchApp.fetch('https://api-dc4a4e89.duosecurity.com/frame/status', request).getContentText())

  while(status.response.status_code == 'pushed')

  {

    statusRange.setBackground('yellow')

    SpreadsheetApp.flush()

    status = JSON.parse(UrlFetchApp.fetch('https://api-dc4a4e89.duosecurity.com/frame/status', request).getContentText())

  }

  //Modifies request

  request.method = 'post'

  //Sends the acceptance

  cookie = JSON.parse(UrlFetchApp.fetch('https://api-dc4a4e89.duosecurity.com' + status.response.result_url, request).getContentText()).response.cookie

  //Sets status bar back to red

  statusRange.setBackground('red')

  SpreadsheetApp.flush()

  //Modifies request

  request.payload = {'sig_response': cookie + app, '_eventId' : 'proceed'}

  //Get heaers until it gets a succesful attempt

  headers = UrlFetchApp.fetch("https://login.vt.edu/profile/cas/login?execution=e1s2", request).getAllHeaders()

   while(headers['Location'] == undefined)

  {

    headers = UrlFetchApp.fetch("https://login.vt.edu/profile/cas/login?execution=e1s2", request).getAllHeaders()

  }

  //Modifies request

  request.payload = undefined

  request.method = 'get'

  //Follows second set of redirects sending the new cookie each time

  while (headers['Location'] != undefined)

  {

    url = headers['Location']

    cookie = headers['Set-Cookie']

    //Sets cookie variables if it is a array or not

    if (Array.isArray(cookie))

    {

      cookie.map(f => setCookieVariables(f))

    }

    else

    {

      setCookieVariables(cookie)

    }

    //Gets new headers

    request.headers = {'cookie': _csrf_ + session_id + legacy_session + legacy + canvas_session}

    response = UrlFetchApp.fetch(url, request)

    headers = response.getAllHeaders()

  }

  //Sets main cookie variable

  headers['Set-Cookie'].map(f => setCookieVariables(f))

  cookie = _csrf_ + session_id + legacy_session + legacy + canvas_session

}

function setCookieVariables(cookie) {

  //gets tag

  tag = cookie.substring(0, cookie.indexOf('='))

  //Deletes things that make it not work

  cookie = cookie.replace(new RegExp(' path=/;', 'g'), "")

  cookie = cookie.replace(new RegExp(' Path=/;', 'g'), "")

  cookie = cookie.replace(new RegExp(' HttpOnly; SameSite=None', 'g'), "")

  cookie = cookie.replace(new RegExp(' secure; HttpOnly', 'g'), "")

  //Sets the appropriate variable

  if (tag == '_csrf_token')

      {

        _csrf_ = cookie;

      }

      else if (tag == 'log_session_id')

      {

        session_id = cookie

      }

      else if (tag == '_legacy_normandy_session')

      {

        legacy = cookie

      }

      else if (tag == 'canvas_session')

      {

        canvas_session = cookie;

      }

}

function getCoursesID() {

  //Get source code

  source = getSourceCode("https://canvas.vt.edu/courses")

  var course = source;

  search = '<a href="/courses/';

  while (course.length > 0)

  {

    //Gets starting index

    start = course.search(search) + search.length;

    //Breaks if invalid

    if (start - search.length < 0)

    {

      break;

    }

  //Gets end index

  end = course.substring(start).search('"') + start;

  //Adds course id

  courses.push({'id' : course.substring(start, end)});

  course = course.substring(end);

  }

}

function getCoursesName() {

  //For every course ID

  for (var i = 0; i < courses.length; i++)

  {

    //Gets starting position

    var search = "/courses/" + courses[i].id

    var str = source.substring(source.search(search));

    //Get name 

    search = 'title="';

    str = str.substring(str.search(search) + search.length);

    //Records name

    courses[i].name = (str.substring(0, str.search('">')))

  }

}

function getGrades() {

  //If not getting all grades

  if (!allGrades)

  {

    //Gets the User Inputed grade

    gradeCol = userInput.getRange(1,1,1,userInput.getMaxColumns()).createTextFinder("Grade").matchCase(true).matchEntireCell(true).findNext().getColumn()

  }

  //For every course

  for (var i = 0; i < courses.length; i++)

  {

    //If not all grades

    if (!allGrades)

    {

      //For every course with a grade in a row

      grade = 'A'

      while (grade.length > 0 && i < courses.length)

      {

        //Get inputed grade

        gradeRow = userInput.getRange(1,1,userInput.getMaxRows(),1).createTextFinder(courses[i].name).matchCase(true).matchEntireCell(true).findNext().getRow()

        grade = userInput.getSheetValues(gradeRow, gradeCol, 1, 1)[0][0]

        //Record grade

        if (grade.length > 0)

        {

          courses[i].letter = grade

          i++

        }

      }

      //Break if last course, to reduce execution time

      if (i >= courses.length)

      {

        break

      }

    }

    //Initalizes variables

    courses[i].weights = []

    source = getSourceCode("https://canvas.vt.edu/courses/" + courses[i].id + '/grades')

    totalAssignments = [];

    //Get starting position

    start = "ENV = "

    source = source.substring(source.indexOf(start))

    end = "};"

    //Converts to object

    const temp = JSON.parse(source.substring(source.indexOf(start) + start.length, source.indexOf(end) + 1))

    //Gets select pices of information

    graded = temp.submissions

    weights = temp.assignment_groups

    courses[i].grading_scheme = temp.grading_scheme

    //For every weight

    for (var j = 0; j < weights.length; j++)

    {

      weight = weights[j]

      assignments = weight.assignments

      //For every assignment

      for (var k = 0; k < assignments.length; k++)

      {

        assignment = assignments[k]

        assignment.id = parseInt(assignment.id)

        //For every graded assignments

        for (var l = 0; l < graded.length; l++)

        {

          gradedAssignment = graded[l]

          gradedAssignment.assignment_id = parseInt(gradedAssignment.assignment_id)

          //If it matches the assignment

          if (gradedAssignment.assignment_id == assignment.id)

          {

            //Merges all data to one variable

            delete assignment.id

            assignment = Object.assign(assignment, gradedAssignment)

            break;

          }

        }

        //Adds more data

        assignment.weight = weight.group_weight

        assignment.weightID = weight.id

        totalAssignments.push(assignment)

      }

    }

    //Adds more data

    courses[i].rawWeights = weights

    courses[i].assignments = totalAssignments

  }

}

//Function to return a section of a string based on start and end strings

function getSection(source, start, end) {

  return source.substring(source.indexOf(start) + start.length, source.indexOf(end))

}

function getAssignmentName() {

  //For every course

  for(var i = 0; i < courses.length; i++)

  {

    //Get source code

    var source = getSourceCode("https://canvas.vt.edu/courses/" + courses[i].id + '/grades')

    source = source.substring(source.indexOf("</script><script>"))

    //For every assignment in a course that dosn't already have a grade

    for(var j = 0; courses[i].letter == undefined && j < courses[i].assignments.length; j++)

    {

      assignment = courses[i].assignments[j];

      //Gets starting position

      temp = source.substring(source.indexOf('/assignments/' + assignment.assignment_id))

      //Record assignment name and weight category

      name = getSection(temp, '">', '</a>')

      assignment.weight_category = getSection(temp, '<div class="context">', '</div>')

      assignment.name = name;

    }

    //Gets weights if their is a table

    weights = [];

    while (courses[i].letter == undefined && source.indexOf("'row'") >= 0)

    {

      source = source.substring(source.indexOf("<th scope='row'>"))

      //Records the name and weight of the weight category

      weights.push({'name' : getSection(source, "'row'>", '</th>'), 'weight' : getSection(source, "<td>", "%</td>")})

      source = source.substring(source.indexOf("</td>") + 5)

    }

    //Record weights

    courses[i].weights = weights

  }

}

//Gets the source code of a Canvas url based on a inputed ulr

function getSourceCode(url) {

  var request = {

    'method' : 'get',

    'headers' : {'cookie' : cookie}

  }

  return UrlFetchApp.fetch(url, request).getContentText()

}

function outputOverview() {

  //Clears sheet

  overviewWS.clear()

  //Initilizes headers

  totalAdd = [['Class', 'Grade', 'Letter',  'Quality Credits',  'Credit Hours', 'Quality Hours']];

  //Get credit column from the User Input sheet

  creditColumn = userInput.getRange(1,1,1,userInput.getMaxColumns()).createTextFinder("Credit").matchCase(true).matchEntireCell(true).findNext().getColumn()

  qualtiyHourSum = 0

  creditHourSum = 0;

  //For every course

  for (var i = 0; i < courses.length; i++)

  {

    course = courses[i]

    //Gets credit hour row based on course name

    creditRow = userInput.getRange(1,1,userInput.getMaxRows(),1).createTextFinder(course.name).matchCase(true).matchEntireCell(true).findNext()

    //Sees if the course is on User Input sheet

    if (creditRow == null)

    {

      //If not get a random answer

      creditRow = userInput.getMaxColumns()

    }

    else

    {

      //If so gets the credit

      creditRow = creditRow.getRow()

    }

    credit = userInput.getSheetValues(creditRow, creditColumn, 1, 1)[0][0]

    //If it is a random answer make it zero

    if (typeof credit == 'string')

    {

      credit = 0

    }

    //Records the credit hours

    course.creditHours = credit

    //Adds data to the add array

    add = []

    add.push(course.name)

    add.push(course.grade)

    add.push(course.letter)

    add.push(course.quality_credit)

    add.push(credit)

    //Totals the amount of credits for GPA calculation

    creditHourSum += credit

    //Sets quality credits for a course

    if (course.quality_credit == null)

    {

      //If so then make it null to be blank when outputted

      add.push(null)

    }

    else

    {

      //If not equal quality credit * credit for GPA calculation

      add.push(course.quality_credit * credit)

    }

    //Totals quality credits for GPA calculation

    qualtiyHourSum += course.quality_credit * credit

    //Adds to the total add to be outputted

    totalAdd.push(add)

  }

  //Outputs overview

  overviewWS.getRange(1,1,totalAdd.length, totalAdd[0].length).setValues(totalAdd)

  //Outputs GPA and total credit hours

  add = [["Credit Hours:", creditHourSum], ["GPA:", qualtiyHourSum / creditHourSum]]

  overviewWS.getRange(1, totalAdd[0].length + 2, add.length, add[0].length).setValues(add)

  formatSheet(overviewWS)

}

function outputData() {

  dataWS.clear()

  //Initalizes Headers

  totalAdd = [['Class ID', 'Class Name', 'Assignment ID', 'Assignment Name', 'Omit From Grade','Due Date', 'Score', 'Possible Score', 'Percent', 'Weight Category', 'Weight']]

  //Get weight column number

  weightColumn = userInput.getRange(1,1,1,userInput.getMaxColumns()).createTextFinder("Function").matchCase(true).matchEntireCell(true).findNext().getColumn()

  //For every course

  for(var i = 0; i < courses.length; i++)

  {

    course = courses[i]

    //Get weight row

    weightRow = userInput.getRange(1,1,userInput.getMaxRows(),1).createTextFinder(courses[i].name).matchCase(true).matchEntireCell(true).findNext()

    //If it isn't a valid value

    if (weightRow == null)

    {

      //Set to a random value

      weightRow = userInput.getMaxColumns()

    }

    else

    {

      //Else set to the correct value

      weightRow = weightRow.getRow()

    }

    //For all assignments for a course without a letter already

    for(var j = 0; course.letter == undefined && j < courses[i].assignments.length; j++)

    {

      //Get weight

      weights = userInput.getSheetValues(weightRow, weightColumn, 1, 1)[0][0]

      add = []

      assignment = course.assignments[j]

      //Adds values to add to ouput on the sheet

      add.push(course.id)

      add.push(course.name)

      add.push(assignment.assignment_id)

      add.push(assignment.name)

      add.push(assignment.omit_from_final_grade)

      dueDate = new Date(assignment.due_at)

      add.push(dueDate.toLocaleString())

      add.push(assignment.score)

      add.push(assignment.points_possible)

      //Caclulates percent

      add.push((assignment.score / assignment.points_possible) * 100)

      var weight = null

      if (weights.toLowerCase() == "points")

      {

        //If points sets weight to points

        courses[i].weights = "Points"

      }

      else if (weights.toLowerCase() == "trust")

      {

        //If trust sets weight to the found weight on Canvas

        weight = assignment.weight

      }

      else

      {

        //Else sets weight based on User Input

        if (weights.length > 0)

        {

          //Parses the imputed weights to convert to a array of objects

          weights = JSON.parse(weights)

          courses[i].weights = weights

        }

        //For each weight category

        for (var k = 0; k < weights.length; k++)

        {

          //If the assignments name contains the valid names for the weight

          if (contains(assignment.name, weights[k].contain))

          {

            //Record weight information

            courses[i].assignments[j].weight_category = weights[k].name

            weight = weights[k].weight

            break;

          }

        }

      }

      add.push(assignment.weight_category)

      add.push(weight)

      totalAdd.push(add)

    }

  }

  //Output totalAdd to Data sheet

  dataWS.getRange(1,1, totalAdd.length, totalAdd[0].length).setValues(totalAdd)

  formatSheet(dataWS)

}

//Formats a givin sheet

function formatSheet(sheet) {

  sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns()).setWrap(false)

  sheet.autoResizeColumns(1,sheet.getMaxColumns())

  sheet.getRange(1,1,sheet.getMaxRows(), sheet.getMaxColumns()).setVerticalAlignment("middle")

  sheet.getRange(1,1,sheet.getMaxRows(),sheet.getMaxColumns()).setHorizontalAlignment("center")

}

function calculateGrade() {

  //For every course

  for (var i = 0; i < courses.length; i++)

  {

    course = courses[i]

    course.grade = 0

    //If not points

    if (course.weights != "Points")

    {

      weightSum = 0;

      //For every weight

      for (var j = 0; j < course.weights.length; j++)

      {

        //Get and set information

        weight = course.weights[j]

        weight.score = 0;

        weight.points_possible = 0;

        weight.weight = parseInt(weight.weight)

        //For every assignment that has a course not have a letter and a weight that isn't zero

        for (var k = 0; course.letter == undefined && k < course.assignments.length && weight.name != "Total" && weight.weight != 0; k++)

        {

          assignment = course.assignments[k]

          //If counts for final grade and maches weight category

          if (!assignment.omit_from_final_grade && assignment.weight_category == weight.name && assignment.score != null)

          {

            //Accumalate data for grade calculation

            weight.score += assignment.score

            weight.points_possible += assignment.points_possible

          }

        }

        //If possibly points for a weight isn't zero

        if (weight.points_possible != 0)

        {

          //Then add to weightSum

          weightSum += weight.weight

          //Add the percent times weight for the weighted average

          course.grade += (weight.score / weight.points_possible) * weight.weight

        }

      }

      //If weightSum isn't zero

      if (weightSum != 0)

      {

        //Caclulate grade percent

        course.grade /= weightSum

        course.grade *= 100

      }

      else

      {

        //Else set to null

        course.grade = null

      }

    }

    else

    {

      //If points course

      course.score = 0

      course.points_possible = 0;

      //For every assignment

      for (var k = 0; k < course.assignments.length; k++)

      {

        assignment = course.assignments[k]

        //If counts towerds final

        if (assignment.score != null && !assignment.omit_from_final_grade)

        {

          //Sum up possible score and score

          course.score += assignment.score

          course.points_possible += assignment.points_possible

        }

      }

      //If points possible isn't zero

      if (course.points_possible != 0)

      {

        //Calculate grade

        course.grade = (course.score / course.points_possible) * 100

      }

      else

      {

        //Else set to null

        course.grade = null

      }

    }

    grade = course.grade

    //If grade is null

    if (grade == null && course.letter == undefined)

    {

      //Set other values to null

      course.letter = null

      course.quality_credit = null

    }

    //For each value in grading scheme for a non null grade

    for (var k = 0; course.grading_scheme != undefined && k < course.grading_scheme.length && grade != null; k++)

    {

      letter = course.grading_scheme[k][0]

      percent = course.grading_scheme[k][1]

      //If grade is greater then percent for that letter

      if ((grade / 100) >= percent)

      {

        //Than record letter

        course.letter = letter

        break

      }

    }

    letter = course.letter

    //Gets quality credit number based on grade letter

    if (letter == 'A')

    {

      course.quality_credit = 4.0

    }

    else if (letter == 'A-')

    {

      course.quality_credit = 3.7

    }

    else if (letter == 'B+')

    {

      course.quality_credit = 3.3

    }

    else if (letter == 'B')

    {

      course.quality_credit = 3.0

    }

    else if (letter == 'B-')

    {

      course.quality_credit = 2.7

    }

    else if (letter == 'C+')

    {

      course.quality_credit = 2.3

    }

    else if (letter == 'C')

    {

      course.quality_credit = 2.0

    }

    else if (letter == 'C-')

    {

      course.quality_credit = 1.7

    }

    else if (letter == 'D+')

    {

      course.quality_credit = 1.3

    }

    else if (letter == 'D')

    {

      course.quality_credit = 1.0

    }

    else if (letter == 'F')

    {

      course.quality_credit = 0.0

    }

  }

}

function dropGrades() {

  //For every course

  for (var i = 0; i < courses.length; i++)

  {

    course = courses[i]

    rawWeights = course.rawWeights

    //For every rawWeight item with a course without a letter

    for (var j = 0; course.letter == undefined && j < rawWeights.length; j++)

    {

      rawWeight = rawWeights[j]

      //For the amount of droped grades in the grade category

      for (var k = 0; k < rawWeight.rules.drop_lowest; k++)

      {

        min = course.assignments[0]

        counter = 1;

        //Get valid first value

        while(!(!min.omit_from_final_grade && min.weightID == rawWeight.id) && counter < course.assignments.length)

        {

          min = course.assignments[counter]

          counter++;

        }

        //Finds valid minimum value for a assignment

        for (var l = 0; l < course.assignments.length; l++)

        {

          assignment = course.assignments[l]

          if (((assignment.score / assignment.points_possible) < (min.score / min.points_possible)) && (assignment.weightID == rawWeight.id) && !assignment.omit_from_final_grade)

          {

            min = assignment

          }

        }

        //Sets omit from final to true

        min.omit_from_final_grade = true

      }

    }

  }

}

/**

 * Returns if a string contains any of the values in a array

 *

 * @param {string} input The value to test

 * @param {string[]} the array to test

 * @return if the inputed string contains any value from tests

 */

function contains(string, tests) {

  value = false

  string = string.toLowerCase()

  for (var i = 0; i < tests.length; i++)

  {

    if (string.indexOf(tests[i].toLowerCase()) != -1)

    {

      return true;

    }

  }

  return false

}

//Spreadsheet variables

const ss = SpreadsheetApp.getActiveSpreadsheet()

const overviewWS = ss.getSheetByName("Overview")

const dataWS = ss.getSheetByName("Data")

const courseSettingsWS = ss.getSheetByName("Course Settings")

const settingsWS = ss.getSheetByName("Settings")

//Official Letter grades to quality credits conversion object

const GRADES = {'A' : 4.0, 'A-' : 3.7, 'B+' : 3.3, 'B' : 3.0, 'B-' : 2.7, 'C+' : 2.3, 'C' : 2.0, 'C-' : 1.7, 'D+' : 1.3, 'D' : 1.0, 'D-' : 0.7, 'F' : 0}

//Cookies variable with three functions, to concatinate all values, set cookie, and to add cookies difrently based on if it is a array or not

cookies = {getCookie : function(){sum = '';Object.values(this).map(f => {if(typeof f == 'string'){(sum += f)}});return sum},

setCookie : function(cookie){tag = cookie.substring(0, cookie.indexOf('='));this[tag] = cookie.substring(tag + tag.length, cookie.indexOf(';') + 1)},addCookie: function(cookie){if(Array.isArray(cookie)){cookie.map(f => this.setCookie(f))}else if(cookie != undefined){this.setCookie(cookie)};settings['Cookie'] = this.getCookie()}}

//Setting to obtion conversion object

settings = {}

getSettings()

//Term name to id conversion object

termTable = {}

getTermTable()

//Course initalization object

courses = {}

//main Function

function main() {

  addRegestrationInfo()

  getUserInputCourses()

  //Inializes the double array to output information to Google Sheet

  assignmentsTotalAdd = [['Class Name', 'Assignment Name', 'Omit From Grade', 'Due Date', 'Score',  'Possible Score', 'Percent',  'Weight Category',  'Weight']]

  overviewTotalAdd = [['CRN', 'Class', 'Title','Percent', 'Midterm Grade', 'Official Grade', 'Grade', 'Quality Credits',  'Credit Hours', 'Quality Hours']]

  //For every course

  for (i in courses)

  {

    course = courses[i]

    //Does things with assignments provided that the option is on or the course dosn't have a valid official grade

    if (settings['All Grades'] || GRADES[course.grade] == undefined)

    {

      getCanvasInfo(course)

      addWeight(course)

      addCustomeWeight(course)

      dropGrades(course)

    }

    //Adds information to rspective totalAdds to output to Google Sheet

    addAssignments(course, assignmentsTotalAdd)

    addOverview(course, overviewTotalAdd)

  }

  //Clears, outputs and formats Data sheet

  dataWS.clear()

  dataWS.getRange(1, 1, assignmentsTotalAdd.length, assignmentsTotalAdd[0].length).setValues(assignmentsTotalAdd)

  formatSheet(dataWS)

  dataWS.setColumnWidth(assignmentsTotalAdd[0].indexOf('Percent') + 1, 85)

  dataWS.setColumnWidth(assignmentsTotalAdd[0].indexOf('Due Date') + 1, 160)

  //Clears, outputs and formats Overview sheet

  overviewWS.clear()

  overviewWS.getRange(1, 1, overviewTotalAdd.length, overviewTotalAdd[0].length).setValues(overviewTotalAdd)

  add = [['Credit Hours:'], ['GPA:']]

  quality = getMaxColumnNotation('Quality Hours', overviewTotalAdd, overviewWS)

  credit = getMaxColumnNotation('Credit Hours', overviewTotalAdd, overviewWS)

  add[0].push('=SUMIFS(' + credit + ', ' + credit + ', ">=0", ' + quality + ', ">=0")')

  add[1].push('=SUMIFS(' + quality + ', ' + credit + ', ">=0", ' + quality + ', ">=0")' + ' / ' + add[0][1].substring(1))

  overviewWS.getRange(1, overviewTotalAdd.length, add[0].length, add.length).setValues(add)

  formatSheet(overviewWS)

  overviewWS.setColumnWidth(overviewTotalAdd[0].indexOf('Percent') + 1, 85)

}

//Add information from the regestration to the courses object

function addRegestrationInfo() {

  //For every term the user has

  for (i in termTable)

  {

    registrations = termTable[i].info.registrations

    //For every course taken during that term

    for (j in registrations)

    {

      course = registrations[j]

      //If it isn't added to the courses object

      if (courses[course.name] == undefined)

      {

        //Add it

        courses[course.name] = course

      }

      else 

      {

        //Adds numbers to it until it is a new course to add to the courses object

        index = 1

        originalName = course.name

        while(courses[course.name] != undefined)

        {

          course.name = originalName + ' ' + index

          index++

        }

        courses[course.name] = course

      }

    }

  }

}

//Get all settings from the settings Google Sheet and adds them to the settings object

function getSettings() {

  //Get settings from the Google Sheet and adds them to the settings object

  settingsWS.getRange(1, 1, settingsWS.getLastRow(), settingsWS.getLastColumn()).getValues().map(f => settings[f[0]] = f[1])

  //Sets the cookie variable to the saved cookie

  while (settings['Cookie'].indexOf(';') >= 0)

  {

    index = settings['Cookie'].indexOf(';')

    cookies.setCookie(settings['Cookie'].substring(0, index + 1))

    settings['Cookie'] = settings['Cookie'].substring(index + 1, settings['Cookie'].length)

  }

  settings['Cookie'] = cookies.getCookie()

}

//Get and set the term name to term id conversion object

function getTermTable() {

  var request = {

    method : 'get',

    headers : {'cookie' : settings['Cookie']}

  }

  //Get source code of website

  url = 'https://apps.es.vt.edu/StudentRegistrationSsb/ssb/registrationHistory/registrationHistory'

  source = UrlFetchApp.fetch(url, request).getContentText()

  //If error is due to invalid cookie for login

  if (source.indexOf("You did something we can't handle.") >= 0)

  {

    //Then login

    canvasLogIn(settings['Username'], settings['Password'])

    source = hokieSpaLogin()

  }

  //If error is due to Hokie Spa login expiring

  else if (source.indexOf(' Enter your user name and password, then click Sign In to continue.') >= 0)

  {

    console.log('Failed Login so tryed again')

    //Then login to Hokie Spa

    source = hokieSpaLogin()

  }

  //Adjusts request

  request.followRedirects = false

  request.headers.cookie = settings['Cookie']

  start = '<option value="'

  //Gets the term information from the source code of the website

  while (source.indexOf(start) >= 0)

  {

    source = source.substring(source.indexOf(start) + start.length)

    id = parseInt(source.substring(0, source.indexOf('"')))

    term = source.substring(source.indexOf('>') + 1, source.indexOf('</option>'))

    termTable[term] = {id : id}

    source = source.substring(source.indexOf('</option>'))

  }

  //For ever term found

  for(term in termTable)

  {

    //Gets regestration information

    termTable[term].info = JSON.parse(UrlFetchApp.fetch('https://apps.es.vt.edu/StudentRegistrationSsb/ssb/registrationHistory/reset?term=' + termTable[term].id, request).getContentText()).data

    temp = {}

    //For every course in that term adds name value to course and adds course to temp

    termTable[term].info.registrations.map(course => {course.name = course.subject + " " + course.properties.courseNumber; temp[course.name] = course})

    //Changes regestration array to object for a term

    termTable[term].info.registrations = temp

  }

}

function getCanvasInfo(course) {

  //Try to get source code of grade page

  try {

    source = getSourceCode("https://canvas.vt.edu/courses/" + course['Canvas ID'] + '/grades')

  }

  catch (err){

    console.log('No Canvas page for ' + course.name)

    return

  }

  //Get information

  start = "ENV = "

  source = source.substring(source.indexOf(start))

  end = "};"

  const temp = JSON.parse(source.substring(source.indexOf(start) + start.length, source.indexOf(end) + 1))

  course.assignments = {}

  course.assignment_groups = temp.assignment_groups

  //Adds grading scheme as a object

  course.grading_scheme = {}

  temp.grading_scheme.map(f => course.grading_scheme[f[0]] = f[1])

  //Adds all assignments

  course.assignment_groups.map(i => i.assignments.map(j => course.assignments[j.id] = j))

  //Add scored assignments information to assignments

  temp.submissions.map(f => {obj = course.assignments[f.assignment_id]; if(obj != undefined){obj = Object.assign(obj, f)}})

  getAssignmentInfo(course, source)

}

//Get assignments information for a course from the source code

function getAssignmentInfo(course, source) {

  source = source.substring(source.indexOf('</script><script>'))

  //For every course

  for(i in course.assignments)

  {

    //Add weight category and name

    assignment = course.assignments[i]

    temp = source.substring(source.indexOf(assignment.assignment_url))

    assignment.name = getSection(temp, '">', '</a>')

    assignment.weight_category = getSection(temp, '<div class="context">', '</div>')

  }

}

//Get section of a string based on start string and end string

function getSection(source, start, end) {

  return source.substring(source.indexOf(start) + start.length, source.indexOf(end))

}

//Adds user inputed course information

function getUserInputCourses() {

  //Gets information from Google Sheet

  userInputedCourses = courseSettingsWS.getRange(1, 1, courseSettingsWS.getLastRow(), courseSettingsWS.getLastColumn()).getValues()

  //For every user inputed course

  for (var i = 1; i < userInputedCourses.length; i++)

  {

    temp = {}

    //Makes the inouted course information as a object

    for (j in userInputedCourses[i])

    {

      temp[userInputedCourses[0][j]] = userInputedCourses[i][j]

    }

    //Atempts to convet the weight to a JSON object for custome weights

    try {

      temp['Weight'] = JSON.parse(temp['Weight'])

    }

    catch (err) {

    }

    //If weight is blank than make it null

    if(temp['Weight'] == '')

    {

      temp['Weight'] = null

    }

    //Adds inputed information to the course object

    courses[temp['Course Name']] = Object.assign(courses[temp['Course Name']], temp)

  }

}

//Get source code of a inputed URL using the saved cookies

function getSourceCode(url) {

  return UrlFetchApp.fetch(url, {followRedirects : true, headers : {cookie : settings['Cookie']}}).getContentText()

}

//Adds weight to every assignment

function addWeight(course) {

  //If it has a custom weight or no assignments then return

  if (!(course['Weight'] == null && course.assignments != undefined))

  {

    return

  }

  //For every weight assignment group

  for (i in course.assignment_groups)

  {

    //For every assignment in that group

    for (j in course.assignment_groups[i].assignments)

    {

      assignment = course.assignment_groups[i].assignments[j]

      //Add the weight to the assignment in the course object

      course.assignments[assignment.id].weight = course.assignment_groups[i].group_weight

    }

  }

}

//Adds assignments to the totalAdd

function addAssignments(course, totalAdd) {

  //If the course has no assignments then return

  if (course.assignments == undefined)

  {

    return