How to list teachers who have published/unpublished courses

James · ‎09-17-2015

Synopsis

This document will show you how to use Microsoft Excel and the Canvas Provisioning Report to generate a list of all courses with their teachers that are Unpublished (or Published). Three solutions are presented: a recommended approach using Excel lookup functions, one that uses the new Power View from Microsoft Excel 2013, and a Google Sheet application that isn't as powerful, but easier to use.

Introduction

This document arose from a question Published or Unpublished Course Reports . Deactivated user went through and did most of the background work and wrote up some instructions using VLOOKUP() commands in Excel. I've done similar things in the past with other projects and it's always kind of a pain since linking multiple tables together isn't native to Excel. If I was doing this myself, I would load the data into a relational database and then issue SQL statements to get the reports I wanted. But Jordan asked me to prepare "the most efficient way to get a list of teachers with (un)published courses." Most efficient for me is not the same as easy for the masses, so I walked the line between the two and this document is the result.

I actually ended up with two ways. One that works pretty much all the time, but requires people to enter formulas (only 2) and will only give one instructor per course. The alternative method doesn't require any formula entry and it returns all the instructors in a course, but it won't work if there are duplicate logins for the same user and it doesn't export or print very well.

At this point, I'm recommending the first method, which I'll call Lookup Functions.

Method 1: Using Lookup Functions with Excel

I've been doing lookups in Excel with VLOOKUP() and HLOOKUP() for a while (decades). I've recently come to appreciate the power of MATCH() and INDEX(), which provide more functionality than VLOOKUP(). MATCH() returns the row that matches, while VLOOKUP() returns the value that matches. Both require that the value be contained in the first column, but with VLOOKUP() it literally has to be the first column since the range is only specified once. WIth MATCH() you can make it lookup the data in a different column and use the row number with the INDEX() command to pull information that comes from a previous column. For example, you could lookup the login_id from column 7 and then get the canvas_user_id from column 3 with a MATCH()/INDEX() combination, but you can't with VLOOKUP().

Does your head hurt yet? Well, that's why I was hesitant to push this method on people. My formulas involving VLOOKUP() required that the columns be rearranged and sorted and then I checked to make sure the value looked up matched what it was supposed to. I was encouraged by the alternative method and it uses the new form, which Microsoft says is so much better than the lookup functions. Well, they're wrong, it's not. It just turns out that I had been using Excel, but not fully understanding the power of Excel, for all these years. And this document will show you how easy it is to accomplish this.

The downside to this is that you do have to enter two formulas. You don't have to copy/paste them down a column, but you do have to enter them. Luckily, you can copy/paste them from this document, so it's not so bad. Also, if you happen to have multiple instructors, this will only return one, probably the first one found.

Lookup Function Instructions

Generate a provisioning report. Go to Account > Settings > Reports and choose Provisioning Report. You need to include the Courses, Enrollments, and Users. Feel free to limit the term to suit your needs.
Download the provisioning report and extract it somewhere on your machine. You should get courses.csv, enrollments.csv, and users.csv files.
Start a new Excel worksheet and copy each of the .csv files into a different sheet of the master spreadsheet.
Format each report as a table by going to Home > Format as Table. The style doesn't matter, but formatting it as a table does. You can also go to Insert > Table to convert the data into a table. Be sure to name the table courses, enrollments, or users so you can refer to them later.
Go to the users table and make a note of the column numbers you would like to appear in the main report. The full_name is in column 6. The login_id is in column 7.
Go to the enrollments table.
1. Click in the next available column and add a header of user_row. That's column L in the current provisioning report, so this would be cell L1
2. Right below that, in cell L2, type the formula: =MATCH([@[canvas_user_id]],users[canvas_user_id],0)
Go to the courses table.
1. Click in the next available column and add a header of full_name. That's column L in the current provisioning report, so this would be cell L1
2. Right below that, in cell L2, type the formula: =INDEX(users,enrollments[user_row],6)
Now it's time to apply the filters to specify we just want teachers and unpublished courses.
1. Go to the enrollments table, click on the pull-down in the role column header. Choose Select all to turn off everything and then check teacher.
2. Go to the courses table, click on the pull-down in the status column header. Uncheck active to get just the unpublished.

This is now a regular spreadsheet, so you can copy, save, print, export, etc. You can also add additional columns to the right of the courses table. You can hide columns to get just the parts you need.

Lookup Function Video Demonstration

This video walk-through starts with step 2.

Lookup Function Notes

In hindsight, this is the simpler and more powerful approach. I shouldn't have listened to Microsoft.
You are required to enter two formulas, but then Excel will automatically apply them to the entire table for you.
If there are multiple instructors, you'll only get one of them.
This technique should work in older versions of Microsoft Excel (and probably Google Docs as well), but I don't have older versions available to test it.
You are able to actually use the results of this method to do other things, unlike the Power View method.

Method 2: Using Excel's Power View

When I originally wrote this document, I thought I had found an easy way. There was no typing of functions involved at all, the only problem was that you had to have Microsoft Excel 2013 or later.

The solution here not to use VLOOKUP() at all. Neither should you use the more powerful MATCH() paired with INDEX() functions. In Excel 2013, there is now a Data Model that we can use to define the relationships between the tables. It's so powerful (in Microsoft's mind) that they have declared VLOOKUP() is obsolete. Of course, VLOOKUP() isn't dying anytime soon because their Data Models isn't as easy, powerful, or useful as they think it is. But in the case of finding courses and their teachers, it is up to the job and a lot easier than multiple VLOOKUP() functions.

There is a trade-off though. The Power View Reports don't export or print nicely from within Excel. They were designed for interactive, one-screen reporting, and provide a scroll-bar if the report goes more than one page. You can share with SharePoint and some other things, but I don't have those to play around with. So is it worth it? For power users, it's probably easier to use a database or the lookup commands. But for the user who knows a little Excel but isn't an expert, this technique works without entering a single formula or doing any programming.

When exposed to real-life testing, the method barfed. It turns out that the canvas_user_id isn't always unique. If there are two logins with the same account, then it's not and that causes the Data Model to fail and the report won't run.

However, for well-behaved data sets, there is a lot of potential with the Power View Reports and I wanted to document how it might be used.

Power View Instructions

Generate a provisioning report. Go to Account > Settings > Reports and choose Provisioning Report. You need to include the Courses, Enrollments, and Users. Feel free to limit the term to suit your needs.
Download the provisioning report and extract it somewhere on your machine. You should get courses.csv, enrollments.csv, and users.csv files.
Start a new Excel worksheet and copy each of the .csv files into a different sheet of the master spreadsheet.
Format each report as a table by going to Home > Format as Table. The style doesn't matter, but formatting it as a table does. You can also go to Insert > Table to convert the data into a table. Be sure to name the table courses, enrollments, or users so you can refer to them later.
Define the relationships between the tables. To do this, go to Data and choose Relationships. Click New.
1. Connect the courses table to the enrollments table using the canvas_course_id as the key for both.
2. Connect the enrollments table to the users table using the canvas_user_id as the key for both.
Save this file and everything else on the computer. Possibly even shut everything else down. The next step involves adding a plug-in into Excel and sometimes those freeze the computer so that you need to do a hard reset (hold down the power button for several seconds to come back). I had to do that three times while making the video, but it may have been the video capture software conflicting with the plug-in, but better safe than sorry. You were warned!
Go to Insert and choose Power Report. This comes with Microsoft Excel 2013, but it isn't installed by default. The first time you run it, it will ask you to enable the plugin.
Wait for the Power Report add-in to be installed and the screen to come up.
On the right side, there is a field list.
1. Click on courses and choose the fields you want to appear in the report. Probably the course_id, which is your SIS course ID and maybe the short name of the course.
2. Scroll down to Users (skip enrollments for now) and click on full_name and possibly login_id (email address or NetID).
3. Now scroll up to courses and drag status to the Filters panel on the right side of the report (left of the field list). Select unpublished as the report.
4. Scroll down to Enrollments and drag role to the Filters panel. Select teacher as the role.

Power View Video Demonstration

This video walk-through starts with step 2.

Power View Notes

No programming is required. No formulas need entered.
This report lists all teachers when there are multiple ones. The VLOOKUP() method would have only found one instructor.
You can select published courses if you like by changing the filter. The choice is "active" instead of "published".
You can add other fields as necessary. You can extend this technique to other tables and data sets as well.
Getting your data out of a Power View isn't easy. In the video walk-through, I said you can print, save to a PDF, or share, but upon further research, it seems that the Power View reports are designed for on-screen, interactive usage. Microsoft's take is kind of "Why would you want to print it?" See Power View: Explore, visualize, and present your data for additional information.
You will probably want to set this up once and then just refresh your data sources when you do new provisioning reports.
If you decide this isn't for you or you experience crashes and want to disable the Power View and Power Pivot plug-ins in Excel, then go to File > Options > Add-ins. Choose Manage Com Add-ins and disable them.

Method 3: Google Sheet Application

Consider this the "Easy" button approach. The other is still good to know and it's more powerful, but I wrote a Google Sheet application that will use an API call to provide similar functionality. When you open the spreadsheet, there will be a new menu item added to the spreadsheet called Canvas. Everything is done from that menu.

Log into Google Drive and then open the Unpublished Courses spreadsheet.
Choose File > Make a copy from the menu. From now on, work with your copy.
Choose Configure API Settings from the Canvas menu. You'll need to authorize Google to access your Canvas data. You then supply your Canvas instance and an access token.
Choose Select Term from the Canvas menu and then pick from current terms, future terms, past terms, and undated terms. This was an effort to make it more manageable for institutions with lots of terms. Undated terms are terms that are missing either the beginning date or ending date or both; if you have specified an ending date in the distant future (like January 19, 2038, or December 31, 2999), then it has a date and will show up under current or future terms instead. The program will not run for multiple terms. If you want to do this, then just run it multiple times and specify a different term each time.
Choose List Unpublished Courses from the Canvas menu. There are two varieties available that deal with how courses with multiple instructors are listed. If you choose combined, then each course will get a single row in the spreadsheet and the multiple instructors will be combined into a single, comma separated list. If you choose separate, then each instructor will be listed on their own line, which makes sorting by instructor easier but it leads to duplicate course entries.

Now wait while it does its magic.

If you happen to be an admin for more than one account, then the system will allow you to Select Account so that you are managing the correct one.

Publish Courses

As a bonus, this application will also allow you to bulk publish your courses. Be careful and make sure that your faculty are on board with you doing that, otherwise be prepared to deal with the fallout.

The way this works is that once you have downloaded the list of unpublished courses, you delete the ID from the first column of any course you do not want to publish. Alternatively, you may put a 0 for the ID or delete the entire row from the spreadsheet.

Once you've narrowed down the list of courses to publish, then choose Publish Courses (caution!) from the Canvas menu.

Note that this method isn't as powerful as the provisioning reports listed above. It just gives you the name of the instructor, but not their netid, email, or other login information so that you could contact them. But it does give you the ability to bulk publish courses, which wasn't available using the Excel methods.

January 29, 2017 Update

Terms are no longer cached by the script. I had originally cached them as a speed issue since Canvas doesn't allow you to fetch information for a single term, it only returns a list of all enrollment terms and then you have to find the ones that you want. The menus are asynchronous, so I was having to load the list of enrollment terms twice -- once when generating the menu and again when processing the menu. However, when schools had a lot of terms (the school that helped track down the issue experienced the problem going from 42 terms to 43 terms), the list would be too long to store in the properties. The code to delete the cache if it exists is still there, just in case someone copies the code over to the old script rather than starting over. Because items are no longer cached, it may take another half-second or so for things to happen or the list of terms to appear.

I've also improved the error handling and provided some additional messages about what is happening so the user is informed. Previously, choosing a term did nothing visible, so people may have wonder if it did anything. Now it provides a message that it selected the single term or that the user should proceed to listing of the courses after selecting the term. The help file has also been updated.

August 23, 2017 Update

The script has been updated based on feedback from @mjennings ‌ so that it works with sub-account admins. The sub-account admins may not be able to get a list of terms, so I have it switch to showing all terms if you try any of the other terms. You can avoid this extra step by choosing "all terms" to the menu.

The previous version showed just one term, so it wasn't necessary to show which term it was as they were all the same. Now that there is the ability to show all terms, I've added the term name to the list of courses.

I've also added the number of students enrolled in the course so that you don't have to stress out over those courses that have no students in them. As a final tweak, I've adjusted the start and end dates to use the timezone specified under Google Sheets' File > Spreadsheet Settings option. This means that you won't have to deal with converting the time from UTC.

You're signed out