Introduction
There are several ways to get data from Canvas in the form of downloading csv files. In most cases you want to apply some analysis of that data, which may require importing it into excel, sorting and filtering data and create formulas and graphs to create some sort of a dashboard. This is all great, but updating it periodically may require quite some manual work each time. Additionally this may be too difficult and time consuming for teachers and ta’s.
Instead of working with csv files, downloading data directly from Canvas into Excel with a click of a button can be a huge advantage. Let’s say students need to complete a course with modules as a prerequisite for another course, your teachers may want to periodically check their progress in these modules. In this case a VBA enabled Excel workbook template like Automated progress report of students in modules comes in handy. And because the Canvas API is so extensive, many types of similar reports can be created using VBA in Excel. You can even build a template and send it to your teachers and ta’s, so the only thing they have to do is to click a button to update the data (without compromising the security of the API key).
Attached macro enabled Excel workbook contains the VBA code to download data using the Canvas API. You can set your Canvas url and API key in a secure way and it enables you to download a list of students in a course. The purpose of this workbook is to offer you the ability to extend the code to download other course and student related data as documented in http://api.instructure.com as you see fit.
It is up to you to add code to populate the areas where the headers and the data need to go.
This data is stored in the hidden Settings sheet, which can only be made visible using VBA code (see https://support.microsoft.com/en-us/kb/142530). This is why protecting access to the code is critical.
Compatibility
- MS Office 2010 on MS Windows (higher versions work most likely, but are not tested)
- MS Office 2011 on Mac OS X (version 2016 for Mac barely supports VBA and is therefore not supported)
For it all to work, make sure you enable editing as well as macros:
Protect your API token by protecting the code
When using this workbook it is imperative that you change the password to the code immediately after downloading it, as any of your users will be able to find this document containing the default password I put on the code.
Because the digital signing used on Windows versions of Office is not compatible with the Mac versions of Office, you won't be able to unlock the Visual Basic Editor on a Mac, because the VBA password is set in the Windows version of Excel. Should you want to develop on a Mac, then remove the password in Excel on Windows, save it, open it in your Mac and set a new password the code, so your API token stored in the Settings sheet is protected.
To access the Visual Basic Editor please check:
- MS Windows: http://www.techonthenet.com/excel/macros/visual_basic_editor2010.php
- Mac OS X: http://www.techonthenet.com/excel/macros/visual_basic_editor2011.php
When trying to open the content from the project explorer, enter the given password. If you did not change it yet, enter “REPLACEME”.
In Windows, change this password via the menu Tools – VBAProject Properties, click OK and save the workbook.
The code
The module CanvasApiReportBase contains the main code for downloading data from Canvas and populating the sheet with content. The form provides input for the required settings.
CanvasApiReportBase content:
|
Sub or function |
Purpose |
|
CleanUpSheet |
To clean up data the sheet so it looks nice and tidy before you send it to your colleague. Meant to run manually from available macros via the Developer tab. |
|
ClearSettings |
To delete the Canvas url and the token and reset the options for the desired content of the first and second column in the sheet. Meant to run manually from available macros via the Developer tab. |
|
EditSettings |
To show the settings form, only available if no data is being downloaded |
|
LoadSettings |
To load all settings in variables before downloading is possible. Each time the Download data is clicked this function is executed. |
|
ValidateSettings |
To validate the entered settings, if it returns false it will indicate that downloading data from Canvas will fail. |
|
GetReportAllEnrolments |
The main sub to download data from Canvas. |
|
GetCourseName |
Performs API call to get the course name associated by the course id. It is also the first call performed, if it fails it indicates a connection error |
|
GetUserSelf |
Get the name of the user associated with the token used in settings |
|
GetAllActiveStudentEnrolments |
Get all students in a course and populate it in a collection |
|
RequestAbortDownload |
Prompts to stop processing any further. Will run when the Download data from Canvas button is clicked while running a report. |
|
UpdateDownloadProgress |
Updates the percentage of the download progress in cell A3 |
|
ResponseStatusErrorMsg |
Displays a message in case of any connection errors |
|
DebugPrintDictColl |
Recursive function to iterate through all items from the Response.Data object. For debugging purposes. |
The code depends on the library VBA-WEB created by Tim Hall which offers the API and JSON support in Excel for Windows and Mac. These are the Web* and *Authenticator (Class) Modules in the project. Documentation and downloads can be found at http://vba-tools.github.io/VBA-Web/docs/
Versions history:
- 1.0
- Initial version
- 1.1 (December 16, 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.2 (October 2021)
- Updated API processing framework, eliminating slow performance issues in Excel some users experienced with previous versions
- 1.3 (July 16, 2024)
- 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
- Prevent Excel from automatically changing numbers such as SIS-ID’s and other numbers which don’t need calculating by e.g. formatting cells as text (instead of numeric or general) using NumberFormat = "@"
- Format cells before populating data and do that from VBA instead of manually in Excel (see http://www.excelhowto.com/macros/formatting-a-range-of-cells-in-excel-vba/)
- Preserve Mac compatibility as much as possible. Mac OS X does not support Active-X, but there are plenty of alternative solutions (see http://www.rondebruin.nl/mac.htm)
- Use the Live API to easily test the API responses before writing your code. Check yourinstitution.instructure.com/doc/api/live
- Please consider sharing your creations. Like you, I am also a Canvas user who also learns a lot from other members in the Canvas Community. Your contributions are much appreciated!
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
