How to extract Notes from Canvas Gradebook using Tableau?

[mlam]

Hi, I understand that Notes column is not included in the Gradebook CSV export file. (How do I use the Notes column in the Gradebook? | Canvas Instructor Guide | Canvas Guides )

Are there any Tableau users out there who are able to extract/export Notes entered in the Gradebook Notes column?

Reason why I need to extract Notes in Gradebook is because College staff has been using the Note column to flag a student as having Assessment Resit. I need to create a report for College staff for prize giving purposes. This report must include any resit information as student who resit an assessment will be discarded from prize considerations.

Any assistance/suggestion would be appreciated. Thanks.

[James]

 @mlam ​, are you per-chance new to using Tableau? I ask because you've asked two questions that to me aren't about Tableau at all, but are about obtaining data. That said, I don't use Tableau, so maybe it's my perspective (or lack thereof), but I understood that it would analyze data and that there are some connectors, like to RedShift, but you basically want tabular data to do any analysis.

The question to me becomes how do I obtain the custom columns data. The question of whether that is used in Tableau or Excel or put into a database is mostly irrelevant. I don't think any of those (again, I don't use Tableau) is going to make the API calls needed to obtain the information.

So, I'm not sure if you're wanting a turnkey solution or you're just wanting to know how you can get the data so you can analyze it in Tableau. It may be that you're associating Canvas Data with Tableau because that's the software you use.

Anyway, I don't see the custom gradebook columns in Canvas Data, so you'll need to get them through the Custom Gradebook Columns API. That API involves the gradebook on a course level, so you'll need to obtain a list of courses that have custom columns and then iterate over those, obtaining the information.

Yes, the notes is considered a custom gradebook column. When I fetch a list of custom columns for one of our courses, then I get this

When I ask to list the column data for id=228, it gives the "content" (value) and "user_id" (Canvas User ID) for each item.

You get the great pleasure of doing this for every course, but at least it gives you all of the students for the course, so it's two API calls per class (one to get the gradebook column id and one to fetch the data). You will also have to worry about pagination of results if any of your courses have more than 100 students in them as the per_page query parameter is limited to 100.

[mlam · Accepted solution]

Received the following update from Instructure team:

The notes column from the gradebook is not in Canvas Data at this time. We are re-working the gradebook, so this will possibly come once it's finished. Same as above, there is not a set timeline for when that project will be complete.

The submission_comment_dim table "comment" column refers to any comments a student/teacher makes on the submission itself through speedgrader, or if the student clicks on the assignment and makes a comment. So you won't be able to find the gradebook notes column information in that table.

You will need to use two API endpoints to get the data:

1. /api/v1/courses/:course_id:/custom_gradebook_columns to get the id of the custom gradebook column

2. /api/v1/courses/:course_id/custom_gradebook_columns/:id/data to get the actual data from the column