[ARCHIVED] Trying to do an API query for students current grade and current score

[dholik]

Hi Everyone,

We're working to enhance our data collection by adding some additional information to our API queries but I'm having difficulties finding it. Specifically we're looking for a students current grade and score. I did find a reference to them in the Enrollment table however when we query those columns they only have NULL values.

Does anyone have an answer to this?

Thanks,

Dean

[jwadec]

I wrote a powershell script to get this and assignment level data. Maybe it will help.

# Working as of 12/11/2020
# Script to Extract Assignment Level Data from Canvas
# Version 1.0

# Configuration Variables

$inputFile = "C:\canvas\reports\student_list_demo.csv" #Input File containing list of students.
$canvasURL = "++++++.instructure.com" #Canvas URL. Do not include https://
$token = "" #Account Token

$enrollmentType = "StudentEnrollment" #Enrollment Type, This ensures that we only get enrollments where role -eq student
$enrollmentState = "active" #Enrollment State, This ensures that we only get active enrollments for the student
$enrollmentInclude = "current_points" #Additiional Data to Include, By default the enrollment API does not include grade information. We actually want it to include that.
$enrollmentTerm = "5" # Go to api/v1/terms to locate the internal ID of the term. For instance, Fall 2020 is ID '5'. Spring 2021 is ID '7'

$exportFile = "C:\canvas\reports\student_grades.csv" #Location to Export the Data
$header = "LAST NAME,FIRST NAME,UID,SECTION,ACTIVITY (in seconds),LAST_ACTIVITY,CURRENT_SCORE,FINAL_SCORE,TERM,ASSIGNMENT_ID,ASSIGNMENT_TITLE,ASSIGNMENT_STATUS,DUE_DATE,SUBMISSION_DATE,START_DATE,GRADED_DATE,POINTS_POSSIBLE,SCORE"

#************ Do Not Edit Below This Line ************

#Check for file
if(!(Test-Path $inputFile)){
  Write-Host Input file does not exist!
  exit
}

#Check for export file. Remove if it already exists.
if((Test-Path $exportFile)){
  Remove-Item $exportFile
  Add-Content $exportFile -Value $header
}

$headers = @{"Authorization"="Bearer "+$token}
$in_data = Import-CSV($inputFile);

forEach($item in $in_data)
{
  $student_id = $item.student_id
  $url = 'https://'+$canvasURL+'/api/v1/users/sis_user_id:'+$student_id+'/enrollments?per_page=100'
  $postData = @{'type[]'=$enrollmentType; 'state[]'=$enrollmentState; 'include[]'=$enrollmentInclude; 'enrollment_term_id'=$enrollmentTerm}
    Write-Host "   "
    Write-Host Getting Enrollments for $student_id
    $results = (Invoke-WebRequest -Headers $headers -Method GET -Uri $url -Body $postData)
      $jresults = ($results.Content | ConvertFrom-Json)
      if($jresults.id)
      {
        forEach ($i in $jresults) 
        {
              $course = $i.course_id
              $user_id = $i.sis_user_id
              $name = $i.user.sortable_name -replace '\s',''
              $sisID = $i.user.sis_user_id
              $secton = $i.sis_section_id
              $activity = $i.total_activity_time
              $last_activity = $i.last_activity_at
              $current_score = $i.grades.current_score
              $final_score = $i.grades.final_score
              $term = $enrollmentTerm

              Write-Host "   "
              Write-Host "   Course:" $course
        
                    $urlAsn = 'https://'+$canvasURL+'/api/v1/courses/'+$course+'/analytics/users/sis_user_id:'+$user_id+'/assignments?per_page=100'
                    $resultsAsn = (Invoke-WebRequest -Headers $headers -Method GET -Uri $urlASn)
                    $jresultsAsn = ($resultsAsn.Content | ConvertFrom-Json)
                          if($jresultsASn.assignment_id)
                          {   
                            forEach ($i in $jresultsAsn) 
                            {
                                $assignment_id = $i.assignment_id
                                $assignment_title = $i.title
                                $due_date = $i.due_at
                                $submission_date = $i.submission.submitted_at
                                $start_date = $i.unlock_at
                                $graded_date = $i.submission.posted_at
                                $points_possible = $i.max_score
                                $assignment_status = $i.status
                                $score = $i.submission.score

                                    Write-Host "      Assignment:" $assignment_title 

                                    $file_contents = ''+$name+','+$sisID+',"'+$secton+'",'+$activity+','+$last_activity+','+$current_score+','+$final_score+','+$enrollmentTerm+',"'+$assignment_id+'","'+$assignment_title+'","'+$assignment_status+'",'+$due_date+','+$submission_date+','+$start_date+','+$graded_date+','+$points_possible+','+$score+''
                                    Add-Content $exportFile -Value $file_contents
                             }
                          }
            }
        }
}

Write-Host "   "
Write-Host "Script Complete"

I know nested looping is bad practice -- this was just something to help us with a reporting project. If you do not want the assignment level data, you can take out that loop. The csv file just needs to contain usernames with a column heading of student_id. This write everything to a csv file.