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) 

[MattHanes]

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]

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]

You, sir, are a rockstar!

[dhulsey]

 @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]

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]

Thank you!

[bfelix]

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]

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]

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]

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]

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

[bfelix]

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]

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:

[floresr]

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]

 @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]

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]

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]

Hi Ethan,

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

Version 1.8 is attached.

Thanks,

Jaap

[stelpstra]

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

[60980756]

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]

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]

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]

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]

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]

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]

 @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]

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]

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

[stelpstra]

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]

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]

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]

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]

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]

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

[salo]

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]

Hi  @salo ​, thanks

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

In the Data tab, enable Filter:

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

Then enter '1' as value and click OK:

I hope this is helpful to you.

[jmayes]

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]

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]

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]

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]

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]

Still working for me in Excel 2016.

[stelpstra]

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]

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]

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]

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

[stelpstra]

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]

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:

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]

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

[aeverhart]

How do I access settings?