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

Adventurer
30 37 9,005

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.

243780_CanvasApiGroups01.png

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:

243790_VbaEnableEditing.png

243791_VbaEnableMacroContent.png

Settings

First fill in the settings:

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
    243794_CourseIDfromURL.png
  • fill in the Course ID in the worksheet at cell B2 (e.g. 200)
    243795_CanvasEnterCourseID.png
  • 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:

243788_CanvasApiGroups03.png

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:

243789_CanvasApiGroups04.png

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.

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

Additional tools

37 Comments
Adventurer

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

Surveyor

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

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

Community Member

Hi, stelpstra@eur.nl‌.  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

Community Member

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

Learner II

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

Community Member

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

Learner II

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

Learner II

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.

Adventurer
Learner II

Thanks for the idea.

Surveyor

stelpstra@eur.nl‌ 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.

Adventurer

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.

Learner II

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?

Adventurer

Hi susan.oconnell@duneland.k12.in.us‌, 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...

Learner II

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?

Adventurer

Hi mieke.hoing@kdg.be‌, 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:

I hope this is helpful to you.

Learner II

Thanks for your quick response stelpstra@eur.nl‌. 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.

Adventurer

Hi mieke.hoing@kdg.be‌, 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!

Learner II

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

Community Member

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

Community Member

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

Adventurer

Hi psimon3@asu.edu‌, 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?

Adventurer

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 

Surveyor

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

Adventurer

Hi j.braak@uva.nl‌, 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.

Community Member

Hi stelpstra@eur.nl‌,

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!

Community Member

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

Adventurer

Hi nfenger@apps4pps.net‌, 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:

335131_pastedImage_3.png

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.

Community Member

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.

Adventurer

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.

Adventurer

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.

Surveyor II

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

Surveyor

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

Surveyor

This is awesome, thanks for sharing @stelpstra .

Surveyor

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)

Learner II

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 🙂

Surveyor

@stelpstra  So great!  Thanks.