How to list teachers who have published/unpublished courses

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.

https://www.youtube.com/watch?v=fvWbluvuTsg

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.

Find more posts tagged with

Admin

Comments

szabarovska

I have been using the Google Sheet Application for two years; however, I attempted to run the script today and I received the following errors:

Can someone advise?

James

@szabarovska ,

Based off the screen-shots, the error appears to be related to there being no data. It selects the current region, but since there is no data, that defaults to the entire page, which is too large to handle. There are some other issues people are experiencing with the Google Sheets, but when I test things, they work and we haven't been able to nail down anything yet.

However, when I look at the code, the only place sort is called is when it's working with the list of terms. It's possible that the API call to fetch the list of terms failed, which might happen if the API credentials hadn't been entered or it might just sporadically happen. I have an entry that sends information to Canvas every 20 minutes about enrollments and 8:25 this morning, it decided that it couldn't find our Canvas hostname, but then it started working again without me changing anything -- perhaps a temporary DNS glitch.

Here are some things to try (I'm just throwing out general ideas in the hope that one works).

Try it again. I thought I was onto something and getting errors and nothing was generated. But then I questioned whether or not I had really clicked what I thought I had and when I went to do it again, it worked. There was also the DNS issue I mentioned earlier.
Use Chrome.
Get a fresh copy of the spreadsheet, especially if you've been copying the same one from semester to semester. I wrote it 1.3 years ago and changed it 1 year ago.
Make sure you've configured the API settings before you do anything else. If it doesn't show success on the connection, other things won't work.
Follow the instructions: First configure API, then select term, then choose List courses.
When an error occurs, click on "Tools > Script Editor" from Google Sheets. Then, from the scripts page with a bunch of computer code, look at "View > Execution transcript" and "View > Logs". The Logs are error messages I've generated by checking for certain conditions. The transcript is a trace of what calls I've made to Google functions and the bottom will give a hint as to what it was doing before it stopped.

szabarovska

Can you share the link of the newest file?

Sabine Zabarovska

Northeast Mississippi Community College

Learning Management Systems Support Specialist

Canvas Support

James

Upon further digging, there is no newer version. Jan 12, 2016 was when I added the Google Sheet in the first place, so it's only been there for 1 year. I saw the changes to the document and thought I had updated the sheet, but I had only updated the document to add the sheet. The link in the document to the Google Sheet is the current one.

Sorry for the confusion.

In the meantime, there may be another issue if you've been copying the same sheet or reusing it from semester to semester. The list of terms is cached and if new terms were added since it was initially ran, that might be an issue. Starting over with a fresh sheet would force it to reinitialize the cached list of terms.

szabarovska

Thanks, James!

I tried using Google Chrome and the same issue exists.

I am using the following form:

/**

@fileoverview This Google Sheets script will provide Canvas API functionality for other scripts.
@author james@richland.edu
@license Copyright 2015 Standard ISC License
@OnlyCurrentDoc

Do I have to use a test, beta, or production instance?

Also, I have created a new token and I'm still not getting anywhere.

Thanks for helping me on this!

Sabine Zabarovska

Northeast Mississippi Community College

Learning Management Systems Support Specialist

Canvas Support

662.720.7194

James

@szabarovska , I'm reaching out to you via email so we don't fill up this space as we try stuff.

Greg

Thanks @James for posting this and especially for the video. Instead of having instructors request a course as we did with our prior LMS we create Canvas courses for every class in our college. This cuts down on a bit of lag (the instructors had to wait up to a day to get an official course offering), but it made it easier to track who was interested in the LMS because they had made a request. With Canvas I instead search for published courses. Since this is our first semester I want to reach out to those instructors using Canvas, so I need to combine the three tables to find that list. I searched within the community and voila! found your document.

James

I'm glad you found it useful.

szabarovska

I am receiving an "Undeliverable message" when I try to respond to your e-mail because of the size limit of server...I am trying to send a video of my steps....advice? Smiley Sad

James

Don't send the video by email, put it somewhere, like a dropbox or upload it to youtube as unlisted video and then share the link.

Boekenoogen

We are experiencing the same issues as Sabine Zabarovska . Can you post the videos that you send her to get this to work...

Dr. John Boekenoogen

University of Oklahoma

James

Sorry, I'm not sending her any videos, it was the other way around. I haven't gotten feedback from her for a couple of days after I asked some questions, so if someone else would like to help out with the debugging, I'd appreciate it -- email me at the address you get when you mouse over my name.

We have established that she is getting the Success! screen but that it's bombing out before it pulls up the list of terms. So I'm not sure if that's because the school isn't using terms (everything is in the default term) or because there are multiple accounts, in which case you might need to choose "Select account" before you try to grab terms. Unfortunately for debugging, I can't test either of those situations with our school's installation.

So, if you want to help debug, do the configure API, then do the Select Term thing. Then go to Tools > Script Editor and choose View > Execution Transcript on the window that opens. Copy the execution transcript and remove your access token (just the token) and send it to me. That will hopefully give me some clue of what's happening. The other thing to test is to take the URL that's in the UrlFetchApp.fetch() line and paste it into a web-browser and see what it comes up with (whether or not it gives any results).

It's possible that a school is using terms without dates and so it's trying to sort on nothing and having a problem doing it. I'd like confirmation before I pursue that route though.

If you're having trouble getting to the Success! screen, then you can watch the setup video at Adjust All Assignment Dates on One Page as, it starts off the same way.

James

I made some tweaks to the code to make sure that a list of terms was actually returned before it tried it to use it. That should silence the errors (or change them), but it doesn't mean that it will work since it needs a term to work. I also modified the sorting function to make sure that there was a value for a date before I tried to use it, but I don't think that was the issue. It's saying it can't call sort on something that's undefined, and that was the line that tried to sort the term list, but it was the list of terms itself that was undefined instead of being an array.

If this was working for people, you don't have to update to the latest version. For those having problems, it might help identify where the problem is, but I don't think it's fixed.

James

@szabarovska sent me the information I needed to track down the problem. She's hitting a space between 42 and 43 terms where converting all the information Canvas sends through the API is bigger than what can be stored in the UserProperties for the caching. If I save just the information I actually use, it saves about 44% of the storage and so someone should be able to have up to 95 terms. I need to write some code to check to see if the dates of the course are nearby and only list those in the menu and that should future-proof it unless someone has more than 95 terms within (say) a 1 year period. I hope to get that change made and tested and I'll post a note when the code has been updated.

@jrboek , does this sound like it might be what you're experiencing -- that OU has a large number of terms in your system?

Boekenoogen

Yes we have a lot of terms.

John

James

@szabarovska ‌ and @jrboek ‌

I've updated the spreadsheet to address the issue and so now it should work again.

The URL is the same, so you can make a new copy of the spreadsheet or you can copy the unpublishedCourses.gs and help.html files from the current version over your existing one.

I'm no longer caching the results returned, so it may take a little longer (we're talking fractions of a second) for schools with large number of terms. I've tried to make the list more manageable by breaking it into current, future, past, and undated (missing a start or end date) terms. I've also improved messages so when something happens (or doesn't happen), the user has an idea what happened.

The issue that was that I was trying to cache the results returned by Canvas so that I didn't have to make the call twice. However, once you got above about 42 terms (depending on the length of names), the results got longer than Google would store in the user properties. By not storing the results but making the call twice, the problem of "argument too large" is eliminated.

szabarovska

James, thank you for taking time out to resolve this issue. You are truly appreciated.

It is working!

Sabine Zabarovska

Boekenoogen

Still having issues. I am trying to see if one of my programmers can help.

Dr. John Boekenoogen
University of Oklahoma

James

@jrboek ‌,

Is there a different error this time? The issue of "too large" shouldn't be an issue because I'm no longer trying to save the data, which was causing the error. If you're still getting that error, make sure you go back and make a fresh copy of the spreadsheet.

If you're not sure if you got the update, the new version has an extra submenu when choosing the term.

If your programmers track down the issue and it's something I can fix (I'm probably not going to fix the proxy server issues), have them reach out to me with the solution. If you want me to look at the problem, I'll need someone willing to shoot me information and test stuff to see what works. What finally worked for Sabine was sending me a list of the enrollment terms that Canvas was returning. That's how I was able to tell that she was getting too many responses and I actually hard-coded her term list into my test code until I got it working.

You can get my contact information by mousing over my name here in the Community.

unknown

Hi all,

Thanks to James for documenting this. I took a stab at option 1, and for some reason the "courses" tab wasn't pulling in the correct name. When I moved the index formula to the enrollments tab, in a new column next to user_row, that did the trick.

We're using this report at my institution to check for TA enrollments across our current term's courses. I can't tell when the formula didn't work for me on the "courses" tab, but just wanted to say what worked for me in case anyone else came across that.

sbeck1

@James ‌, I have my list of unpublished courses via the VLOOKUP model. We are a Microsoft campus, so we do not have the Google integration. Could you tell me how the Google list is bulk publishing the courses?

sbeck1

We found where it can be changed in the APIs. The verbiage threw us off.

Thank you for all the helpful information you continue to make available for Canvas users.

James

@sbeck1 ,

I see you already discovered the answer while I was in class teaching (I love fixing problems without doing anything), but I wanted to clarify that this is not a Google integration in the normal sense of adding Google Drive or the Google LTI. This can be used by anyone with a Google or Gmail Account whose school doesn't have obstacles to jump through (like not allowing tokens or using a proxy server).

bfowler

Hi All,

Thanks again to James for all his contributions.

I am running Option 3-- but I would like to see our published courses and their faculty.

Is that a possibility, did I just miss an instruction?

Best,

Brady Fowler

James

@bfowler ‌,

The code in option 3 can be edited to return just the published courses. I'd make a copy and called it "Published Courses" instead of "Unpublished Courses". Then make these one-time changes.

Open it up in Google Sheets.
Click on Tools > Script Editor
Click on Unpublished Courses on the list of code modules on the left side
Scroll down to about line 307.
Change 'published' : false, to 'published' : true,

Then it will give you a list of just the published courses.

It would be possible to add another column that showed the published status as a 1 or 0, but the big drive for this was finding unpublished courses and that's not as quick of a change (not difficult, just not as quick).

bfowler

Hi James,

Thanks for the reply! I made the changes and got the same data as the original unpublished courses sheet.

I am new to Google scripts.

copied and renamed the sheet,
made the line 308 change in the file called unpublished courses.gs
saved it, and then ..I think..ran it.

Is there a publish option I should be using to bring the script revision to life?

I apologize if this is a beginner's error, but hey, I'm a beginner ...

James

You should be able to follow the same steps as above for the unpublished list. The menu will still say unpublished (unless you change that portion of code as well).

bfowler

HI James,

Thank you for the support, it seems to be working now, and very nice to see it pulling straight from the live data.

Here's how I ended up doing it:

make the line 307 edit in the unpublished courses.gs file
use the "save all" command in the script editor
reconfigure the API name and token
select term
select list unpublished courses

A real treat to see this script in action.

I spent some of yesterday working out on Option 1, and the MATCH/INDEX tools look powerful too.

I am running a late version of excel and had to hack away in the function builder interface to get the commands to work; notably it didnt accept my named sheets, needed me to specify Table1 etc.
Is there a way to get the full_name INDEX column to operate as a sorting key in the courses sheet?

Regards from Olympia WA

James

@bfowler ,

If you select and copy the data in the main table, then go somewhere else (like another sheet*) and do a Paste Special as Values, you can sort it by any of the fields in the table using Data > Sort. You might want to convert that to a table so you can easily filter or sort by clicking on the header at the top of the column.

*You can paste it in the same sheet, but be sure to leave a blank column between tables or they'll get joined together and you don't want that.

MAJ18

@James ,

Thanks for this! I am running the Option 3 and testing the Publish Courses (caution!) option in our beta environment. I am selecting te account, but no matter which term I select, they are all returning the following messages:

and then,

Is this because it is the Beta environment or am I doing something else that is causing the issue?

Thanks in advance!