Automated report of assignments in courses

[stelpstra]

Attached Excel workbook is able to download data from Canvas (exclusively using Get API calls) to create an overview of all assignments in all courses you specify using Course SIS IDs.

The worksheet contains the following columns to be filled with data from the API as specified in Assignments - Canvas LMS REST API Documentation :

sis_course_code	assignment_group_id	only_visible_to_overrides
course_code	due_date_required	locked_for_user
workflow_state	max_name_length	lock_explanation
course_name	turnitin_enabled	quiz_id
ass. id	vericite_enabled	anonymous_submissions
assignment_name	grade_group_students_individually	freeze_on_copy
published	peer_reviews	frozen
points_possible	automatic_peer_reviews	frozen_attributes
description	peer_review_count	use_rubric_for_grading
due_at	peer_reviews_assign_at	rubric_settings
lock_at	intra_group_peer_reviews	rubric
unlock_at	needs_grading_count	omit_from_final_grade
submission_types	position	moderated_grading
grading_type	post_to_sis	grader_count
group_category_id	integration_id	final_grader_id
has_overrides	integration_data	grader_comments_visible_to_graders
course_id	muted	graders_anonymous_to_graders
created_at	has_submitted_submissions	grader_names_visible_to_final_grader
updated_at	grading_standard_id	anonymous_grading
html_url	unpublishable	allowed_attempts
submissions_download_url

Analytics

On the Analytics tab a diverse set of calculations are present:

Compatibility

• MS Office 2010 on MS Windows (tested on 2010 version 14.0.7184.5000, higher versions work most likely, but are not tested)
• MS Office 365 on MS Windows (works fine, but when extreme hardening policies are used the download speed drops extremely. Erasmus University users should save the workbook in Documents --> no-app-control folder)
• MS Office 2011 on Mac OS X (not tested but should work. Excel 2016 for Mac barely supports VBA and is therefore not supported)

Enable editing and content

In case Excel displays the following warnings, enable editing and content for the Workbook to work properly:

Settings

First fill in the settings:

• the Canvas url of your institution (e.g. https://yourinstitution.instructure.com)
• an API access token (check How do I obtain an API access token for an account?)
  
  
• click Save

Download assignment details of all courses you specify

You can find Course SIS IDs:

• by navigating to a course, view the course Settings page and see the SIS ID as described in How do I use course settings? 
• by creating a provisioning report (admin rights needed) of courses in a (sub)account for a single term or all terms as described in How do I view reports for an account? Open the downloaded csv and copy the Course SIS IDs from the column sis_course_code.

In column A under sis_course_code you can enter a list of Course SIS IDs of which you want to receive details of the assignments:

The list of Course SIS IDs must be a consecutive list and also be part of the table which starts in cell A5.

When you have entered your list of Course SIS IDs to the first column, click Download data from Canvas to start the download progress. Any remaining data in the table from a previous run will be deleted from the sheet and any duplicates in the list of course SIS ID's will be deleted. The button Clear worksheet does the same regarding cleaning up the worksheet. It takes an average of one second per course to download the data. Clicking the Download data from Canvas button again before all the data has been downloaded will ask you if you want to abort the download.

Versions history:

• 1.0 (18 April 2019)
  • Initial version
• 1.1 (3 May 2019)
  • Bug fixed: limit of 10 assignments per course removed (correct api pagination applied)
• 1.2 (13 May 2019)
  • Bug fixed: mixed date formats (mm dd yyyy versus dd mm yyyy) based on localisation where month and day could be in reversed order
  • Improved handling of dates and times, e.g. added functionality where in case multiple due dates and times assignment are stored in once cell as an array of values (to preserve the possibility for filtering and calculations). These are displayed in magenta.
  • In case multiple submission types are set for an assignment, both will be displayed in one cell and displayed in magenta
  • Added the count of rubric settings and rubric criteria
• 1.3 (13 February 2020)
  • Added an Analytics tab containing many useful calculations on downloaded data
  • Added possibility to use Canvas Course IDs in the first column as a source (as an alternative to using the Course SIS ID)
  • Fixed issues with peer review assign dates
  • Fixed several bugs
  • Improvements in the code
• 1.4 (20 February 2020)
  • Fixed a bug regarding large ID numbers
  • Simplified downloading percentage calculation
• 1.5 (17 December 2020)
  • Updated the processing of API pagination (Instructure changed the method regarding the enrolments API) to accommodate for both the old and the new method
• 1.7 (July 16, 2024)
  • Added post_manually and anonymize_students
  • Improved external_tool_tag_attributes, integration_data, allowed_extensions, frozen_attributes, rubric_settings, rubric and overrides
  • Solved column autofill problem
  • Updated code related to pagination to accommodate for the change as indicated at https://community.canvaslms.com/t5/The-Product-Blog/Forthcoming-Update-to-Canvas-API-Response-Header...

Performance

In case you experience extremely slow performance (a minute per row or so) to the point that Excel is freezing, then make sure the Windows Defender Antimalware Client version is at least 4.18.2102.4. You can check the version on your device following this guide How to Find the Microsoft Defender Version Installed in Windows 10.

Remarks

• Disclaimer: use this workbook at your own risk (I cannot be held responsible for any undesired consequences). This Excel workbook only reads from Canvas AND never writes to Canvas. It should not have any negative effect on Canvas in terms of integrity or performance.
• First try it on your test environment https://yourinstitution.test.instructure.com (slow performance is expected and may cause time-out errors) before using it on production
• Always check the results in Canvas or in case your student(s) get accounted for their activities in groups as Canvas is the most reliable source
• Suggestion: save the workbook as a template (xltm file) after entering the settings so you can always start with a fresh and clean workbook so you will be prompted to save it. Keep in mind that if you don't save the file in a macro-enabled workbook format (xlsm), running the report again will not work any more as it requires the macro functionality to run.
• I created this workbook largely in my own spare time (for fun, to learn, to help, to share and because I can), so huge amounts of support can't be expected
• Leave your comments in case you are using it, if it makes your work easier or more fun

Download

Download the latest version of this file from this Google Drive folder.

Do not open in Google Docs nor in MS Office online, because that will not work, just first save the file and then open it in the Microsoft Excel application.

Additional tools

• Automated progress report of students in modules 
• Automated report of group sets, groups and members in a course 
• Create course groups from sections using Excel 
• Download data to Excel using VBA and the API (workbook with code attached) 

[Boekenoogen]

This is a very cool tool and as we were testing it, we noticed that it only displays 10 rows of data. When we tried to edit the Macros to debug it, it says that it is password protected. Is it possible to get a copy without the Macros locked?

Here are the results we are getting. It should display 8 units worth of assignments and as you can see it stops at 10 rows. We tried different courses and it had the same result stopping at 10 rows.

Thanks!

[stelpstra]

Hi  @jrboek ‌, from your screenshot I see that you are only checking one course, is that correct? Does that one course contain a different amount of assignments then 10? What do you mean by "8 units?"

The macro code is protected by password for the main purpose to protect your (of anyone's) private token. If you discover a bug I am happy to find and fix it. In that case I need more specifics on your scenario of testing.

[Boekenoogen]

Thanks for replying so fast. Yes, the course listed in the screenshot has more than 10 assignments. There are 8 units each unit has 4 assignments, so we should see 24 rows but only see 10 of the 24.

I hope this helps.

Thanks

[Boekenoogen]

Thanks for the update... so far it is working great!

[stelpstra]

Hi  @jrboek ‌, I found the cause of the problem. The API limited the list of assignments per course by 10. I have now applied correct Pagination to fix the issue. Version 1.1 is now attached to the updated document. Please let me know if this solves the issue.

[baxl]

Thank you for sharing! This is great!

[stelpstra]

I have added a new version (1.2) with the following changes:

• Fixed date format interpretation issues by Excel (dd mm yyyy versus mm dd yyyy), previous version could mix these formats in cases like 04-02-2019 (may be interpreted as 4 February or as 2 April) where Excel would interpret 14-03-2019 always as 14 March 2019
• Added missing dates and times in cases of more than one assign moments (due, lock and until dates and times), which are stored per cell as arrays
• Added rubric settings item count and rubric criteria item count

The first two points about the dates are illustrered here, items in magenta are multiple values in one cell (array):

The highlighted cell contains the value:
={"22 februari 2019 22:59";"22 februari 2019 22:59";"19 februari 2019 22:59"}*1

Based on your country settings the date values are separated by a "list separator" (in my country separated with semicolons), illustrated here:

I have not been able to test if this is working flawlessly in e.g. US or UK country settings, but I would like to know of you would see errors in your country.

You can ignore the "Unprotected Formula" warning. In case you wonder why there is cell has *1 in the end, it is to force Excel to recognize the dates not as text but as dates.

When there are more than one submission type set for an assignment, these are now also populated in in one cell as an array and with a comment added to the cell:

Also in this case the "list separator" should be the one of your country settings.

The rubric settings item count (always 6) and rubric criteria item count (variable amount) are added as an indication:

The details of the rubric can be found in Canvas in the assignment.

I hope it all works in different countries!

[stelpstra]

At the moment it seems that the API is not supplying the turnitin_enabled field or data, therefore the value No (zero) is populated in the report, even if the assignment is set up with TurnitIn enabled. I have reported this to Canvas Support and I am awaiting their response.

[stelpstra]

I just added an improved version (1.3):

• Added an Analytics tab containing many useful calculations on downloaded data
• Added possibility to use Canvas Course IDs in the first column as a source (as an alternative to using the Course SIS ID)
• Fixed issues with peer review assign dates
• Fixed several bugs
• Improvements in the code

The analytics tab contains this type of information based on the downloaded assignment details:

[Boekenoogen]

I keep getting an error. The settings work, I input a SISID and it comes back and gives me a 404 error. When I try a course id, I get the same 404 error. I tied this process on two other Canvas sites. The first site gave me the same error, but the second one worked. What was the difference? First, the course id was very small and I believe I had higher permission levels. 

Do you think there is a number or character limit or character type limit on your SISID? If not, it must be my permission level. I just wanted to reach out. I love this tool, I just want it to work for me.

Dr. John Boekenoogen

[stelpstra]

Hi  @jrboek , thanks‌ for testing version 1.4 and confirming the bug with large ID numbers is solved!

[james_umphres1]

Hi Jaap,

This is a very useful tool but is there a way to eliminate some of the data or filter it first? I'm trying to get a list of assignment for about 4,000 classes and it times out.

Thanks!

[a_craik]

Hi  @stelpstra ‌

Thanks for putting the work in on this! Would you be willing to share an open source version (without password protection on the VBA)?

[stelpstra]

Hi  @a_craik , the code is protected for the only reason that your token is stored in a hidden sheet which can be compromised by anyone with the password. Therefore I will not share the password or release a version without a password. If you wish to test this workbook for data connections and security reasons, please make use of the Process Monitor - Windows Sysinternals | Microsoft Docs or similar tools to check all connections this workbook makes.

[a_craik]

No worries Jaap - it was only to modify the code as it returns much more than we need and speed/volume has been an issue.

Thanks anyway!

[stelpstra]

Hi  @a_craik ‌, you can hide the columns which you don't need or minimize the width of these columns to just a few pixels (don't delete the columns to prevent it from crashing). Stripping columns from the code would not improve the speed, because the bottleneck is the low speed of the API (1/2 second per API call minimum). The calls performed retun all assignment data of an assignment object, the API does not offer the ability to only sent back a selection of the assignment object. I have tried various ways to improve the processing speed, but it seems not possible. Please note that if your institution has enabled strict security hardening in combination with Office 365, that this is a huge negative influence on performance. If that is your case, try it on a privately owned / managed computer / laptop to test the difference.

[enoch_hunsaker]

I'm LOVING this report!  Thank you for sharing. I do wonder if you have any insight into how I could make a couple small tweaks to it that would make it even more awesome...

1. How would I configure it to use the course ID rather than an SIS ID for the trigger to pull in data?  Most of our blueprint courses are manually created and have no SIS ID.
2. How would I edit the API it's calling so that I can pull in the url from the external_tool_tag_attributes field.  I'd like to be able to identify exactly which Tools are being used without having to review each external tool individually.