Brian Bennett

Quiz Item and Outcome Uploads from a Google Sheet

Blog Post created by Brian Bennett on Jul 26, 2018

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.

Outcomes