Canvas - Getting PrarieLearn grades into Canvas

Eventually, UCSB’s implementation of PrairieLearn will feature direct integration with Canvas.

In the meantime though, if you want grades from PrarieLearn into your Canvas gradebook, you need to use an indirect method involving these eight steps, which we’ll describe first at a high-level, then in detail.

Create the assignment in Canvas
Export the Grades as a .csv file
Import that CSV file into a Google Sheets spreadsheet
Export the Grades from PrarieLearn as a .csv file
Import that CSV file into the same Google Sheets spreadsheet.
Configure the column in the Canvas sheet in the spreadsheet to pull the grades from the other sheet using VLOOKUP
Export that sheet as a CSV
Import that final CSV into Canvas.

1. Create the assignment in Canvas

Create an assignment in Canvas. Leave the dates blank, and make the submission type: No submission

You also need to “Publish” the assignment so that it shows up in the gradebook.

2. Export the Grades as a .csv file

Navigate to Grades and select Export then Export Entire Gradebook

3. Import that CSV file into a Google Sheets spreadsheet

Import this file into a Google Sheets spreadsheet. The top left corner should look like this:

4. Export the Grades from PrarieLearn as a .csv file

Now, in PrairieLearn, navigate to your assessment. Select the Downloads tab:

That should give you a page like this. There are many more files than the ones shown in this screenshot, but the one you want is the one at the bottom of this screenshot, namely the one that ends in _instances.csv. Click that link and download that file:

5. Import that CSV file into the same Google Sheets spreadsheet.

Now, in your same Google Sheet where you imported the file from canvas, choose File : Import:

Choose the file you downloaded from PrairieLearn (the one that ends in _instance.csv.

In the modal that pops up, select Insert New Sheet:

So that it looks like this:

You should now have the two files in the same spreadsheet, side by side like this:

To make things more clear, let’s rename these tabs to from-canvas and from-prairielearn, like this:

6. Configure the column in the Canvas sheet in the spreadsheet to pull the grades from the other sheet using `VLOOKUP`

Now, find the column in the from-canvas spreadsheet for your Exam. In the example, it’s column AN, but it will likely be different in your sheet.

Note that the third row has 100 in it; this is important. This should match the “possible points” (i.e. maximum grade) for your assessment. If it doesn’t, fix that in Canvas and here before proceeding.

Now, in the cell under the 100, the cell on the fourth line down, we’ll put a formula to populate the grade.

For FERPA reasons, I’m not showing the values in my spreadsheet, but verify for yourself that:

Row 4 has data for the first student
Their UCSBNetId (i.e. their email without the @ucsb.edu part appears in column C and column D).

The formula we need is going to use that value to look up their grade and put it in the cell.

Verify that on your from-prairielearn tab, you have these column headings:

You will see that:

columm C (Username) on the from-prairielearn sheet matches column C on the from-canvas spreadsheet
columns J and K on the from-prairielearn sheet have the score as a percentage and as a raw score.

So the formula for the basic score is this:

=VLOOKUP(C4,'from-prairielearn'!C:K,8,false)

Note:

The 8 here signifies that column J is 8 columns over from column C where we started, so this pulls in the value of column J (percentage). If we wanted column K (raw score), we’d use a 9 in the formula instead.
The false is important; it says that if we don’t find an exact match, the value N/A will be reported.

The problem with this formula is that it will give us N/A values, which Canvas won’t like. So here’s a slightly more complex formula that gets the job done.

=IF(ISNA(VLOOKUP(C4,'from-prairielearn'!C:K,8,false)),0,VLOOKUP(C4,'from-prairielearn'!C:K,8,false))

It means this:

If the value we looked up is NA, use 0, otherwise use the value.

Put this formulas into the 4th cell, and then drag it down to the bottom of the spreadsheet to the last student row.

You should now see that the values are populated correctly for the students.

7. Export that sheet as a CSV

Now, export this as a CSV, using the File/Download/Comma Separated Values (.csv) menu option in Google Sheets:

8. Import that final CSV into Canvas.

Now, return to Canvas to the Grades screen. Use the Import button:

This should import your grades into the Canvas grades spreadsheet. Note that it can take 2-3 minutes for all of the grades to populate, so you may need to refresh the page a few times after the upload is complete.