cancel
Showing results for 
Search instead for 
Did you mean: 

Quiz Item and Outcome Uploads from a Google Sheet

bbennett2
Navigator II
4 3 789

I've been working on an easier way for teachers to upload Quiz Items and Outcomes from a Google Sheet. The template sheet uses Google Apps Script and a user's personal API key to interact with their courses.

Quizzes

Teachers frequently write tests and quizzes together. This mechanism allows teachers to write and upload dozens of questions easily from a Google Sheet.

Screenshot of the template spreadsheet

The Canvas API only allows questions to be uploaded to an existing Quiz resource. Teachers can select the appropriate course and quiz dynamically using the popup.

Quiz uploader sidebar with dynamic course and quiz selection boxesOnce a quiz resource has been selected, users can define the number of items to upload. The spreadsheet marks an item as successful and updates the next row to be uploaded so questions are not duplicated on subsequent runs.

Considerations

  • All questions uploaded are stored in the "Unnamed bank" question bank.
  • The "Topic" and "Primary Standard Indicator" fields are used to title questions for easy bulk sorting in the Canvas UI.

Updates to come

  • Error messages are...opaque. Often a failure results in "the resource does not exist," meaning the user hasn't selected a course or quiz.
  • Questions are not batched right now, so uploading too many questions can lead to a script timeout.

Outcomes

The Outcomes API is difficult to use. I know there are CSV imports now, but it is sometimes helpful to upload Outcomes as they're written rather than generating a CSV over and over.

Outcomes have to go into an Outcome Group. If there are no groups defined in the course, the course name is used as a top-level group.

The template spreadsheet is similar to the quiz template, with specific fields being defined for the upload to run successfully. There is a helper sheet where users can define different rubric scoring ranges for each upload.

Outcome upload template in a Google Sheet

Rubric template for uploading Outcomes

The Outcome upload sidebar is similar to the Quiz Item sidebar. Because new outcomes must be attached to a group, a dynamic list of codes and group titles are generated when the user selects their course.

Outcome group codes displayed dynamically on course selection

Considerations

  • Outcome uploads are done to the course, not to the user's account. This allows them to bulk-modify outcomes semester to semester as they update their courses.

Updates to come

  • Improve error messaging
  • Pull all existing outcomes and their status into a sheet for proofing and updating.

I have a template project you can copy. You can also see the entire source on GitHub if you'd like to take a look or contribute. My plans are to eventually convert this to an AddOn for easier distribution and use.

3 Comments
GregoryBeyrer
Adventurer III

 @bbennett2  Awesome! This is a great contribution to the Canvas Community and something that has been missing from Canvas: batch upload of questions using a simple text editor. It was one of the few things Canvas was missing when I first started using it 3 years ago, and with your excellent work it can now be done.

The embedded video is a recording of the steps I took:

  1. Select the link to your template to make a copy in my Google Drive
  2. Select the link "Setup" from the (newly added) Canvas Assessment Manager menu in my copy of your Google Sheet
  3. Select the button "Continue" on the Sign in dialog
  4. Choose my account in the Sign in dialog and select the link "Advanced" following the warning This app isn't verified
  5. Select the link "Go to Canvas Quiz Uploader (unsafe)" following the warning This app isn't verified
  6. Select the button "Allow" in the Sign in dialog
  7. Paste my API key and type or paste my Canvas URLs in the Connect to Canvas dialog and select Save Settings
  8. In Canvas, create a new quiz "Canvas Assessment Manager BETA - Pact-12 Mascots" and save it
  9. In the Google Sheet, select "Canvas Quiz Uploader" from the Canvas Assessment Manager menu (there are already questions and answers on the QuizTemplate tab)
  10. In the Canvas Quiz Uploader panel, select the course and quiz, type the number of questions on the sheet, and select the button "Run"
  11. In Canvas, confirm the questions and correct answers in the quiz
  12. Confirm the questions and correct answers in the Unfiled Questions question bank

I am excited about using this in my classes, sharing with my fellow faculty, and the future development of your project. Thank you!

Please note: This is a silent video.

bbennett2
Navigator II

Thanjs Gregory.

The "unsafe source" warning is from Google and I just need to submit it to

the developers console. I need to go back and add that note to the docs so

people don't worry. It'll also go away when I convert it to an add-on.

On Fri, Jul 27, 2018, 4:40 AM professorbeyrer@gmail.com <

Boekenoogen
Learner II

I really like this tool. I have been looking at a way to use Google Sheets more and this will help.