Export Individual Course Gradebook Via API

[bneporadny]

My institution is looking at automating grades from Canvas into our SIS.  Grades in our SIS are letter grades instead of numeric. Rather than trying to translate all the grades from numeric to letter grade we were hoping to find a way to export the courses grade book so that we can setup a custom column in the grade book called final grade.  The only issue I am having is finding how to pull the grade book out of individual courses via an #api call. 

Has anyone found a way to do this?

[pklove]

I think this might be the same as in this thread: https://community.canvaslms.com/message/115792-re-api-to-get-all-grade-column-scores-per-id?commentI... 

[bneporadny]

Hi Peter,

Thanks for this and just as I thought there currently isn't a straight forward way to pull the entire #grade book out of a course and into a CSV file as you can do via the #gui‌. 

[James]

 @bneporadny , what do you consider straight-forward?

Since we talked at InstructureCon, I spent most free moments in October writing code that will pulls all the grades out so we can bring them into Starfish. That seems similar to what you're talking about. Anyway, I found some stuff that I hadn't used or been able to find before, including how to get quiz responses out of the API (even when you don't want them).

If you want individual assignment information, that comes from the submissions API. However, that just gives raw scores and doesn't include things like rules from dropping grades or weights of assignment groups.

If you're just looking for the overall grade, that's available in the enrollments information, which is obtainable in several locations -- I'm using the course flavor of the list enrollments endpoint to get it. You get the current, final, unposted_current, unposted_final results as both a score and a grade. The score is a number and the grade may be a number, but it might be something else -- especially when there is a grading scheme in use for the class. That gives you the grades for an entire course, even if there are multiple sections in it, with one API call per course.

[bneporadny]

James,

Thank you for your response. What I was looking to do is to be able to pull the courses gradebook file out via code, read through the CSV file for a custom column called final grades, where instructors would input the letter grade they wish to give the student for the course and I was going to read that column to then import into our SIS. But from all that I am hearing and seeing getting the CSV to export just as it does via the GUI isn't a possibility currently. 

[James · Accepted solution]

 @bneporadny ,

I'm not following why you think it's necessary to involve a CSV file. Unless I'm really missing something, which is a distinct possibility, you've got a workflow in mind that takes you from point A to B to C to D to E and finally to F, when there is a direct connection between A and E so the workflow could be A to E to F. If I am missing something, I'm still not sure what you're asking for.

A CSV export will include the custom columns and you could parse it, but that information is obtainable directly from the API without messing with CSV files. The request to generate a gradebook download is an internal call, not an API one, and it starts a process that then has to wait to finish by monitoring the progress before you can download it. You could start a headless browser session, go into each course's gradebook, wait for it to load, then click the export button, wait for the export to be generated, then download it. That's a lot to automate and a lot that could go wrong.

On the other hand, there are API calls that allow you to obtain that information directly without having to mess with a CSV file and without having to wait for the CSV to generate before you can download it and without any parsing of CSV files.

I'll give you two ways I can think of to accomplish this task. One using custom gradebook columns and one using a column in the gradebook for the final grade.

Custom Column Solution

Step 1: Create a Custom Column

Use the Create a custom gradebook column endpoint to create a custom column for each course.

I sent this payload to my sandbox course:

{
  "column": {
    "title": "Final Grade",
    "position": 1,
    "hidden": false,
    "teacher_notes": false,
    "read_only": false
  }
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

I got this response (make a note of the column ID = 27480 for later).

{
  "id": 27480,
  "title": "Final Grade",
  "position": 1,
  "teacher_notes": false,
  "read_only": false,
  "hidden": false
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

When I go into the gradebook for that course, I see this:

Step 2: Enter Final Grades

As a teacher, I go into the gradebook and enter grades

Step 3: Retrieve Grades

Now I'm going to use the List entries for a column endpoint.

Using the column ID (27480) that was generated when I created the column, I get this response (I changed the user_ids slightly)

[
  {
    "content": "A",
    "user_id": 2175000
  },
  {
    "content": "D",
    "user_id": 3346000
  }
]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Notes

You may need to use another call to match up the user_ids to a SIS ID if you haven't already saved them.

If you didn't keep track of the IDs for the custom columns when you created them, you can use the List custom gradebook columns endpoint. Then look for the one called "Final Grade".

[
  {
    "id": 27480,
    "title": "Final Grade",
    "position": 1,
    "teacher_notes": false,
    "read_only": false,
    "hidden": false
  },
  {
    "id": 2404,
    "title": "Notes",
    "position": 1,
    "teacher_notes": true,
    "read_only": false,
    "hidden": false
  }
]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

Also note that I have two position 1's, so maybe I should have used position 0 when I created it. Or Maybe Canvas looks at position in ascending sort and then ID in descending sort to determine what order to put them in?

Assignment in Gradebook Solution

The other way I can think of doing this is to have a gradebook assignment that doesn't count towards the final grade. It is a letter type grading with a grading scheme attached to it. This could help enforce what teacher enter into it.  

Step 1 Create a Grading Scheme

Do this at the Account Level so that you don't have to create it for each course. If you have a standardized scale, you can put that in here and then the faculty can just type in the final percentage for the score.

I'm going to go with a 4.0 scale (A=4 ... F=0) just to make it interesting. An A is 4/4=100%, B is 3/4=75%, C is 2/4=50%, D is 1/4=25%, and F is 0/4=0%. I've set my scale to split the grade evenly on either side. Again, how you create this scheme depends on how you want faculty to enter the grades.

Step 2 Create an Assignment for the Final Grade

I called this Final Grade Assignment (just so you could tell it apart from the Final Grade custom column I created earlier) and I did it through the GUI although it can be done through the API as well. I made it worth 4 points to match a 4.0 scale, but it can be anything. I don't want it to count towards the final grade because it is the final grade. I assigned it the grading scale I created earlier.

I went ahead and created a rubric and used it for assigning the grade, just to make it easier for teachers to enter their grades. The problem there is that the rubric cannot be automatically created through the API.

Step 3 Enter the grades

Because I have a rubric, it's easy for the instructors to enter the grades. That extra blue arrow is from my QuizWiz: Enhancements to SpeedGrader and Quizzes‌ script that allows me to save the rubric, advance to the next student, and re-open the rubric, all with the click of a single button.

Here's after closing the rubric:

Step 4 Retrieve the Grades

Now it's time to get the grades from the gradebook. This requires the Submissions API. Since you're after just a single assignment, I would use the List assignment submissions endpoint.

There is a LOT more information that is returned. I've trimmed it down.

[
  {
    "id": 237117974,
    "grade": "A",
    "score": 4,
    "assignment_id": 20738248,
    "user_id": 2175000
  },
  {
    "id": 237117975,
    "grade": "D",
    "score": 1,
    "assignment_id": 20738248,
    "user_id": 3346000
  },
  {
    "id": 237117976,
    "grade": null,
    "score": null,
    "assignment_id": 20738248,
    "user_id": 8432000
  }
]‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

The property that you want is the "grade" not the "score".

Note that this gives you the test student as well, it's the one with the null grade.

The list assignment submissions API has an option to include[]=user, so you can get the user details at the same time you get the grades. When you do that, you get something like this (I'm only showing one entry):

{
  "id": 237117974,
  "grade": "A",
  "score": 4,
  "assignment_id": 20738248,
  "user_id": 2175000,
  "user": {
    "id": 2175000,
    "name": "James Jones",
    "sortable_name": "Jones, James",
    "short_name": "James Jones",
    "sis_user_id": "123456",
    "login_id": "james"
  }
}‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍

[ScottHawley]

5 years later, and a simple Google search for "canvas api download gradebook" turns up this page as one of the only attempts to answer this question of how to use the API instead of the GUI to download grades.  This seems like it would be a basic function provided by the API...?. 

But the accepted answer goes into many spurious details about adding extra columns, and then the payoff is that it only downloads grades for ONE assignment, instead of the entire gradebook.

It would be more helpful for myself and future users finding this page if an answer could be added that sticks to the main topic of just downloading the ENTIRE gradebook.  

[James]

While I don't have a solution for getting a CSV file, I do have two other solutions that don't require it. You'll just have to wait for the post to be approved, it's in moderation right now.

[bneporadny]

James,

The only reason I was looking at the csv option is because it was going to be the quickest and easiest to development for me. I have code that reads through #csv files and plucks columns out and was going to piggy back off that. But I guess that currently isn't an option. 

I appreciate you providing ways that are currently available in Canvas to accomplish this and am going to review them and figure out which one will work the best for us. Just going to be a little longer to develop/test and deploy. 

Thank you again for your time and expertise. 

[themidiman]

This discussion is intriguing. I went into the network log activity in Chrome dev tools to look at the HTTP request response flow and wondered if this could somehow be automated somehow with wget or curl in an authenticated session. The request seems to be a GET request to https://<courseurl>/gradebook_csv which is NOT an API endpoint, but perhaps this could be a workaround to there not being an API endpoint for exporting the gradebook CSV.

[mary_speight]

We're also looking into the possibility of this for state records and retention policies that require us to maintain grade books for 5 years while we're only permitted to keep student submissions for 1 year. Providing this type of bulk exporting would enable us to adhere to the state policies. I'd love to know if anyone comes up with a solution!