Automated progress report of students in modules

stelpstra
Community Champion
142
44181

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

Canvas Module Progress Sheet

Canvas Module Progress Comments

Canvas Module Progress Stats

(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:

Vba Enable Editing

Vba Enable Macro Content

Settings

First fill in the settings:

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
    Canvas Module Progress Course ID
  • fill in the Course ID in the worksheet at cell B2 (e.g. 210)
    Canvas Module Progress Enter Course ID
  • 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)

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

142 Comments
MattHanes
Community Champion

This is truly an amazing spreadsheet. I just ran it on a course with 3600 people with 3 modules. The only hiccup was on column B row 2002 it inexplicably stopped doing the CountIf function correctly. It started using a weird group of columns.

This is the formula on all the previous rows with a different row number after CountIf: =COUNTIF($C2001:$S2001,"completed")/17

On line 2002 and onward, it looks like this for all remaining rows: =COUNTIF(RC3:RC19,"completed")/17

It was easy to fix manually on the sheet, but I thought I would just let you know in case you wanted to fix it. Truly awesome work here.

stelpstra
Community Champion

Hi Matthew, nice to hear that you like it and thanks for sharing details about the formula issue.

It appeared that as of row 2002, cells in column B were formatted as text and therefore the formulas did not work from there.

I've changed the code so these cells are formatted as percentage prior to populating it with the formulas and attached version 1.3 to the document.

MattHanes
Community Champion

You, sir, are a rockstar!

dhulsey
Community Champion

 @stelpstra ​, thank you so much for this. Can you confirm that giving a zero to a student on an assignment (for non-completion) means that item will be marked as complete in the spreadsheet? That is what it looked like in my preliminary testing this morning.

stelpstra
Community Champion

Hi Dallas, you're welcome.

Canvas returns only true or false on the "completed" status form a single module item per student. If true, the word "completed" is put in the spreadsheet, if false the in the state of the module is used in the spreadsheet (it populates either "locked", "unlocked" or "to do").

I tried your scenario by grading (speedgrader) a student which has not opened or completed the assignment and Canvas gives back an "unlocked" status, which means the the completed status of the module item for this student returned false.
You can easily try the API response of your particular case in your live API at https://YOURINSTITUTION.instructure.com/doc/api/live#!/modules.json/list_modules_get_0 using the GET method, supplying the course id, selecting "items" at include and supplying the student id. I hope this is helpful.

dhulsey
Community Champion

Thank you!

bfelix
Community Participant

Great resource! We are trying to do this but noticed that the role is locked in as student. We went to try to access the VBA but looks like its password protected?

We have another role based on the Student role that we would love to run this against. Any ideas?

Thanks!

Brad

stelpstra
Community Champion

Hi Brad,

The user associated with the used token needs to be an admin, or a teacher or ta in that specific course you want to get the data from. Otherwise you won't be able to download module progress data and there will be an authorisation error, Canvas simply won't let you.

Secondly, the list of enrollments is limited to the enrollment type "StudentEnrollment" (including instances thereof you may have created), because Canvas will only supply progress information of students and not of enrollment types "TeacherEnrollment", "TaEnrollment", "DesignerEnrollment" or "ObserverEnrollment".

If this is not helpful enough, can you explain a little more specific what you are trying to do?

bfelix
Community Participant

Thanks for the reply, Jaap. The issue is that the data does not seem to include the additional instances of StudentEnrollment. It only pulls exactly StudentEnrollment. Since most of our students are under a derivative of this role, we dont get the data we want.

We thought perhaps we could adjust the role in the code. Is this possible?

Thanks.

stelpstra
Community Champion

Hi Brad, I corrected a small mistake where I accidentally used "role" instead of "type" (of role). While I was at it I added the actual role of each student to the cell comment per student. I tested it successfully, but please try it and confirm if it works on your instance too. Thanks.

bfelix
Community Participant

Working! Fantastic. Thanks for the quick fix, and great work overall! This workbook is quite helpful to us.

bfelix
Community Participant

Hi Jaap,

We also just found a need to grab student ID's in the sheet. But rather than ask you for repetitive "feature requests", would you be willing to share the password such that we can work with the sheet ourselves? We would be happy to share back any useful development efforts.

Thanks for considering.

Brad

stelpstra
Community Champion

I have added a new version (1.5) in which it is possible to choose the type of content for column A and column B. Under Settings the options Sortable name, Full name, SIS login ID, User ID and Role can be selected:

CanvasModuleProgressSettings.png

floresr
Community Champion

This is a very useful report, but I'm afraid most faculty would not be able to benefit from it since it requires getting into the API.

It would be awesome if the process could be made simpler (one or two clicks) so that faculty can run their report for any given class they teach, without having to learn Get API calls. Does anyone know if the development team might have such a thing in the works?

Thanks!

Ricardo

60980756
Community Participant

 @stelpstra ​, this seems like a marvelous, even miraculous solution to my own challenges in tracking student progress. However, I can't seem to get it to work.

When I run the macro, it seems to successfully retrieve the course title, Module names, and Module item names... but then I get a endless series of dialog boxes saying, "An error occurred: Unauthorized." and then "Bad request". I have to force-quit Excel entirely to make it stop. Strangely, there is one course for which it works without error - an old personal 'sandbox' course I set up to experiment with. All other courses give the error I described above.

My account has domain-name-wide access, and I'm listed as a Designer on the specific course I'm testing. I generated a new token specifically for this task. I'm using Excel 2011 for Mac in El Capitan (OSX 10.11.3)

Have you or any other user of this Macro experienced this problem? Any idea what might be causing it? Is there a specific role I need within the course, or are there any settings in the course itself that might be blocking access?

stelpstra
Community Champion

Hi Ricardo,

Teachers don't have to learn anything about API calls, they only have to get create their token and fill it in under settings in de Excel sheet, nothing more. How to create a token is descibed in this guide How do I obtain an API access token for an account?

If even this would be too difficult for teachers, your admin may be able to help by creating a token after masquerading as that particular teacher, creating the token, fill in the settings in the Excel sheet and send it to that teacher.

Another (less recommended) solution is to use one token for all teachers, created under an account with teacher or admin access to all relevant courses.

Perhaps this is helpful to you.

Thanks,

Jaap.

stelpstra
Community Champion

Hi Ethan,

The reason you are getting these errors is because Canvas (not the sheet itself) does not alow you as a course designer to have access to the student data. As far as I know the Canvas API documentation does not specify which roles are alowed to read student progress data nor if these permissions are fixed to a role or that this specific permission can be enabled via Permissions (see How do I set permissions for a course-level role in an account?) by an admin. Perhaps your institution disabled the permission "View usage reports for the course", although I am not sure this would solve your issue. You could ask your admin to look into the permissions for the course designer role. Please do share if this solved it.

In your sandbox course you may be teacher or even admin which is the reason why it works without errors.

Regarding the repetition of the error messages, in the next update I will provide the option to be able to abort downloading data.

I hope this is helpful to you.

Thanks,

Jaap

stelpstra
Community Champion

Hi Ethan,

I improved the error handling, which enables you to cancel the download process in case Canvas returns an error when downloading data.

CanvasModuleProgressAbortMessage.png

Version 1.8 is attached.

Thanks,

Jaap

stelpstra
Community Champion

Fixed a bug related to handling API pagination, which could generate an error when using Mac OS X. Version 1.9 is attached.

60980756
Community Participant

This latest version fixes the problems I was having! If you've tried this before and weren't able to get it to work, I encourage you to try it again! Thanks very much to  @stelpstra ​!

f000f2p
Community Novice

Hello Jaap,

The Macro runs great! Thank you!

I have a question on 'the state of modules'. On the spreadsheet, I got 'to do' value for a number of students on a few module items. However, I got no 'to do' value return instead of a 'started' value if I ran a script to get the value of state of module for a student (/api/v1/courses/:courseid/modules/:moduleid?student_id=1234&include[]=content_details). Did you write in your Macro to switch 'started' to 'to do'?

I understand the 'completed' (students complete the module item), 'unlocked' (a module item is unlocked for students to access) or 'locked' (a module item is locked by future date or prerequisite) for the state of a module item, how is 'to do' or 'started' defined/emitted? what did students do to get 'to do' or 'started' return for the state of module items?

stelpstra
Community Champion

Hi f000f2p​,

This is the logic applied:

If Module_Item_Completed = true Then text = "completed"

ElseIf Module_State = "started" Then text = "to do"

Else text = Module_State

See Modules - Canvas LMS REST API Documentation

The status "started" is a bit misleading, because all module items in a module would get the state "started" as soon as the module would be unlocked, while the student might not even actualy started all the module items at once.

I hope this is helpful to you.

f000f2p
Community Novice

This is quite helpful. Thank you!

‘to do’ is more precise than ‘started’, especially for module items that yields participation/submission, such as quiz, survey, assignment, discussion, etc..

For instance, students can preview a quiz module item instead of taking the quiz, ‘started’ would be misleading, while ‘to do’ makes better sense.

Jing

dlupianez
Community Novice

Hello, this is a great tool!

i'm having an issue with the spreadsheet.

It downloads all the activities for all the students with completed status.

screenshot.png - Google Drive

What should i check for it to work ok?

Best regards!

dlupianez
Community Novice

I have found the answer to this question, module requirements needs to be configured.

How do I add requirements to a module?

Best regards!

nrblair
Community Novice

 @stelpstra ​. Thank you so much for this amazing tool! It is so useful and easy to use. I appreciate you sharing this out so much! I was curious, is it easy to add a column B option for section? It would be great to pull down the report listing student by sortable name in Column A and Section in Column B. Is this a possibility for future iterations or is it something I can add myself to the spreadsheet?

Thank you again!

Natalie

stelpstra
Community Champion

Hi  @nrblair ​, great question. A student can be in multiple sections and to populate this in the Excel sheet would require a dynamic amount of cells to be useful (e.g. for sorting and filtering). Additionally, when getting the enrolment data, the only data that is returned about the section(s) is the section id (it is a number). It would require an additional request (per student and per section) to get the name of the section, which takes about 0.3 second per request and will slow down the download process significantly. I can think of a work around to save some download time, but in either case it would require a fair amount of programming and testing using different scenarios.

Currently I am not planning to add additional functionality to the worksheet.

kerry_floyd
Community Contributor

Thank you so much for making this! It's an incredibly helpful tool. It used to work for me just fine, but now it isn't. I haven't changed anything in my settings (I don't think) but now I'm getting an error message that says "there's no course associated with that course id." This is for a course that I've run the report on before, and it does it for new courses as well. I'm an admin, so I don't think it's a permissions issue. Is anyone else having this problem or have any solutions? Thank you!! Screen Shot 2016-11-03 at 4.29.58 PM.png

stelpstra
Community Champion

Hi  @kerry_floyd ​, on our instance it works without any problems.

Did you get any messages before this, e.g. "Unautorized" or "Not found"?

Could it be that the course has been deleted or archived to a sub-account in which you are not a admin?

nipp4277
Community Novice

Hi, I'm getting the same error. I tried going into the VBA Editor to look at the code, but it seems to be password protected. Is there anyway I can get an unlocked version or the password to troubleshoot. I feel this a great tool and would like to use it. Thank you!

kerry_floyd
Community Contributor

I just tried it on my PC and it worked just fine, so I'm guessing it's an issue with the Mac version of Excel that I'm using. Thanks for taking the time to reply!

stelpstra
Community Champion

Hi  @kerry_floyd ​, it wil not work in Excel version 2016 for Mac, because Microsoft discontinued the macro functionality (VBA) for the greater part in version 2016 for OS X. On your Mac it will only work using Excel version 2011. Additionally, are you using the latest version (1.9) of the Excel sheet (you can see the version on the Settings dialog)? If you are using Excel 2011, sheet version 1.9 and still having this problem, please let me know.

stelpstra
Community Champion

Hi nipp4277​, are you using Excel for Mac version 2011 (see compatibility in the description) and the latest version (1.9) of the Excel sheet?

kerry_floyd
Community Contributor

Yeah, the problem was the Mac version of Excel, so it's all working again. Thanks!

salo
Community Novice

Jaap, thank you so, so much -- this is absolutely brilliant and it just saved this first-time Canvas user an unbelievable amount of time (140-person class).

For the record, I'm a Mac user and Excel 2011 on Sierra handled the spreadsheet just fine.

Not an Excel whiz, so there's probably an easy way to do this that I'm not figuring out: how to filter by students whose completion percentage (column C) is less than 100%?

stelpstra
Community Champion

Hi  @salo ​, thanks Smiley Happy

A filter like you suggest is possible with a few steps.

It won't work without selecting the content, because Excel will assume the incorrect range.

To do this, you have to select the range starting from cell C6 (% completed) down to the last cell containing data, like this (illustration using Windows):

212166_pastedImage_2.png

In the Data tab, enable Filter:

212171_pastedImage_4.png

Click on the filter options in the cell and select Number Filters -> Less Than...

212172_pastedImage_5.png

Then enter '1' as value and click OK:

212173_pastedImage_6.png

I hope this is helpful to you.

jmayes
Community Novice

This is such an amazing resource! It provides a great visual for tracking the completion percentages for our various training courses.

In the past week or so, I have been having trouble with the file downloading the information to generate the report. It will get to about 4% and freeze, then give me an automation error. Do you know what may be causing this? I am on a PC, using version 1.9, and I tried using a new API token. I cannot seem to figure it out. Any suggestions?

stelpstra
Community Champion

Hi  @jmayes ​, I've tested it a moment ago and it worked fine. At 4% the list of students has been downloaded (so your token is correct), will be populated in the sheet and it is about to start downloading and populating progress data of each student. To view module progression, View all grades must be enabled (see page 19 and 20 from https://s3.amazonaws.com/tr-learncanvas/docs/Canvas_Permissions_Account.pdf). If this is not enabled for your Admin role in (the sub-)account the course is in, this would explain the error message.

jmayes
Community Novice

It turns out that, while on a PC, the Excel 2016 version was causing the problems for me. We went back to using Excel 2013 and it works just fine. Thank you for helping me troubleshoot this issue. I am so happy to be able to use this file again!

stelpstra
Community Champion

Does anyone else using Excel 2016 experience the same problem? As I don't have Excel 2016 and don't know people who do have it, I'm not able to reproduce or troubleshoot it.

f000f2p
Community Novice

I am using the Macro in Excel2016. I had run into similar error: The reports frozen and returned an error after it progressed to 30%. But I don't think the error I had experienced is related to Excel 2016. I looked through individual student where the report stopped, and found out that the user ID of the student contained obscure digits (something like '10000028' '1.00E+7', which caused the error. After I removed the student from the course, the report ran successfully. It is probably worthwhile to find out where the report freeze.

MattHanes
Community Champion

Still working for me in Excel 2016.

stelpstra
Community Champion

Thanks for the example f000f2p‌. Do you mean 10000028' '1.00E+7' as two separate values or as one? And were did these numbers exist, in Canvas in the SIS ID field or did only the Excel sheet populate it this way incorrectly? But it remains difficult for me to test it because I don't have access to version 2016 of Excel.

f000f2p
Community Novice

It is the Canvas numeric id for an user, and it exists in USER api <- user[“id”], or course enrollment api course[“user_id”]

It shows up as proper digits, such as ‘10000028’, via GUI, but it is converted to a scientific number, such as ‘'1.00E+7'’ after download and open it in Excel2016. I confirmed it by using Canvas user API pulled the course user list, created a csv file and open the csv in Excel2016.

Thanks!

petern
Community Contributor

This looks amazing.  Connected and worked fine.  There is one little hiccup - all the values say completed (100%) even though they are not.  Ideas?

christopher_bar
Community Novice

Im having the same issue - all my items return a value of 100% complete ...even though they aren't! ...any advice?

stelpstra
Community Champion

Hi petern‌ and  @christopher_bar ‌, did you set any module requirements?

See How do I add requirements to a module?

I hope this is helpful to you.

stelpstra
Community Champion

In the code the numberformat for column A and B is set to text. Trying to replicate your situation (although in Excel 2010), I changed the SIS ID of one of my test accounts to 10000028 and the SIS ID is displayed correctly as text:

219020_pastedImage_1.png

If Excel 2016 is operating differently, then you should check if column A and B have the numberformat of the type text.

I hope this is helpful.

stelpstra
Community Champion

I have built a similar report you also may be interested in: https://community.canvaslms.com/docs/DOC-11862 

aeverhart
Community Explorer

How do I access settings?