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

stelpstra
Community Champion
38
15987

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.

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

38 Comments