How to list teachers who have published/unpublished courses

[James]

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

1. 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.
2. Download the provisioning report and extract it somewhere on your machine. You should get courses.csv, enrollments.csv, and users.csv files.
3. Start a new Excel worksheet and copy each of the .csv files into a different sheet of the master spreadsheet.
4. 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.
5. 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.
6. 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)
7. 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)
8. 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

1. 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.
2. Download the provisioning report and extract it somewhere on your machine. You should get courses.csv, enrollments.csv, and users.csv files.
3. Start a new Excel worksheet and copy each of the .csv files into a different sheet of the master spreadsheet.
4. 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.
5. 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.
6. 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!
7. 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.
8. Wait for the Power Report add-in to be installed and the screen to come up.
9. 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.

1. Log into Google Drive and then open the Unpublished Courses spreadsheet.
2. Choose File > Make a copy from the menu. From now on, work with your copy.
3. 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.
4. 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.
5. 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.

[James]

I wrote a post the other day about using Q to provide SQL capability on CSV files. It allows people to relatively easily combine files and extract the columns you need. I need to update this blog post to include that as an alternative.

[mzimmerman]

Cool!  I'm not familiar with q, but I'd guess that it will run on Mac, which will make it usable on my desktop machine...

[James]

Yes, they had stand-alone installation packages for the major operating systems. OSX was listed on the installation page.

I knew that stuff out there allowed SQL to work directly on CSV files, but didn't know the best (or any of them). I don't know that Q is the best, but the other one I looked at wanted to install a bunch of dependencies while Q was one package. If you know SQL, it was just a matter of using the filename.csv instead of the table name. If someone don't know SQL, they could take the example I gave and quickly modify it.

[mzimmerman]

Well, I'm definitely more comfortable doing SQL than Excel queries, so I'll give Q a try.  Thanks for pointing it out!

[mrocco]

Thank you! This was incredibly helpful.

[mrocco]

Thank you! This was a huge help.

[jeneen_hill]

Hi- @James - I am looking at an old post, and wanting to try to clean up old Canvas courses that were never used, thought I could try this idea? My question - in step 3- "Configure API settings"

where do I find this? TIA, Jeneen Hill

[James]

@jeneen_hill 

Step 3: from the Canvas menu. When you make your own copy, it should ask for permissions to do things. If you grant those and all goes well, there should be a "Canvas" menu item (File, Edit, View, ... Canvas).

[jeneen_hill]

Thank you so much! Working on it now. Jeneen

[jeneen_hill]

Hi- @James 

Getting this error code? Any help would be appreciated 

[James]

@jeneen_hill 

That error occurs because it was expecting something and didn't find it. There are lots of places in the code where there is a .length, so that message by itself isn't enough to help me figure out what went wrong.

I would start by making sure that the API token that you created and entered into Canvas has admin rights. If you used a regular non-admin account, then it will not work.

Otherwise, letting me know exactly the steps you took to get to that message might help me narrow down cause.

You may also be able to go the Tools > Script Editor and then click on the Execution Log button at the top to get an idea of where it crashed.

[jeneen_hill]

Hi- @James 

I am an admin for our account. 

Our instance is: https://natronaschools.instructure.com/

Do I include what is underlined? for the Canvas hostname?

I don't think the google sheet is recognizing our instance? - Should natronaschools be somewhere in the script? 

Thanks so much, 

Jeneen

[James]

@jeneen_hill 

Your Canvas instance should not be part of the script. After loading the Google Sheet, you have to go to the Canvas menu and choose Configure API Settings. At that point, it will ask for your site and you can copy/paste the full URL or just type natronschools (since you're using instructure.com).

I believe it will come back with a confirmation message if it is able to connect using the API. If you do not get that confirmation message, then nothing else will work. If you are not putting in an access token and specifying the instance in the Configure API Settings, then nothing will work.

In some of my scripts, I automatically redirect people to the configure menu if they try the others first, but I cannot remember if I did that here.

[jeneen_hill]

HI @James 

I got this to work, thank you so much for your help. I just tried again today and it worked, I didn't do anything different?

But... How could I best use this to clean up old/unused courses in Canvas?

Thanks,

Jeneen

[James]

@jeneen_hill 

If your courses were created using a SIS import, then I would use the list to create a courses.csv file with a deleted status and then import it.

If you created them some other way and don't have SIS IDs available, it will be a little harder. You can go through the list manually from the Admin > Courses list and delete them or you can create a script that will call the API to delete them.

[BrandiGranett]

Hi, @James 

I was using your magical spreadsheet tonight, and when I tried to download a fresh copy the Canvas menu item was missing. Did something go awry?

--Brandi

[James]

Brandi ( @BrandiGranett )

Hmm. I've had some people report that with other scripts in the past. Then they come back later and say they just made another copy and it worked and I never figured out why.

I just downloaded a fresh copy using a second Gmail address. It took a bit (maybe 15 seconds), but the Canvas menu came up for me. When I tried to do the Configure API, it requested Authorization.

Some people are not able to use my scripts with a school account. Their institution has locked things down so that they cannot access content from other domains or run scripts for security purposes. I'm not sure exactly what they have set that blocks it, but I have encountered it. I think the determined people there used a non-school issued account to get it to work.

[BrandiGranett]

@James Thanks! You really saved me so much time today. 

--B

[James]

@BrandiGranett 

Can you share how you fixed it in case other people have the same issue?

[BrandiGranett]

@James I used the copy I had already made again--I deleted the values and then started over. I am still not able to see the Canvas menu if I try to download a fresh copy.

[gabriel_martine]

@James thank you so much for putting this together.  I've used the Google Sheets for a few years now, and today it didn't work.  So I made a new copy of the most recent Sheet, I configured the API settings per the instructions, and then I went to select the term.  (There is only one account in my Canvas instance.)  Then I got this error:

What am I doing wrong?

[James]

@gabriel_martine 

It looks like I expect something to return a result but it didn't and then I acted on it without checking to make sure that it did. 

Where exactly that happened can be difficult to track down. As you saw, there wasn't much in the way of debugging information. When I look at the code, The main code for this script has a check before almost every attempt to refer to length except perhaps those with publishing an unpublished course. Since you're saying it's happening on the list of terms, that's unlikely to be the issue.

I made a copy of the script and ran it and it worked for me, so it's hard for me to track down where the problem is.

I looked through the code for getting a list of terms and there is a check for an undefined result, but it's possible that it's defined but an error.

Are you sure that the token you used has admin rights? Getting a list of terms and the other calls require that permission.

Did you select the account even though there is only one? I don't know if that matters, but I have a check in my code that says you must do it first and throws an error if you don't.

Debugging is not impossible, but a bit more challenging. How skilled are you at looking at computer code and following directions? If you think you're up to the task, which option from the Select Terms menu did you pick?

[gabriel_martine]

Thank you, @James .  After configuring the API, I try "Select Account".  I get the same error message (length undefined) as before.

[James]

@gabriel_martine 

Does the Configure API routine come up with a success message that says you can now connect to <your Canvas instance>?

Getting an error there suggests that it's a problem in the api.gs script that's actually making the call, which could be an issue with a bad token or some other restrictions. I've had people tell me their school Google account is locked down and they cannot run external scripts, but it works using their home Gmail account.

[gabriel_martine]

I'll keep trying.  I'm using my home Gmail account, I am our Canvas admin, etc..  Thank you, g

[James]

@gabriel_martine 

I feel like I'm not able to help much when you respond to the comments instead of answering the question.

I normally ask a question and then provide the rationale behind the question. Saying that you're using your home Gmail and you are the Canvas admin doesn't answer the question about whether you got a success message when you configured the API routine. If you don't get a success message when you configure the API, nothing is going to work and the error about length is not the actual problem.

If you do get a success message, then the next step would be to know which choice under "Select Terms" you picked. They all end up in the same place, but with different parameters.

[gabriel_martine]

Hi @James sorry about that.  No, I am not getting a success message.

[James]

@gabriel_martine 

Until you get past the Configure API Settings menu with a success message, it won't work.

If you go back to Configure API Settings, it will remember what you typed in previously (at least it does for me when it works).

You should get a prompt that asks for your hostname and access token. What are you putting in as the Canvas Hostname.

When you click Submit, you should get a message that says "Success! Connected to <instance> as <user>. You may now use the API calls." with an OK.

If it cannot make the API call, then it will say "Failure. Unable to connect to <instance>."

Based on the results, there are two paths to follow.

If you're getting the Failure message, then what are you putting in for your hostname? I set it up so that you can copy/paste a link from your Canvas instance and it will figure it out.

If you're not getting a success or failure message or the screen isn't popping up at all, then we need to make sure that you agreed to all of the Google warnings about the script and developer not being signed.

When you start by going to Canvas > Configure API Settings, it comes up with an Authorization Required screen. Click Continue. It may ask you to Sign In to your Google Account. Then it says Google hasn't verified this app. Click on Advanced, scroll down and Go to Unpublished Courses (unsafe). On the next screen, scroll down and Allow access.

Then go back to Canvas > Configure API Settings because it didn't work the first time.

Now you should get the Canvas API Configuration screen mentioned above.

You may need to start off with a fresh copy of the spreadsheet if you have trouble finding those authorizations.

[gabriel_martine]

@James, thank you for these detailed instructions.  I get neither a success nor a failure message. 

I tried again, with a fresh copy.  Canvas API Configuration > Authorization Required dialog > Sign in to my Google account.  No error: I didn't have to do the Advanced or Unsafe steps.  Instead, I got this hopeful-looking screen, and hit Allow.

          

Then went back to Canvas API Configuration and entered    avemaria.instructure.com

I have tried https://avemaria.instructure.com/ and just  avemaria

and then enter the Access token, XXXX~XXX... and so on.  It's a fresh token with no end date.

Again I got neither a success nor a failure message, and clicking on Select Term > Current Terms got me a length: undefined error.

[James]

@gabriel_martine 

If we cannot get the success or failure message then the Select Terms is going to fail.

It's strange that it didn't make you do the advanced authorization, it makes me do that and I'm the person who created the script. Right after the Authorizaton Required, it asks to Choose an account, and then pops up a Google hasn't verified this app. That's the screen with a blue "Back to Safety" and an "Advanced" link. If I don't click Advanced and okay it, it makes me repeat that step.

Maybe it's something that Google updated for me since I have apps that I've been using. A year or two ago (might have been pre-covid), they went through this "we're going to disable apps that you haven't used for a while" because of security. I'm not sure what to do with this if it comes up missing, but go to your Account (avatar in top right of Chrome) and choose Manage your Google Account. Then click on Security. Scroll down to the Third-party apps with account access (it only shows 3 so you may need to click Manage third-party access). See if Unpublished Courses is listed. Mine was actually listed twice, once from my efforts yesterday and once from today. You can click on them and see when they were granted access. It should say it has access to Google Docs and to connect to an external services.

Now you might think that has nothing to do with this because it's a fresh install, and it might not, but it might be remembering the old installation and having issues because of that. Removing all of the Unpublished Courses (or at least the old ones) might reset things.

Before you do that, another possibility might be related to the the version of JavaScript you're using. When the script was written, Google was using version 1.3 of JavaScript and it didn't support a lot of the new features. They went through and announced they were upgrading to version 8. I checked and mine is running with version 8. This is a long shot and I don't think it's the reason, but try going to Extensions > Apps Script. Then click on the Gear (Project Settings) icon on the left. Under General settings, make sure "Enable Chrome V8 runtime" is enabled. I really don't think this is it, but I'm grasping for straws.

[gabriel_martine]

@James thank you, again, for the detailed look.

(1) I don't get a Back to Safety, etc., dialog.  It acts like everything is fine.

(2) Under Project Settings, Chrome V8 runtime was enabled.

(3) Unpublished Courses has access to Google Docs, to Connect to an external service, and to Display and run third-party web content in prompts and sidebars inside Google applications

I had four versions of Unpublished Courses (twice from the summer and twice from this week).  I Removed all but the latest one, tried the API again, and there was no success or fail message.

Then I removed all of the versions of Unpublished Courses, tried again (Authorization, Connect, etc).  Same behavior as above (no Back to Safety, no confirmation message, TypeError: Cannot read property 'length' of undefined)

--

A twist: I've been trying this in my Google account as an alumnus of a university.  This morning, I've switched to trying it in a regular Gmail account.

(1) the Back to Safety error now appears, and after clicking on the "unsafe" link, I allow access.

(2) There was no success or failure message after the Configure API dialog, and there was a length undefined error.

--

What is awesome about the Google Docs tool is that my faculty don't have a problem with me force-publishing their courses.  Publication is actually essential for a number of Student Support functions we do, so this had been invaluable.  This semester I will try a more manual approach.

[wchang]

Hi @James,

Thank you for sharing. I tried the Method 3 in our beta environment. It seems to me that all of our terms are under the Current Terms, because the unpublished course list I got contains the courses that are very old. And then, the script Exceeded maximum execution time. What can I do to resolve this?

--Wen

[James]

@wchang 

What you're bumping heads against is the hard timeout for scripts set by Google. There's not much that can be done to extend the runtime of the script, so you'll have to look for other things.

Your beta instance is going to be magnitudes slower than production, so switching to Production should get you more courses.

If you have a lot of really old courses that were not published, you might see if you could delete them. Make sure it isn't going to break anything, but since the courses aren't published, it's unlikely they're needed. Of course, that statement depends on your SIS integration. Will it break something to delete an unused course from five years ago?

If you cannot delete them, then you could create a term called "Really Old Courses that are Still Unpublished" and set an end date some time in the past on it. Then move the courses into that new term. I think that the "default term" doesn't have an ending date, so it counts as current.

The query that gets the courses could likely be rewritten to use GraphQL. I wrote it before Canvas had GraphQL, but if GraphQL can be used, you can fetch a lot of information quicker than with the regular API. For example, I was able to download the ID, state, and name of almost 13000 courses in just under 6 seconds using GraphQL. That would be magnitudes longer with the REST API where I can only fetch 100 at a time and need to space out the requests to keep from running against the Canvas rate limits. With GraphQL, you get about 30 seconds to execute a command and if it cannot be done in that time, it fails. When I added the request to get the teachers (there is a filter for that), it timed out. If I apply pagination, it took about 14 seconds per 1000 courses (your mileage may vary).

But if you rewrote that code, it would speed things up substantially as well. The problem with GraphQL is that you don't have some of the filtering capabilities that you do with the REST API so you have to filter it after you've downloaded it.

It would probably be faster to write cells in blocks rather than one at a time. I haven't looked at this in years, but if I'm appending one row at a time, that's a lot slower than writing multiple rows.

If you have a LOT of courses (which you likely do since it's timing out) and cannot narrow that list down, I would seriously consider using the provisioning report as described in method 1 or 2. If you're looking to publish the courses, you can likely take the results from method 1 and copy/paste them into the Google Sheet and then use that script to publish them.

[wchang]

@James 

Thank you very much for the thorough information. I will try the method 1 or 2. But before I do that, I wanted to try the Unpublished Courses report that's built-in in Canvas first. So I generated the report, copied the report, and pasted it into the Google sheet. Then, I ran the script. It worked!

I do have a question about the number of the lines. Is there a limit for the amount of courses that need to be published? If there is, what is the number?   

[kailey]

@James Thanks so much for this amazing resource! I'm attempting Method 1 and am able to get past Step 6. However, when I add the INDEX formula from step 7, I get #SPILL! errors all down the full_name column in the courses sheet. I compared the columns in each of my sheets with those in your video (had to delete a few columns since the reports now include more columns) to confirm they match since the video specifically mentions column 6 from the users table. When Googling, #SPILL! errors seem to be when a formula returns multiple results, and Excel cannot return the results to the grid. Any suggestions as to how I can work around this? Thanks again!

[James]

@kailey 

It looks like Excel has changed the format since 2015. When I open the file that I was using back then to write this blog, it added an extra set of brackets to the formula in line 7. I'm probably over simplifying, but a single bracket refers to the whole column (thus the spill error) while a double bracket refers to a specific value in the column

Try this (adjusting the 6 if it's not column 6 anymore).

=@INDEX(users,enrollments[@[user_row]],6)

Newer versions of Excel / Office 365 have an XLOOKUP() function that combines the MATCH and INDEX into a single command. I believe that was in Google Sheets when I tried it last week.

[kailey]

@James Thanks! The formula now pulls the instructor names into the courses sheet but the instructor names don't seem to be correctly placed next to the proper course. Is that expected? I looked back at your recording and when you sort your spreadsheet, it looks like the full_name column remains static while the rest of the sheet reorganizes based on how you tell it to sort. Any way to get the instructor names in the same row as the course they're teaching? 

[James]

@kailey 

Good catch. Obviously the name should change to be with the course and so something is broken  that I didn't catch when I made the original video. I guess no one mentioned it in the last 7 years because method 1 isn't that easy. I've had a lot more interest in the Google Sheet application I wrote to do it.

I may or may not be able to update the original blog post. The software we're using for the community has a limit on the number of characters you can have. It imported the content from the old community, but when I try to edit some of my previous blogs, it complains that I write too much. Either way, it's too much to look at tonight, so I'll just post the updated directions here.

It turns out that there may have been multiple issues going on. The biggest is that if you filter the data, it still returns the first match in the unfiltered data. The data I used was provided to me by an Instructure employee at the time that was clean and able to be used in a video without worrying about personally identifying information getting out. The problem was that I wasn't familiar with the data so glaring errors like a student showing up as a teacher didn't register with me. When I tried to duplicate this tonight with my institution's data, I saw that one of my calculus students was teaching economics and immediately realized something was wrong. He is a smart kid, but I'm not sure the accreditors would appreciate it.

Here's a quick fix if you have Office 365, which has the XLOOKUP function. The column names have been updated as of 2022-10-11, but the formulas no longer depend on what column number something is in. It refers to everything by name.

Method 1 Lookup Function Instructions

1. 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.
2. Download the provisioning report and extract it somewhere on your machine. You should get courses.csv, enrollments.csv, and users.csv files.
3. Start a new Excel worksheet and copy each of the .csv files into a different sheet of the master spreadsheet.
4. 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.
5. Go to the enrollments table. We are going to duplicate the name of the first teacher for the course onto this page. I will use the sortable_name as an example.
   1. Click in the next available column and add a header of sortable_name. That's column P in the current provisioning report, so this would be cell P1
   2. Right below that, in cell P2, type (or copy) this formula:
      =XLOOKUP([@[canvas_user_id]],users[canvas_user_id],users[sortable_name])
6. Go to the courses table. We are now going to duplicate the name of the first teacher for the course onto this page.
   1. Click in the next available column and add a header of sortable_name. That's column P in the current provisioning report, so this would be cell P1
   2. Right below that, in cell P2, type (or copy) this formula: 
      =XLOOKUP([@[canvas_course_id]],enrollments[canvas_course_id],enrollments[sortable_name],"")
7. Now it's time to limit the enrollments to just Teachers, otherwise Canvas may return the name of a student when you ask for the teacher. Filtering doesn't work as XLOOKUP ignores it. Instead, we need to delete all the rows that are not teachers.
   1. Go to the enrollments table and sort on the role column. Now delete all rows that are not teachers.
   2. Go to the courses table, click on the pull-down in the status column header. Uncheck active to get just the unpublished.

In step 6.2, the "" at the end is what it will show if it cannot find a value. This would happen if there was an course that had no teacher. The default value is #N/A, but I thought an empty response would be less abrasive. You could also say something like "No teacher listed". It's up to you.

This replaces everything beginning with step 5. If you don't want to use the sortable_name, you can pick any other column. What you name the column on the courses page does not have to be what column you picked. You could call it "name" instead of "sortable_name" if you like.

The part inside the formula does have to match the name in the table it's referring to. Whenever you use the brackets, as in users[sortable_name], that is looking for a column called sortable_name in the users table. If, in step 5, you named the new column on the enrollments page Name, then the formula in step 6 would refer to enrollments[Name].

If you don't have Office 365, the XLOOKUP command isn't available. I didn't have time (or desire) to repeat with MATCH and INDEX, the XLOOKUP makes it so much cleaner and easier to understand.

[kailey]

@James The updated instructions for Method 1 worked. Thanks so much!

My goal was to get a list of published courses with instructor information (rather than Unpublished courses) so I don't think the Google Sheets method would be able to get me what I needed. I was curious though, so I did attempt to Method 3 to see if I can get a list of unpublished courses. Unfortunately, I continued to run into quite a few errors so it didn't work out in my favor.

Anyway, thanks again for your wonderful blog. Very much appreciated!

[kailey]

@James I wanted to share that if you filter the enrollments file to only display rows where the role is "teacher" prior to copying the data to the master (rather than deleting all other roles later on), that saves a whole lot of time. I'm going to share updated steps below in case anyone in the community wants to use your first method. Also including the formulas to pull in email addresses for those who may be interested.

We wouldn't have been able to get this much needed info without your help. Very much appreciated.

--

1. Generate provisioning reports. 
• Admin > Account > Settings > Reports 
• Generate Provisioning reports for: Courses, Enrollments, and Users. Select the desired term.
2. Download the provisioning reports and open them all up.
3. Open a new Excel worksheet which you will use as a master. Save it to your device. 
4. Create three tabs in the master and name them (1) courses, (2) enrollments, and (3) users. 
5. From the downloaded enrollments file, filter to display only rows where role is “teacher”. Copy the results to the master. 
6. Copy the other two provisioning reports into the designated tabs in the master spreadsheet.
7. Format each report as a table.
• Go to Home > Format as Table or Insert > Table to convert the data into a table. The table style doesn't matter.
• Name the tables (upper left) (1) courses, (2) enrollments, (3) users so you can refer to them later in the scripts.
8. Go to the enrollments table. We are going to pull the name of the first instructor listed in each course onto this page. 
• In the enrollments table, click in the next available column and add a header called full_name.
• Right below the header, enter the following formula:
  =XLOOKUP([@[canvas_user_id]],users[canvas_user_id],users[full_name])
9. Go to the courses table. We are now going to duplicate the name of the first instructor for the course onto this page.
• Click in the next available column and add a header of full_name. 
• Right below that, enter the following formula:
  =XLOOKUP([@[canvas_course_id]],enrollments[canvas_course_id],enrollments[full_name],"")
10. Filter based on course status
• Go to the courses table, Filter and select only active or unpublished to view the list of courses you would like to see. 
11. Bonus: Include emails.
• Go to the enrollments table. In the next column, add the header “emails”.
• In the cell beneath the header, enter =XLOOKUP([@[canvas_user_id]],users[canvas_user_id],users[full_name])
• Go to the courses table.  In the next column, add the header “emails”.
• In the cell beneath the header, enter =XLOOKUP([@[canvas_course_id]],enrollments[canvas_course_id],enrollments[emails],"")

[James]

Method 4

I had someone email me today asking if my Google Sheet for this still worked. I didn't know so I tested it and it did. But then he asked if there was a way to sort the Canvas Course Search by unpublished courses.

There is not, but I tried something on a whim and it worked. It turns out that if you are a Canvas admin, then you don't need Excel Lookup function, Power View, or Google Sheets to find a list of the unpublished courses.

1. Go to your Admin > Courses page as detailed in How do I use the Courses page in an account?
2. Add a query parameter of published=false to the URL and hit enter. This needs to come after a ? and if there are already other values after the ?, then you will need an & in front of it.

Examples for account 12345 are <instance>/accounts/12345?published=false or <instance>/accounts/12345?enrollment_term_id=876&published=false

The published=false is passed on to the List active courses in an account API endpoint, where it describes that published=true shows only published courses, published=false excludes published courses, and if omitted (the default), then it is not filtered.

The beauty of this method is that you can then filter by term, search for instructors or courses, click at the top of most columns (except for Published) to sort, paginate through the list, and do it all from within Canvas with no special tools needed.

You cannot publish the courses from that menu, but you can find them.

[d00084815]

@James I've been using your Google Sheet to list unpublished courses for several semesters (thanks so much!), but I encountered a problem in trying to use it today. When attempting to select the new term through the Canvas menu, I get this message:

TypeError: Cannot read properties of undefined (reading 'length')

Then

You must specify an account before you can find the terms

I suspect Instructure has changed something in the API that broke this. I even tried starting over with copying a new sheet and reconfiguring the API settings, but still got the error.

[James]

@d00084815 

I doubt that it's something that changed on Canvas' end. There have been enough people report this that there is likely a bug in my script that doesn't check something. Almost always this has been related to not making it through the configure API settings part successfully. In fact, I can duplicate the error you're getting if I don't successfully authenticate.

Here are the suggestions that seem to work (either that or people give up after trying).

1. Go back to Canvas > Configure API Settings. Make sure that it shows your hostname and a bunch of discs (solid circles) for the Access Token. There is a way to check this. If you go back to configure API settings, it should show your hostname. If so, click Submit (no need to change anything) and you should get a Success! message.
2. If not, then you didn't successfully authenticate and that is where people normally run into issues. When configuring the API, put in the whole hostname. I designed it to work with institution when your full instance is institution.instructure.com, and that works for me right now, but there are other times that it works better with the whole thing rather than just the shortcut. You should also be able to copy/paste the URL from your dashboard.
3. Make sure it comes back with a confirmation notice that you have authenticated correctly. If that message doesn't appear, then it didn't take. The message says "Success! Connected to Canvas instance as your name, You may now use the API calls." If you get a Failure! notice, then there is a problem with the instance/token. Nothing will work
4. Make sure the API token you're using has admin rights (this is rarely the issue).
5. If you are reusing a token that you have saved, go to Account > Settings and make sure that the token is still valid and hasn't expired.

[d00084815]

@James Thanks for the help! My problem does definitely seem to be authenticating via the API. When I go to configure the API settings, it does not show my hostname. I've tried putting both the short and full hostname in (and copying from the browser). I've also verified that I'm using the correct token (and even regenerated/tried using a new one, both with Admin rights), but I never get a notice that I authenticated successfully. I didn't get a failure notice either, but when I open the configure API settings again, nothing has changed. Any other suggestions for me?

[James]

@d00084815 

Are you using a school Gmail account? If your institution has taken over the domain at Google (ours has), they can impose limitations on what can be done by their users in the name of security. Some people have reported that the scripts work with they use a personal Gmail address but not when they use their school account.

[d00084815]

@James I am using a school Google account. I did resolve the problem, but I'm still not sure why I couldn't get it working on a fresh copy of the Unpublished Courses sheet.

I had an older copy of the Google sheet that I was able to successfully update the API token in the Configure API Settings and it is working now. For some reason, I'm not able to successfully configure the API settings on a fresh copy of the Unpublished Courses sheet (the API settings aren't being saved). I'm using the same hostname and access token that are working on the older copy of the sheet.

[donna_lummis]

Like so many others have reported, the Google sheet I was using stopped working. It suddenly started timing out. I did try reducing the number of terms but always got the error that I had to pick an account first. I tried following that message but selecting an account never led to any type of message whatsoever. I assumed the script ran in the background and was simply auto-selecting our root account because I'm a full Admin, but I continued to get "you must pick and account first" when I tried to select a term. (I did get a list of terms to select, so I know that part was reaching my Canvas instance successfully.) I read more comments and decided to start over thinking my problem was that after so many years it was my cache that was the problem. Reconfiguring the API seems to have been a mistake because I can't get it to work anymore despite trying everything from scratch. I never get a confirmed or failure message when entering the API token. I get a "length undefined" message when I try to select a term. I realize it's not connecting but nothing works. I have copied in a new version of the sheet, approved the integration when prompted by Google, tried every possible version of our instance and a new API token, and I get nothing. It will not connect. I even tried restoring the old sheet that connected yesterday but that didn't restore the API configuration I deleted this morning. I don't know if there is anything else I can try but I'm fairly desperate to get this to work. Classes are starting and I cannot publish everything manually. I'm also not a programmer and it would require a lot of time for me to use one of the other methods to accomplish this task. Does anyone see something I missed? Thank you in advance for any advice.

[James]

@donna_lummis 

"I realize it's not connecting but nothing works" says a lot. If it doesn't connect, nothing is going to work. Until you get the API token working, it's not going to do anything useful.

There are several things that could be going wrong, but I seem to think the length undefined issue was related to not being authenticated when it assumed that you were.

The script used the REST API. Since then, Canvas has allowed some things to be fetched using GraphQL. Those queries are generally faster than the REST API so there may be a way to speed it up. However, if you're not getting the confirmation message when you put in the credentials, it's not going to work.

If you're not putting in the full URL when it asks for your instance, do so. I've seen some of the scripts fail when you put in school vs school.instructure.com. You might even want to put in the full https:// with that.

The part about having too many terms may be an issue if you're making so many requests at once that Canvas is blocking them. That might also be an issue if there are a lot of classes. It's been a while since I've looked at this and I never used it for myself as we don't publish for faculty - we put that on them. Of course, all that is meaningless if you cannot connect.

Can you give me ballpark figures of how many terms and courses within a term you're talking about?

[donna_lummis]

@James ,

Thank you for looking at this and offering advise. I have been putting in our full instructure URL lately starting with the https:// after reading through some of the comments in this thread. We do have a lot of terms (over 60). Some of these terms only have a few courses (like summer session 1 or 2), while others have over 400, which very well could explain the timeout problem. That's why I was trying to limit the terms it was importing. I do think now that it's a matter of getting it to connect more than anything else. I have a high speed internet plan, but I did try changing my internet connection from my home ISP to my cellular hotspot, and I also tried using a VPN connection to work. I even tried these various connections again a minute ago in the hopes that being out of regular business hours may have an effect. I think my next option is to try this again from on campus. Maybe something is blocking the connection somewhere and being on campus will avert that issue. In the meantime, I got some help from some of our administrative admins and we got everything manually published for the start of classes tomorrow. 

[James]

@donna_lummis 

I'm glad you were able to get the courses published. I doubt that it's your ISP causing the problem. They would have to be blocking calls from Google to make that happen. The calls to the API come from Google servers, not from your computer. That means they tend to be fast. It also means that it's not using your ISP, so checking from work probably won't make a difference. I will say that sometimes the ISP does make a difference. My wife uses JASP (a statistics package) in her classes and if I go to download it from home, it takes about 30 minutes. If I download it from work, it takes 11 seconds. Yes, they have a faster connection, but then I can download it from school to home in 4 minutes.

60 or even 70 terms isn't too bad, as long as they're not getting all fetched at the same time. It's the number of terms that end up in the current, future, past, or undated categories. It tries to fetch all courses for all terms included. Unless that's in the hundreds and you have hundreds of courses for each, it probably won't be an issue.

The number of courses sounds bad, but if it's just for one term, that's not going to be the issue. I made a request to get 100 courses for our fall 2024 term. It took 813 ms. When I asked for the teachers, it took 979 ms. Those are both under a second, so getting 400 courses takes less than 4 seconds. If I have to do that for 10 terms, then I'm looking at 40 seconds. That's still not very long in the grand scheme of things.

That also suggests that there's not a lot of gain to be made from switching to GraphQL. The list of terms isn't available through GraphQL. Getting a list of all the courses (published and not) for the current term took 632 ms, faster than the 813 ms for just the first 100. The issue is that it does not include the start and conclude date nor the teachers. Including the teachers made it take 17.85 s, much longer than the 4 s to get them. I didn't find a way to get the start and end date for the course through GraphQL. So basically, I cannot speed up the fetching of data other than to perhaps allow for more filtering of the terms.

I really think the problem is not being able to get it connect when you do the configuration, rather than some problem the script is having afterwards.

Did Google Sheets come up and ask you to grant permissions to execute the API calls? I don't have a way of signing my scripts and so it throws up all kinds of questions and makes you jump through hurdles. If you were using a school computer or they managed your computer, it's possible that they would have blocked Google from allowing that and then it won't work. Our IT department blocked Chrome from saving passwords or using the password manager after I had signed in to Chrome. That was on my personal account, not even the school's account. It was a real pain because many of my passwords are those cryptic ones that Chrome offers to create for you, so there was no way I knew what it was. Thankfully I had my personal laptop with me and was able to pull up the code to get int. But I've seen other people having problems with the Google scripts running from school controlled computers or accounts, but they work fine from a personal account on a personal machine. And you might think -- oh, but it worked before. Yeah, my passwords worked in the spring, but they reset things over the summer, so you never know.