Automated report of group sets, groups and members in a course

[stelpstra]

Attached Excel workbook is able to download data from Canvas (exclusively using Get API calls) to create an overview of all group sets, its groups and its members in a course.

Group sets and groups are displayed (vertically) in column A and B (see Settings).

Additional information received from Canvas (for each group set, group and member) is available via cell comments (raw data delivered via the API).

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 2011 on Mac OS X (tested on 2011 version 14.7.3. 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?)
  
• select the desired content for column A and column B (optional)
• Select if the groups must be listed vertically or horizontally
• click Save

Download groups data

To start downloading

• 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. 200)
  
• click the button Download data from Canvas to create the report

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

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

If in Settings, Content column B can be set to blank, but if a type of content is selected, an extra column containing that information is added:

If in Settings, List groups is set to Horizontal, group sets and groups will be displayed in row 5 and 6 respectively instead of column A and B:

Group leaders will be displayed in italics.

To easily distinguish the group members, the cell background colour can be set as default in cells I2 and J2.

Versions history:

• 1.0 (July 24, 2017)
  • Initial version
• 1.1 (August 3, 2017)
  • "group" Unassigned added, displaying unassigned students per group set
  • Sections information is added to the comments per enrolment
  • In Settings, options to select Sections for column A or B are available
• 1.2 (November 21, 2018)
  • Bug fix: login id was missing from the data
• 1.3 (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.

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 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 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 assignments in courses 
• Create course groups from sections using Excel 
• Download data to Excel using VBA and the API (workbook with code attached) 

[baxl]

Just found this and helps me so much!  Thanks for sharing

[caramirezs]

 @stelpstra , thanks a lot, this help me so much too.

"(for fun, to learn, to share and because I can)" nice one! 

[frankel]

Hi,  @stelpstra ‌.  I tested the spreadsheet in my test area, and it worked fine.  However, when I tried to use it on my real class, I received a time-out error and a message saying the course had no enrollments.  Then, when I closed the spreadsheet, there was a dialogue box asking for a password. When I closed that, it automatically closed and then restarted Excel.  Thanks in advance for any help you can give, as having this data would be very helpful for me and my TAs.

cdf

[frankel]

So, I started all over again -- generated a new token -- and this time it worked.  Thanks, so much,  for sharing your solution.

[c_murphy]

This is so helpful! Thanks so much for sharing! Made the elective enrolments (which are not handled by registry) far less onerous. 

[cathy_leahy]

Excellent tool, worked with no issues. 

For a future update it would be great if it also gave an indication of students who were no longer enrolled/active

Thanks

[Boekenoogen]

We are just now starting to use groups more in our courses. I think this will be a great tool to use.

[Boekenoogen]

Is there an API call to search for courses that have groups? I have been looking, but I might not be in the right place.

[stelpstra]

Hi  @jrboek ‌, you could take a look at Group Categories - Canvas LMS REST API Documentation and Account Reports - Canvas LMS REST API Documentation and experiment with those using the Live API.

[Boekenoogen]

Thanks for the idea.

[caramirezs]

 @stelpstra ‌ question; " "group" Unassigned added, displaying unassigned students per group set", How did you do that? What does API function call unassigned students? Thanks a lot.

[stelpstra]

Hi caramirezs‌, you can get the unassigned students from List users in group category while adding the optional request parameter "unassigned". The unassigned group is not a real group you can pull from the api, but in the sheet I treat it as a group. I hope it is helpful.

[susan_oconnell]

This is great and it works perfectly at school (Windows 7, Office 2013). Thanks so much!

I'm having trouble at home (Windows 10, Office 2016). Excel locks up (not responding) after displaying the message that 1% had been downloaded. Any thoughts? My internet is fast enough, and everything else seems to be set up ok. Is there a macro setting I might be missing?

[stelpstra]

Hi  @susan_oconnell ‌, although I have not been able to test it in version 2016 myself, both Office 2013 and 2016 use VBA version 7.1. So it should work. Did you set the correct Canvas url in Settings? Could it be that there is a space after the course id you entered (happens sometimes with copying and pasting)? It is difficult to troubleshoot from a distance...

[mieke_hoing]

Thanks a lot! This is very helpful!

What confuses me is that I have chosen "Login ID" for Content column B, but this results in an empty column. Our students log in with their school email address. So I am wondering why this doesn't show in de spreadsheet? Am I doing anything wrong?

[stelpstra]

Hi  @mieke_hoing ‌, thanks for your enthusiasm. The data that is displayed is based on the amount of permission you have in that course and the sub-account of that course. Perhaps the permission to see the login id has been disabled for your role. You can see what permissions are available here:

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

I hope this is helpful to you.

[mieke_hoing]

Thanks for your quick response  @stelpstra ‌. I am an admin of the subaccount I was working in, so I don't think that is the problem. I have tried enrolling myself in the course with a teacher role, but this does not solve the problem. I still get an empty column.

[stelpstra]

Hi  @mieke_hoing ‌, thank you for pointing this out. You helped me identify a little bug which I fixed immediately. I have updated this page and attached version 1.2 for any one to download and use. Could you please test it for me and let me know if the issue is solved completely? Thanks!

[mieke_hoing]

Hi  @stelpstra , version 1.2 does indeed give me the email addresses. Thank you for fixing this so quickly!

[psimon3]

Huge time-saver. Note that it didn't work on MacOS. I had to use a virtual machine running Windows. 

[psimon3]

At my institution, a unique ID + the domain name yields the email address. Great add for those other schools, though. 

[stelpstra]

Hi  @psimon3 ‌, Excel version 2016 for Mac barely supports VBA so that won't work. It should work in version 2011 of Excel. Which version do you use?

[stelpstra]

I recently also added the a tool to create groups from sections: https://community.canvaslms.com/docs/DOC-16249-create-course-groups-from-sections-using-excel 

[j_braak]

Hi Jaap,

I have been using the Excel for quite a time.
But now it freezes at 1%. Can it be that it doesnt work with office 365?

Best,

Jeffrey

[stelpstra]

Hi  @j_braak ‌, it should work in the desktop version of Office 365. Could it be that your token is expired or that you don't have access to the course? Some people reported that new firewall policies caused some issues downloading the data. Please, let me know if you solved it and what you think caused it.

[robert_suurna]

Hi  @stelpstra ‌,

I was just about to start writing a python script to fetch groups and its members in a course when I found your Excel workbook. Thanks for sharing!! Saved my a lot of time!

[nfenger1]

UPDATE: I finally caught the last few minutes after a long delay. It seems like there is a long delay of 10-20 min at 4% then it proceeds normally and you can see each student appear every half second or so. I did look at the VBA code from CanvasApiReportBase1v0 which is also having the delay and the code was looping through something during the long delay so it's not frozen, it's doing something. I also tested CanvasModuleProgress1v9 and it does not have the delay.

Office 365 works but it took about 20 minutes to pull group info for ~90 students (it was at 1% then 4% for most of the time looking like it was frozen). My copy of Excel 2016 installed on the same computer took less than a minute with the same Excel file. Something has changed with Office 365 that's seriously impacting performance...

[stelpstra]

Hi  @nfenger1 ‌, thanks for adding these comments. You are right that something seems to be changed with the O365 version. As our university is also implementing O365 I have been able to reproduce behaviour. I have been using the Process Monitor - Windows Sysinternals | Microsoft Docs to test what is going on. Our IT department mentioned it looks like the security hardening policies on our network is most likely causing the macro to become really slow. With hardening disabled they had no issues running it. The Process monitor is showing thousands of rows of this:

The CSC folder is a cache folder I appear not to have rights to to access. I found explanations like "CSC stands for client-side caching and is another term for offline file sharing".

I have not been able to test this on O365 on a private computer. Our IT people have made it possible to run it without any problems using O365 when the workbook is located in a special folder which they have excluded the hardening for. I hope to find the cause of it, but I'm no security policy expert so any help would be great.

[pisan]

Extremely useful. Puts my python scripts to shame since a lot more people can use Excel than execute python in a Unix shell. I have extracted the group information from Canvas to create pre-assigned Zoom breakout rooms which has been very handy for teaching.

One concern I have is that I cannot view the macros. I would like to view the macros to make sure that they are not writing anything to Canvas, they are not sending my Canvas token to a third destination, etc. Slight uneasiness on running macros that I just downloaded from the internet.

Would appreciate if you shared the VBAProject Password, so I, and others, could check the code. Thanks.

[stelpstra]

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.

[stelpstra]

For the duration of one or two weeks this report was not working and generated an error message that something went wrong and no student data was downloaded. This was because Instructure changed the method regarding the processing of API pagination of the enrolments API (I have not yet seen a notification this was coming). I updated the code handling the pagination for all API calls to accommodate for both the old and the new method. Version 1.3 is available for download.

[creme-brulee]

You are a genius! Thank you for sharing and making other people's lives easier!

[Dlh48304]

This is a very helpful tool. THANK YOU for creating and sharing it!

[leon_huang]

This is awesome, thanks for sharing @stelpstra .

[carlinjm]

Hi @stelpstra 

I'm getting this error using CanvasApiCourseGroupsReport1v3.xlsm:

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

It finds my course, but stalls at 1% downloaded

MacOS (v10.14.6), Excel 2011 (v14.7.7)

[susan_oconnell]

I recently upgraded my device and I am now getting the same error. Currently Using Windows 10, MS 365 Excel. The same spreadsheet worked on a Windows 7 machine with Excel 2016. I saw that you looked into this once before. Did you learn anything new? thanks for any help 🙂

[ecass]

@stelpstra  So great!  Thanks. 

[ewillia3]

Thank you for your contribution! This is a great tool that I've used until recently. I saw a workaround for what I need to do by generating a groupset, then clicking on "Import" and ironically you can download a roster from within the next dialog. 

However your VBA projects are quite fascinating and I'm interested in doing work on them myself. The tool I'm looking to build would be an API write to canvas for assignments so that extra time students can be given the amount of time like 1.5x and 2.0x accordingly. This is a very tedious process in Canvas and it should be centralized to ensure accuracy. There is no good way to do it on an assignment and there is no alternative to tons of clicks that i've found so far. Please advise if you know of anyone who has done this or if I'm starting something new. 

[jsdennis]

This has been a great tool, but you can now download students with team assignment directly (without an API token) from Canvas: https://community.canvaslms.com/t5/Instructor-Guide/How-do-I-import-groups-in-a-group-set/ta-p/41779...