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.
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.
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.
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.
This video walk-through starts with step 2.
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.
This video walk-through starts with step 2.
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.
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.
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.
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.
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 must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
I'm James Jones. My full-time job is teaching mathematics at a community college, with an extra-duty assignment that involves Canvas. I hate repetitive tasks and will spend 13 hours writing a computer program to automate something that takes 5 minutes to do. This often benefits others in the form of Canvancements, which are my Canvas Enhancments that I freely contribute to the Canvas Community.