Importing Rubrics from a Spreadsheet

[James]

I've written an Rubric Importer user script that will allow you to copy a rubric from a spreadsheet like Excel or Google Sheets and paste it into a textbox inside Canvas and then automatically turn it into a rubric within Canvas. Many feature requests have asked for improving the rubric experience and https://community.canvaslms.com/ideas/6311-allow-importing-of-rubrics-from-csv-format  is currently in the Gathering Information stage.

Canvas rubrics only allow for simple designs. You can have a criterion, a longer description, and then a series of single line ratings and a point value. Point values must be in descending order and they can't be negative. The input box where you type the rating doesn't wrap and so if your rating description is longer than what will show, you have to scroll back and forth to see the whole thing. Moving rows around in a rubric is not possible unless you use a script like the one I wrote about in Sorting Rubrics Made Easy‌.

The Rubric Importer script allows you to design the rubric in a spreadsheet where you still have the same basic limitations as to layout, but now you can resize columns or rows and wrap text and move things around in the spreadsheet. Then when you're done, you just copy and paste the rubric into a form in Canvas and the script creates the form needed to turn it into a rubric.

The rubric creation form is an undocumented call. It is not part of the Canvas API and you need to be logged into the web interface of Canvas in order to use it. It is basically a wrapper around the Canvas form that takes a tab-delimited input and sends the data to Canvas to create the rubric.

Creating the Rubric in the Spreadsheet

Block Rubrics

This is a new feature for version 2 of the script.

It allows you to specify the points on one row and then use them for rows that come after that. Here is the rubric that originally took me 5 minutes to clean up to the original format. Notice that the ratings are in the wrong order since Canvas wants them in descending order (largest on the left to smallest on the right). The script automatically reverses them if necessary to put them into the correct order.  This block can be pasted into the script as-is without any modification.

Are your rubrics a little more complicated and don't have everything exactly the same point values? The Block means that you can block your rubric into sections and have different point values and different number of ratings for each blocks. Any empty cells to the right of the block are skipped, and a block could have just a single row of ratings in it. The blocks are independent of each other, so some can be in ascending order and some can be in descending order. 

Here's another rubric that you could copy and paste directly into the script.

You can even have sparse rubrics by leaving out ratings if they don't apply to that particular criterion.

None of these have a long description, but you could add it after the Criteria column. If there is something in that column then it will use it, if there isn't then it will be ignored.

You can also link to outcomes, although you'll have to look up the outcome IDs yourself. The simplest place to put them is at the right of the spreadsheet outside of any rating columns, but they can appear in other places. 

Here is the version of the block rubric that I used for testing purposes. Notice the heading row does not contain the point values and that there is nothing to the right of the point values.

The highlighted cells reference outcomes. I don't recommend that you make your rubric this messy, I just wanted to test it with a bunch of different varieties.

Here are the guidelines.

• The headings are completely optional and will be skipped if included in the first row of what you paste into the script.
• The criterion name must be in the first column.
• The long description is optional, but if you have it then it must come in the second column. You can omit long descriptions for any criteria that don't have it.
• The rating point values need to come next and they must be the last thing on a row. They are not recognized as rating points if there is extra information after them on the line. There does not have to be anything before the rating points, but it is okay if there is (it's ignored).
• The first rating point value must be on either the first or the second line and must come before you use it. If it hasn't found a rating points line by the second line, it decides the table is not in block form and moves on to the flexible form.
• Outcomes should come last, making sure they don't exist in a column that is assigned a point value. Be careful if you're using tables with Excel and you add outcome information as Excel will add column labels for you and if your points are on the first line, those extra column names will cause the rating points to go undetected and you won't be able to import your rubric.
• If the outcome ID is in the criteria name column or at the end with a blank criteria name, then the name of the criterion will be taken from the linked outcome. This is the way that Canvas does it, but it doesn't give you the chance to change it. If you specify a criterion name or long description and link to an outcome, then the information that you supply will be used instead of the information from the linked outcome.
• If you use outcomes, you can specify whether or not to use that for scoring in the next cell to the right. A 1 means use that linked outcome for scoring and a 0 means do not use it for scoring. The default is 1, which matches Canvas' default.

Before using the script, you will need to highlight (select) the text inside the spreadsheet. When you do this, you can skip the heading row (or include it). After selecting the text, copy it. The keyboard shortcut is Ctrl-C (PC) or Cmd-C (Mac).

Flexible Rubrics

A rubric is checked to see if it is in block form first and if a suitable points line cannot be found in the first two lines, it falls back to this method.

The original release of this script required a fairly stringent form in the spreadsheet and did not support outcome associated criteria. Here is the example nonsensical rubric that it was originally tested with. This format still works.

Here is the spreadsheet that version 2 was tested with.

The highlighted cells reference outcomes. I don't recommend that you make your rubric this messy, I just wanted to test it with a bunch of different varieties. The one thing I want to point out is the "Did you still feel tired ..." in line 16. It is in the Long Description column of the spreadsheet, but it came out in the Criteria column of the Canvas rubric. The column headings are completely ignored by the script, they are only there for you to help put some order to your rubric. I recommend not even copying them into the script.

Here are the guidelines.

• The long description is optional and may be omitted on a row-by-row basis. You don't have to include it at all and you do include it, you don't have to include it for every row. I could have even moved the ratings in the Grade row to the left one cell and it would still work, although I don't recommend that while designing the rubric.
• The headings are completely optional and will be skipped if included in the first row of what you paste into the script.
• Every rating has two cells and must come in pairs. The first cell (left) is the rating description and the second cell (right) is the point value. The point value should only contain the number, it should not have extra text like "pts" or "points" in it.
• Rows can have different number of ratings in them. Any blank cells at the end of a row are removed and ignored.
• The point values can be in ascending order or descending order and the script will put them into descending order, which is what Canvas requires.
• You'll have to look up Outcome IDs on your own and put them into the system. They can come first in the row, replace the criterion name or the long description, come at the end of the row, or in some cases, be split between the front and end of the row.  For simplicity sake, I recommend the beginning or the end.
• If you specify a valid outcome ID, then it is looked up and any ratings for that row are replaced by those from the outcome. If you have a criterion name or long description, it will use those instead of the values looked up from the outcome (Canvas replaces them with the information from the linked outcome and doesn't give you the option to change it). If you leave off the name or long description, then it will use the information from the linked name.
• You may specify a 0 after an outcome to say make this worth zero points. This is the Canvas equivalent of ignoring the outcome for scoring in your rubric. The default is to include linked outcomes in the scoring.

Although no limit on the number of ratings is enforced by the code, people have said there are problems with you have too many ratings in the rubric. This does not address that and if it doesn't look good or work right when you create it inside Canvas, then it won't here either.

Before using the script, you will need to highlight (select) the text inside the spreadsheet. When you do this, you can skip the heading row (or include it). After selecting the text, copy it. The keyboard shortcut is Ctrl-C (PC) or Cmd-C (Mac).

Importing Outcomes

When the script was originally written, it didn't support linking outcomes. I argued that there was no easy way to tie the information in the spreadsheet to the outcomes in Canvas. Since then, I thought of a way to manually link them, but it requires that you look up the outcome ID inside Canvas and then transfer that number over to the spreadsheet manually.

Of course, you don't have to use outcomes at all.

Finding the Outcome ID

Begin by going to the Outcomes page of your course or account. Then follow these two steps.

1. Navigate through to find the outcome that you want to import.
2. Mouse over the name of the outcome and look at the address of the hyperlink in your browser.

If your browser doesn't show the address when you mouse over it, you can go ahead and click on the Outcome name and then get the outcome ID from the location.

Please be careful typing in the Outcome ID. The system does not show you what the rubric will look like before it sends it, so double check. The outcome will be imported into the course if it does not already exist, so putting in a wrong ID may bring in the wrong outcome.

Use Criterion for Scoring

When you import a criterion from an outcome using the Canvas interface, it asks you if you want to use this criterion for scoring. The default is checked, which means that the criterion will count in the rubric points. Sometimes you want to measure an outcome without having it directly affect the score, so you uncheck this box.

For the script, the default when you include an outcome is the same as it is Canvas. That is, by default, criterion linked from outcomes will be used for scoring. If you do not want this to happen, then you should enter in the cell to the right of the outcome ID. The script will pick that up and pass it on to Canvas.

Although the default is to use the criterion for scoring, if you plan on using some for scoring and some for not scoring, I suggest that you be explicit with a 1 for scoring and a 0 for not-scoring. It will help keep the sheet more aligned and easier to understand.

Overriding Outcome Information

When you import a criterion through the web interface, it copies the name of the criterion, the long description, the rating descriptions, and the rating points over from the associated outcome. On top of that, you can't edit any of those.

However, if you go through and rename the outcome or modify the long description, it does not change the name or description in the rubric. That gave me the idea to allow the user to specify a different name or long description than what the outcome had. Since we're using an undocumented non-API call to create the rubric, Canvas could change that behavior at any point and without notice. But for the time being, you're not stuck with the name on the outcome.

For example, the name of the outcome above was "2.2a: Solve problems by performing deductive and inductive reasoning." and the long description was the same. I didn't come up with those, that's part of our cross-disciplinary outcomes. But let's say that I wanted to include it on a rubric and everything else was designed by me and has a short name for the criterion so it doesn't push the squish the rest of the rubric to the right. Using this script, I could rename it to be "Reasoning" and either leave the original long description that came from the outcome or change that as well.

The basic rule is that if you put something in the name or long description fields of a linked outcome, then it will use what you put in there. If you don't specify that information, then it will use what comes over from the outcome.

The ratings are transferred from the outcome and there is no way to stop that. Since mastery can be tied to the point values, I didn't want to mess with that part of it. You can leave off the ratings completely when you are specifying an associated outcome. A line with just a single number on it will be interpreted as a linked outcome that should be used for scoring.

Detecting Outcome IDs

The script looks for Outcome IDs by trying to match the text in a cell to positive integer. This means that if you're in a place where there might be an outcome ID, that you can't have a criterion with an integer ID. I doubt that many people do that anyway, but just in case you're in the habit of naming your criteria 1, 2, 3, etc., you're going to run into trouble trying to use the script as it's going to complain that it can't find outcome IDs 1, 2, or 3 when it runs. Spaces are trimmed from the beginning and end of all cells, but you could add a decimal point and call them 1., 2., 3., etc., or even #1, #2, #3, etc., and then it would not detect it as an attempt to link to an outcome. If you absolutely must have 1, 2, 3, etc., then you could create them with dots and then go through manually and edit the criterion names to remove the dots.

The simpler you keep the script, the better job it will do at figuring out what you want it to do.

Using the Script

The script only runs from the rubrics page. For a course, this means you go to Outcomes, click the more icon on the right, and then choose Manage Rubrics. For an account, there is a Rubrics button in the navigation menu that will take you there. The location (URL) ends in /rubrics.

It adds a new Import Rubric button underneath the Add Rubric button.

Click the Import Rubric button to open the modal dialog box. I've resized it here, but it takes about 80% of the screen width.

1. Type the name of the rubric into the Rubric Title box.
2. Click in the Rubric Contents box and paste the rubric that you copied from the spreadsheet. The keyboard shortcut is Ctrl-V (PC) or Cmd-V (Mac). Note that it will not look right when you paste it, but it's okay.
3. Click the Create button.

Here's what my example looked like before I hit Create.

If everything is successful, the page will reload and your new Rubric will appear in the list of rubrics.

The Canvas script shows the rubric on the page after you click their Create Rubric button. My script bypasses their form completely and just sends the information directly to the Canvas servers. The page must reload to list the rubric and the command to reload the page is automatically sent for you when the script successfully runs.

You can then click on the rubric title to see the rubric, just like you can with any other rubric you have.

What if it doesn't work?

There are some error messages that may occur. They will definitely occur if you don't follow the guidelines. They will look like this after clicking the Create button.

Here are some of the error messages you may see.

• Only one rating found in line #. This happens if you're using the block rubric and only specify one rating.
• Invalid content at the end of line #. This happens if you're creating a block rubric and have specified an outcome in the early part of a rubric and then you have information to the right of the ratings points. The program expects that this will be a 0 or 1 to indicate the scoring, but you have something else in there instead.
• Rating without associated points at end of line #. You have an entry to the right the points block, but it's not an outcome.
• Empty rating description in line #, column #. This happens in the flex form of a rubric and it's expecting a description for the rating, but the description is blank.
• Second item in pair is not a number in line #, column #. This is a companion error to the empty rating description error. It is trying to find a point value, but it cannot find any.
• Unbalanced rating/points at the end of line #. This happens in the flex form of the rubric when you have extra information on the right that cannot be tied to an outcome.
• Conflicting directives for using outcome for scoring in line #. This happens in the flex form of a rubric when you tell it how to handle scoring of a linked outcome at the beginning and again at the end of a criterion line.
• Unable to locate outcomes: #. You must manually look up the Outcome IDs for any outcome that you want to use. Sometimes we get it wrong as we transfer it over. This message will happen when Canvas is unable to locate the outcome. This could also happen if you're having a bad internet day as I've put a 3 second timeout on the lookup. I didn't want people to think the script wasn't doing anything and click the Create button a second time. This could also happen if you name your criteria using non-negative integers. See the section on Detecting Outcome IDs for more information on this.
• You must provide a title for your rubric. This happens when you didn't enter a title in the form's Rubric Title input.
• You must paste your rubric into the textbox. This happens if you left the Rubric Contents box empty.
• All the information was supplied correctly, but there was an error saving rubric to Canvas. This error happens when there is a failure trying to submit the form to the Canvas servers for processing. You can try again later or you can look at the JavaScript console within the browser to try and see if you can find out why.

There are also two errors that you should not get if you're running my code as intended. If someone modifies the code or installs it globally for an institution, then it might occur.

• No association specified, refusing to create rubric object. The script tries to determine whether you're creating a course rubric or an account rubric and the ID that goes along with those. Normally, it has no problem because the only pages where the script runs are also the pages that supply that information. However, on the off chance that something dreadfully wrong should happen, it is impossible to create the rubric and you'll get this message.
• Unable to determine where to place this rubric. This is the same error as No association specified, refusing to create rubric object. The only difference is where it's called internally. This one occurs when it tries to determine the association and the other is a check to make sure someone didn't call the script to actually process the input without also supplying the association. If you're using my script, the No association error should never be returned as this error will keep it from getting that far.
• Unable to create criterion for line #. I think I have most of the error checking done before it ever attempts to create the criterion for a row, but this is just in case I missed something.

If something does go wrong, then fix it in the spreadsheet and copy/paste it again. The textbox in the form is not suitable for editing.

Version 2 of the script represents a major update that more than doubled the number of lines of code and made it considerably more complex than the original version. As you can see from the examples, I tested it with many variations, but there are probably cases I didn't try. Try to keep things as simple as possible. While specifying the outcome in the middle of the criterion line should work, it's more complicated than if you put it at the front or end. If you find something that isn't working and you can't figure it out, please let me know.

Video Demonstrations

Importing from Excel

The rubric in this video was already in Excel, but it only had the total point values at the end of the rubric. Everything in the first column was full points, values in the second column were 50%, and ratings in the last column were worth 0. This video shows how to insert the needed columns, put in a formula to calculate the 50% of the values, and then copy/paste it into the script and create the rubric.

The rubric has 19 criteria with 3 ratings for each. The total time to place the rubric in the proper format in Excel and then create the script was 1:07.

Importing from Word

Importing from Word is not supported directly, but you can copy/paste from Word into Excel and then make the changes in Excel.

The rubric had 6 criteria with 4 ratings each and it took me 5:00 minutes to manually create the rubric from Word. Thankfully triple clicking will select the text in a table cell or it would have taken longer. It took me a while to get into the groove and originally forgot to change point values or put things in the wrong order. You know, all those things that happen when you manually create a rubric using the Canvas interface.

I then created the rubric using my script. The rating values were in ascending order so that had to be reversed. The rubric entries had a row that had hard returns in them in Word that got broken into two rows when transferring to Excel. The rubric script will only accept and ignore a single header row at the top. I fixed the double rows, but didn't have to since copying the header is optional anyway. After bringing the rubric into Excel, I moved the columns around and added the point values to put it into standard format for the script. I then copy/pasted the table without the headers into the script and created the rubric. The total time from starting the copy from Word to clicking the Create button was 1:26.

Note that the information in this video is now superseded by the ability to create block rubrics. This feature was not available in the initial release. It will be even faster now than this video shows.

Known Limitations

There are some known limitations with this script. It was designed to be a quick way to get a simple rubric into Canvas. Major changes happened with version 2 a week after the initial release that allow for some complicated behavior.

Creation Only (no append)

This script does not append rows to an existing rubric, it creates new rubrics. You would need to get the existing rubric into Excel and then add the news rows. You would then have to create a new rubric inside Canvas.

No Editing / No Sorting

This script creates rubrics. If you want to edit them, you'll need to use the built-in capability of Canvas. If you want to rearrange the rows in an existing rubric to move criteria around, then see the Sorting Rubrics Made Easy‌ script.

Downloading Rubrics from Canvas

You can highlight a rubric in Canvas and copy/paste it into Excel. However, the format it comes across is not compatible with the format of this script. User scripts run on certain pages and the page that the download would happen on is a different page than the page that handles the creation. That would need to be handled by another script.

Creating Rubrics from Assignments

This script does not allow you to import rubrics from an assignment page where you can add a rubric to an assignment. This script bypasses the form that gets created when you create an assignment and it doesn't handle the attaching to an assignment that would have to happen if it was done there. Instead, you'll need to go to the rubrics page and add it there and then attach it to the assignment after it's been imported.

Installation

Quick Install

For those power users who are impatient, here are the quick install steps.

1. Install Tampermonkey for Chrome, Firefox, or Safari
2. Install the Rubric Importer user script.

If you run into problems, be sure to go back and read the instructions.

Custom URLs

The script automatically runs on any page that matches *.instructure.com/courses/*/rubrics or *.instructure.com/accounts/*/rubrics. This are the main rubric pages for courses and accounts when your site is hosted by Instructure without a custom URL. If you have a custom URL, like canvas.university.edu, then you will need to modify the script to get it to work.

To make this change in Tampermonkey, click on the Tampermonkey Icon, choose Dashboard, and then click on Rubric Importer. Then change the *.instructure.com in the // @include statements on line 5 and 6 to match your instance and save your script.