Download data to Excel using VBA and the API (workbook with code attached)

stelpstra
Community Champion
8
34645

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.

CanvasApiReportBase-main2.png

CanvasApiReportBase-comments.pngData of the students are populated in the left two columns and the remaining available data is added in the cell comments.

It is up to you to add code to populate the areas where the headers and the data need to go.

CanvasApiReportBase-settings.pngTo set your Canvas url and the API key, click on the Settings button and fill in the required fields.

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:

VbaEnableEditing.png

VbaEnableMacroContent.png

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:

VBAProjectPassword.png

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.

VBAmenuVBAProjectProperties.png  VBAProjectProperties.png

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-VBAProject.png

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

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

Please find the XLSM file attached below this article.

Additional tools

Tags (4)
8 Comments
matthias_5
Community Participant

 @stelpstra ‌ This is a fabulous tool you have come up with. Thanks for taking the time to answer my emails regarding adding the ability to pull email addresses in addition to names and SIS ID numbers. The code you provided should enable us to customize your original spreadsheet.

Thanks!

Ken

stelpstra
Community Champion

Based on this concept I have built the following solutions for anybody (with an api token) to use:

I hope this is useful to you.

Feel free to share and comment!

creme-brulee
Community Explorer

This is awesome! Thank you so much for sharing! Do you think it would be possible to download data from the new quizzes with a tool like this. I can currently only see students' responses in speed grader one by one without the ability to bulk download students' responses for any kind of analysis. I have some experience with VBA but not very extensive. Thank you!

dju_white
Community Member

HI @stelpstra,
Thank you for sharing your VBA and detailed explanations. I have borrowed extensively from your original work and my code is probably not as elegant as your own, but I share it here as it might be of use to others wanting a course enrolment report.

The Excel worksheet below lists students enrolled in a course. In the 'Settings' window there is an option to choose active students or all students including those with a pending/invited status. In the 'Settings' window, the user must also enter their Canvas site URL as well as their own API token (as explained above).

The option also exists to use sub-accounts and sections to group types of courses. This needs to have been set up in your Canvas site before you can use the sub-account option. The sections in a course can be listed using a second button on the attached worksheet.

By selecting a sub-account ID rather than a course ID, all students enrolled in a group of courses within a sub-account will be listed. 

On a separate sheet, is a PIVOT table that groups all enrolled students by course ID and section ID and allows the user to filter students by date and enrolment status.

The VB developer code password is REPLACEME.

Link to Google Drive with worksheet: Canvas Course Enrolments Report.XLSM

(please save the file. It will not work if opened in Google Sheets. Users must also enable macros in Excel for the report to work).

David

stevetuxford
Community Member

Hello and thank you so much @stelpstra for this I have been searching for something like this a very long time.  I am trying to open the VB you provided however the REPLACEME code is not working?

@dju_white Thank you so much also with building on this!

stelpstra
Community Champion

Hi @stevetuxford, the code of the file by @dju_white appears to have a different password (which I don't have) which is recommended not to be shared to protect the token from being accessible to others. The file from my contribution opens the code successfully using the password given.

Kab07
Community Member

Hi. I am new to this and was looking for how to automate the process to download the student submission and progress. This document looks like what I am looking for. But when I am trying to follow the steps and click "Download data from canvas" it shows that "Canvas error 404...." not found the course ID. However i have out the course ID from the canvas URL. Please suggest what could be the possible issue @stelpstra 

TIA

stelpstra
Community Champion

Hi @Kab07, I have sent you a private message as there could be many logical reasons for this to be happening.