Automated progress report of students in modules

[stelpstra]

Attached Excel workbook is able to download data from Canvas (using Get API calls) to create a progress report of all students in a course with modules to which requirements are added (see How do I add requirements to a module?).

(no data means not yet downloaded while download is in progress)

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)

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?)
  
• select the desired content for column A and column B
• click Save

Run progress report

To run a progress report:

• find the course ID of the course from which the report needs to be generated in the address bar
  
• fill in the Course ID in the worksheet at cell B2 (e.g. 210)
  
• click the button Download data from Canvas to create the progress report

Depending on the amount of students and module items this may take a some time (about half a second per student).

In the top-left corner the download progress is displayed in percentages.

To cancel the download progress click the button Download data from Canvas again (only while download progress is less than 100%, otherwise downloading would start again).

Versions history:

• 1.11 (July 29, 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.
  • Changed some Excel formulas to prevent #DIV/0! from being displayed
• 1.13 (November 9, 2022)
  • Added a row "Requirement" below the module item names
  • If a module item has no requirement, the completion information per student stays empty and a comment is added for explanation
    (note: When no requirements are configured for a module, Canvas assumes progress as Complete, whether students interacted with the content or not at all)
• 1.14 (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

• Disclaimer: use this workbook at your own risk (I cannot be held responsible for any undesired consequences). This Excel workbook only reads from Canvas, never writes to it and 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
• Check samples from reports with actual data in Canvas or in case your student(s) get accounted for their activities in modules 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 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 report of assignments in courses 
• 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) 

[stelpstra]

@twassmer, nope, not the purpose of this tool.

@matthewheinlein, thanks, same to you. This blog has been updated several times. If you read it, you'll find your answers 😉

Edit: I see now that you meant not the tool described on this page but another one of my tools. There are many problems with my pages from the start they Instructure migrated the community to this new platform. I am in contact with community managers, but not much is improving. Therefore the other tools you can also download from the google drive linked on this blog. The versions you find there are the latest versions of my tools.

[okiepoke]

Any ideas for how someone with a Mac running Version 16.44 (365 subscription) might be able to use this? I was SO excited when I came across this thread; however, my Mac won't even let me open the Settings. 

You'd think Canvas would have better analytics by now, especially with a thread like this one that provides a legit solution that a lot of people are looking for. 

Thanks in advance for any ideas you might be able to provide. 

[ashleyrshipp]

You are awesome!!

I was in the process of writing a Python program to do the same thing and export to Excel... don't know why I didn't think of using VBA directly!?!

You just saved me hours of coding!

[nfenger]

For those experiencing slow performance of Office 365. I was able to resolve the issue by temporarily disabling Windows Defender real time protection and opening an PowerShell window as administrator and running this command mentioned here. Because I don't really know the implications of disabling AMSI (Windows Antimalware Scan Interface) I re-enable it when I'm done  by changing the $true in the command to $false after I was done and re-enabled Defender real time protection.

To disable AMSI run this in PowerShell

"[Ref].Assembly.GetType('System.Management.Automation.AmsiUtils').GetField('amsilnitFailed','NonPublic,Static').SetValue($null,$true)"

To re-enable AMSI run this in PowerShell

"[Ref].Assembly.GetType('System.Management.Automation.AmsiUtils').GetField('amsilnitFailed','NonPublic,Static').SetValue($null,$false)"

 If you get an error "This script contains malicious content and has been blocked by your antivirus software." you forgot to disable Windows Defender real time protection. Don't forget to re-enable it when you are done.

You can find lots of resources on running PowerShell as administrator on the web, just search it.

If anyone knows the implications of leaving AMSI disabled please let us know.

[peter_tan]

Hi, 

Thank you for this great work.

It works from a root account API generated access token.

I'm trying to figure out if this excel sheet can pull from a sub account API generated access token? It doesn't seem to work so far for non-root account API Access Token.   

 Any help there? Thanks 

[stelpstra]

@nfenger Thanks for sharing your findings, although I would never recommend disabling Windows Defender.

Instead, find the Microsoft Defender Version Installed in Windows 10 and make sure it is updated to at least Antimalware Client Version: 4.18.2102.4

Recently I worked with Microsoft Support to find this to be the root cause if the problem. Older versions of Antimalware Client will most likely cause these problems.

[stelpstra]

@peter_tan, the token should be created in your personal account, not on a sub-account level. Please create a token as described here https://community.canvaslms.com/t5/Admin-Guide/How-do-I-manage-API-access-tokens-as-an-admin/ta-p/89
With this token you can access courses in the account(s) you are admin in and courses you have a non-user course role (having the correct permissions).

[dwillmore]

This is great, thank you.

[stevetuxford]

Hey mate, love what youve done - I recall asking for the VBA password as I need to edit it, however I have seemed to have lost it.

[stelpstra]

Hi @stevetuxford, the code is protected because your token is stored in a hidden sheet which can be compromised by anyone with the password. Therefore I will not share the password with anyone.

[stevetuxford]

Hello @stelpstra thank you for the reply that is fair enough. Pardon my ignorance if this is also a problem but is it possible to just get the script then in a separate document without compromising anyones security?

[stelpstra]

Hi @stevetuxford, I have sent you a private message.

[CoreyMcNeill]

@stelpstra This is some fantastic code that saved me a lot of time! Thank you for creating this! I am working on another project where I would like to take the API call for the course activity and compile it similar to how you did for the module progress. I understand that your VBA code is password protected for valid reasons. However, would you mind sharing the source you used to create the code? Thanks! 

[stelpstra]

Hi @CoreyMcNeill , thanks! Did you look at Download data to Excel using VBA and the API (workbook with code attached) and is that what you are looking for?

[CoreyMcNeill]

Hey @stelpstra , I did try that, but I got this error message. 

[stelpstra]

Hi @CoreyMcNeill, the page appeared to be hidden without me being able to detect or change that. The page has been made available again.

[stelpstra]

Hi @RussTol,

You posted a message on this page and the same on one of my other pages. Something went wrong in deleting the double message so your question was:

Just saw this thread and this is great.

I tried using CanvasModuleProgress1v12, but an error window is displayed:
"Unable to complete the request"
Canvas returned the message: Unauthorized
Would you like to abort?

The downloading of the report stops at 28%
I'm using Excel 365, Windows 10.  

Help.

The module progress report respects the configured roles and permissions set by your Canvas admin for you. This message could mean that you don't have enough rights to see the course, student data, or specific elements of that. Did it download progress of some of the students or no progress at all, only the names of student or not even that? What happens if you click "no" when you get the error message?

Information of roles and permissions depending on your role can be found at:

• https://s3.amazonaws.com/tr-learncanvas/docs/Canvas_Permissions_Course.pdf
• https://s3.amazonaws.com/tr-learncanvas/docs/Canvas_Permissions_Account.pdf

So it could be many things. If you send me more details via a private message on which details you see it stops and as much details answering the questions above, than together we may be able to find the cause of this issue and hopefully solve it. Screenshots of the data in the Excel sheet where it stops do help a lot.

[jdgarcia]

Thank you so much Jaap! We use this tool a lot in our university and the latest version works so much quicker than before. We're really grateful of your work.

Greetings from Chile!

[CarolBuehrens]

Hi Jaap!

Kudos! This is a great tool and has been very helpful!

Is there a way to add the email address to your "settings" Content columns drop-down?  (Content column A and B, including pendings.) I am using this to help instructors monitor their online courses that have progressive enrollment, and with an email address at their fingertips, they can click and send a reminder to help students stay on track. Thank you!

[stelpstra]

Hi @CarolBuehrens, glad to hear it has been helpful. Unfortunately, the email address is not in the objects that are used in this project and would require developing new functionality to be able to incorporate your request. I'm not working on these projects actively any more, only fixing bugs if there are any.

Alternatively, you could add an extra sheet, adding student details and lookup formulas (e.g. using Index Match) and other formulas to complement the data from the report. Note that the ModuleProgress sheet creates new rows when pulling in new data, so instead of using direct references like this =ModuleProgress!$B$7:$B$60 (this would automatically change every time you run the report) you should use dynamic ranges instead as explained at https://exceljet.net/excel-functions/excel-index-function and https://exceljet.net/formula/dynamic-named-range-with-index

I hope that helps.

[eglover1]

This report is running great for me now. Thanks so much for the updates you've done. I was wondering if this report can include the student's current grade percentage in the course when it compiles their progress. I wasn't sure if I was missing something.

[stelpstra]

Hi @eglover1 glad to hear it has been helpful. Unfortunately, the grades are not in the objects that are used in this project and would require developing new functionality to be able to incorporate your request. I'm not working on these projects actively any more, only fixing bugs if there are any.

[nicolelschrock]

Disclaimer:  I am self-taught on this type of task.

In settings I'm only getting the first two fields in my window.  I am using a Mac with 2018 Excel.  Is that the issue or something else?

I continue to get the error "unable to complete request:  ERROR 401"

[stelpstra]

Hi @nicolelschrock, VBA (the coding language used) may not be fully supported in the Mac version of Excel 2018. I haven't been able to find a VBA compatibility list for Mac versions, but this post indicates there are issues with how forms in Excel are used from VBA in version 2018 on Mac. Try version 2011 (see 'compatibility' section) if that is available to you on Mac. Alternatively, on Windows it works fine.

[DFarrarUTH]

Just - just - thank you!  This is exactly what I have been looking for! 

@stelpstra  I am having an issue and I am not sure why - everything is uploading correctly; however, neither the SIS User ID nor the Login ID (when I update the settings) are populating.  I would like to be able to pull in their emails. Any ideas?

[stelpstra]

Hi @DFarrarUTH, nice that you like to tool.

I'm not sue what you mean by "everything is uploading correctly". The settings need to have your the Canvas hyperlink of your institution configured and your token needs to be an active token, belonging to a user account that has teacher-like access to the course you have configured at Course ID. The related course needs to have students of which the accounts are active (not invited, pending or anything else than active). If any of this is not correct, you will either see errors or get an empty report.

If this doesn't help you solve your issue, please send me a private message with screenshots and a detailed description of your situation, the more details the better the root cause can be identified.

Regarding adding the email addresses, this has been requested before and I'm not planning to add functionality any more, therefore it won't be possible to display email addresses.

[stelpstra]

I have added an updated version (1.13), these are the changes:

• A row "Requirement" is added below the module item names
• If a module item has no requirement, the completion information per student stays empty and a comment is added for explanation
  (note: When no requirements are configured for a module, Canvas assumes progress as "Complete", whether students interacted with the content or not at all)

[phonehome]

TLDR: The progress tracker stopped working, as I was using a pretty old version. I downloaded 1.13 and it works great again!

...

I have been using this tool for years----kudos to Jaap for creating it! It really should be a default feature of Canvas (as opposed to invididual-level manual progress checking). 

However over the years it has progressively stopped working on my devices. Initially it worked on both my personal and unviersity laptop. Then a few years ago it stopped working on my university laptop but still worked on my own. And this semester it's no longer working on either. Mind you, these devices are constantly being updated (new work and personal laptops this year). Yet somehow they seem to be getting worse and worse at handling the reports. 

Specifically, what happens is that I set everything up correctly (API token, subject number, enable macros & content, etc.), and as soon as I run the report, Excel freezes into a blank screen). It seems to be unable to cope with the task, even though my machines now are more powerful than before. Is there any reason why it would always freeze like this? Is there anything I can do? I've tried giving Excel higher priority in the Task Manager, but it doesn't seem to help. It just stays frozen forever. 

EDIT: *facepalm* I downloaded the most recent version (1.13) and it is working great. I guess I was using a very old version which wasn't working with current Office... I'll leave this up here, in case anyone else finds themselves in my shoes. 

[stelpstra]

Hi @phonehome, great to hear your enthusiasm! To be honest, I have no idea how many people are using it, so it's nice to hear that it is still relevant to people to use it. Nice that it is working again with the latest version 😀

[cdoty]

I keep getting an unauthorized error in addition to a "no course associated" message.  Both are incorrect as I have all access in my instance and the course exists...I have attempted on multiple courses (and instances).  Has anyone else run into this issue?  Any troubleshooting help is appreciated!

[stelpstra]

Hi @cdoty, I have used it last Tuesday without any issues. Are you using the latest version (1.13)? Is your token still valid? If so, please send me a private message detailing the url you entered in Settings, the length of the token (not the token itself! and the course id that you filled in), then I'll try to help you.

[cdoty]

@stelpstra thank you so so much for all of the additional help!  I think it may have to do with my "super admin" permission since I know this is working for others.  Thanks again for going above and beyond to assist me!!

[Denham]

Hi @stelpstra 

I've found the spreadsheets you created very useful. I was hoping to modify one to pull a different set of data I was after but the code section is password protected. Any chance you've got a post somewhere going through the basics of how you did these?

Kind Regards,

Renee

[Denham]

Hi again @stelpstra  you can ignore my previous question, I found your other post in the Canvas Developers Group with a starting point for creating one of these, greatly appreciate you sharing that 🙂

[DFarrarUTH]

Hello, @stelpstra!  

I am tasked with mining data from our Canvas courses and have had great success with your workbook - thank you!  

However, when I attempted to open older, locally-saved versions of the workbook, I received an error message, "Excel cannot open the file..."

Today, I downloaded v1.13 from your Drive and received the same error when attempting to open the file.  Do you have any solutions for this issue?   

[MattLaidler]

Hi @stelpstra 

This looks amazing! However whenever I try to run the code I get the initial information (the module content) but then the download stops at 2% and does not populate the student data. Any ideas on what could be causing it? If not can you tell me which field from which data table you pull the completion status from please? Would you be willing to share the code you have written for pulling down the data?

[stelpstra]

Hi @MattLaidler, does the user account belonging to the token have sufficient permissions to access student details in the People menu item? The permission "Users - view list" must be enabled for the role that user account has. Another reason could be that the course does not have any students with an active enrolment in the course. Also check that the modules have requirements configured and that the course has been published, meaning it is accessible to students. If that doesn't help you solve the issues, please send me a private message and I will try to help you.

About sharing the password to the code, I will not share it with anybody as mentioned a few times before, for the sole purpose to prevent any possibility of exposing the token of anyone using this tool.

[stelpstra]

Hi @DFarrarUTH, I've had similar issues with my tools at the university where I work. Our IT department has applied many security policies of which one or more were blocking the use of macro's, which are VBA code. For me and a bunch of colleagues to still use the tools, for us they configured a specific folder (called no_app_control) that has less heavy policies applied of which the VBA restrictions are less strict. They told me they are not exactly sure which policies were responsible for the error messages. Fortunately it solved our problem. Could your situation be similar? Feel free to send me a private message about this topic.

[JagrutiD]

Hi Jaap,

Your automated canvas reports are awesome but i am facing issue in one of the reports specifically the module progress report, when i run it for a course which has more than 2000 users it gives out an error like "This course does not have modules and/or module items, or you may not have the required permissions in Canvas for this action" and when i click ok i get another popup saying "An error occured For loop not initialized"

This course has only Non graded surveys and has about 333 section in which roughly 7 students are there who have been assigned diff surveys

I have admin access, the same report work for different course id perfectly, please assist.

Screenshots for reference

https://drive.google.com/file/d/1BrhkG77XuQSj6w3Luc-TXJMaSrxX5v3S/view?usp=drive_link

https://drive.google.com/file/d/1LEzJpI5pIF-VH870ZWeihEy_LQmG5GQM/view?usp=drive_link

Thanks & Regards

[stelpstra]

Hi @JagrutiD, 

There can be many variables why this would happen, without seeing it and checking some details with you I don't know what could cause the error. If you want we can schedule a brief moment to have a look at it together. You can contact me at jaap.stelpstra@eur.nl and let me know when would suit you. My timezone is of the Netherlands and I do not work on Fridays.

Kind regards, Jaap.

[dharmas]

Hello @stelpstra ,

Thanks for the amazing work. We're getting error in the middle.

• Mac OS - The report is always failing at 4%. Error message is An error occurred. Application-defined or object-defined error
• Windows: It's stopping around 40-60 % randomly. The course is having 2900 students. Error message is An error occurred. Application-defined or object-defined error

Is it possible to fix the issue or we can work on the fix if you can share the code. We're happy to contribute.

Once again, kudos for the amazing work!

[stelpstra]

Hi @dharmas, the more students a course has, the slower Excel gets pulling the data in and processing it. Most likely memory problems occur as VBA is just a scripting language (with which this tool is made) lacking good memory management, as first the list of al students are pulled in and kept in memory to be able to get progress data of each student. With so many students the amount of memory needed explodes. Unfortunately I have no solutions for processing such large courses. Your next question may be if I can give you the password to the code, but anyone who would have that password would be able to capture the token of the user using the tool, which is a high security risk and therefore I do not share that password.

[blduran05]

Hello @stelpstra! This is a fantastic report, thank you for creating it! Just a couple weeks ago it was working fine for me but suddenly now I'm getting this error message "An error occurred. Object variable or With block variable not set". 

Looking at past comments on this page, it seems this has been an issue before due to Instructure changes. Any chance it's the same issue again and fixable? 

Thank you!

Brianna

[stelpstra]

Hi @blduran05, you're welcome. Yesterday I used the report myself without any problems, can you elaborate on what changes by Instructure you mean that may have affected the functioning of the report in case I missed it? I am aware some cases with 1000+ students in a course that is sometimes too much for Excel to handle and I don't have options to debug that many students. You can send me a pm with details of your course and enrolments so I can reflect en respond to that. Thanks.

[blduran05]

@stelpstra Thanks for the quick response! Unfortunately I don't appear to have access to private messaging (no inbox icon in my toolbar), perhaps because I only have a Canvas Free for Teachers account..

My course lists are very small - <35 people each. I just assumed it might be an Instructure change given some of your past posts around this error message but sounds like that's not it since it's still working on your end. 

The report is pulling some data (2%) - it pulls the titles for each module item but then it errors out before it pulls any student data. 

[stelpstra]

@blduran05  Hi Brianna,you may not have enabled your private messenger: https://community.canvaslms.com/t5/user/myprofilepage/tab/user-preferences:private-messenger If you enable it you can send me a pm.

I'm online for the next hour, if you send a Zoom link or similar within that hour I can have look with you at that your situation.

[blduran05]

@stelpstra Appreciate you - I'm logging on now: https://edf.zoom.us/j/83350481711 

[CarolBuehrens]

Hi Jaap!

I've been a longtime fan of your progress report. However, this morning our spreadsheets suddenly stopped working. All course modules start loading and then we get an error at 2%, which appears to be when the students are being loaded:

"Microsoft Excel: An error occurred. Object variable or With block variable not set."

Could this be caused by a recent change by Canvas? (I noticed quite a few code issues have been corrected over the weekend.)

Please let me know if you have a workaround.

Thank you! - Carol

[DFarrarUTH]

Good morning!

I am also receiving the "Microsoft Excel: An error occurred. Object variable or With block variable not set." error. 

I found this information on Microsoft Help: https://learn.microsoft.com/en-us/dotnet/visual-basic/language-reference/error-messages/object-varia... 

Unfortunately, this is outside of my expertise, so I don't know how to repair it within your workbook. 

Please help when you can - this report has been the backbone of my data.  Thank you!

[stelpstra]

Hi @CarolBuehrens and @DFarrarUTH, thanks for letting me know.

I fixed the issue which was related to this change https://community.canvaslms.com/t5/The-Product-Blog/Forthcoming-Update-to-Canvas-API-Response-Header...

You can find version 1.14 in the Google Drive link mentioned above in the original blogpost.