# Community

cancel
Showing results for
Did you mean:

# How to list teachers who have published/unpublished courses

Community Champion
34 56 11.5K

## 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.
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.

2. Choose File > Make a copy from the menu. From now on, work with your copy.
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.

Community Contributor

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:

Community Champion

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.
Community Contributor

Sabine Zabarovska

Northeast Mississippi Community College

Learning Management Systems Support Specialist

Canvas Support

Community Champion

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.

Community Contributor

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

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

Community Champion

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

Community Champion

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.

Community Champion

I'm glad you found it useful.

Community Contributor

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?

Community Champion

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.

Community Contributor

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

Community Champion

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.

Community Champion

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.

Community Champion

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

Community Contributor

Yes we have a lot of terms.

John

Community Champion

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

Community Contributor

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

It is working!

Sabine Zabarovska

Community Contributor

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

Dr. John Boekenoogen
University of Oklahoma

Community Champion

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

Community Participant

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.

Community Champion

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

Community Champion

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.

Community Champion

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

Community Participant

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,

Community Champion

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

1. Open it up in Google Sheets.
2. Click on Tools > Script Editor
3. Click on Unpublished Courses on the list of code modules on the left side
4. Scroll down to about line 307.
5. 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).

Community Participant

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

Community Champion

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

Community Participant

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

Community Champion

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.

Community Champion

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?

Community Coach

Okay, most amazing one!

This is just the second Canvancement I have tried - hey, I'm busy, leave me alone!

• Took me less than a minute, and that's only because I had to read the instructions,
• Worked like a champ!
• My office partner is gonna love this, and so will our students!

Thanks again  @James ‌! You really do rock!

Kelley

Community Participant

Just used the Excel version - awesome! Saved me so much time!

Community Champion

Sorry for the delay, we've been traveling home from InstructureCon and just got home tonight.

One possibility might be if you created the access token using the main account. It won't work on the beta account until the weekly copy-over. So you might try it -- in read-only (don't try to change the state) on the production instance.

Assuming that's not the issue, try opening your browser and pasting /api/v1/accounts/self/terms at the end of the main URL for your beta instance and see what the message is. The error message you're getting happens when that fails to return information or if it fails to have an enrollment_terms object.

Community Champion

Thanks  @James , I figured you guys were still traveling. I tried adding the /api/v1/accounts/self/terms at the end of my URL and returned the following:

{
"status": "unauthorized",
"errors": [
{
"message": "user not authorized to perform that action"
}
]
}

I know that I am just a sub-account admin, but do you happen to know which permission I would need to be able to run these on my sub-account?

Community Champion

First, try changing the "self" to be the ID of the sub-account that you're an admin for and see if it lets you do that. If so, then we've got the wrong sub-account in the Google Sheet. No one has ever mentioned using them on just a sub-account, but I'm an admin at the account level so I never thought to see what would happen if you weren't. I'm not convinced that's the problem, though, based off what I found next.

You might check out the Canvas Account Role Permissions document. Page 6 says the Account Admin has a default role status of "Terms" and that allows the user to view and manage terms for the root account.

A more useful note might be on page 29 under "Additional Considerations".

Users and terms are owned at the account level, so the SIS endpoint always confirms the user’s permissions according to root account. Sub-accounts only have ownership of courses and sections; they do not own user data.

Sub-account admins are not able to view SIS information unless they are also granted an instructor role in a course. Sub-account admins cannot view SIS information without the course association, as the instructor role has permission to read SIS data at the root level.

It should be possible to code around the terms check. I only threw it in there as a way to narrow down the list of courses in a way that was manageable. I figured that the most common use-case was admins would want to know who hadn't published their courses for upcoming semesters (probably towards the start) and I wanted to make it useful for them so that's how I coded it.

Community Champion

yep, looks like it is because I am not at the top level. The message when I added the subaccount ID was:

{"message": "Terms only belong to root_accounts."}

I can still filter by term from the GUI so I would think that I could pull courses that are listed just within certain terms. I appreciate your help.

Matthew

Community Champion

I checked and I think that because I am not at the top level, I won't be able to run this due to the terms belong at the root account level and they are not going to give me that. Any way I could persuade a "code around" that might work for me?

Community Champion

What is it that you're trying to do? How do you envision that working without terms -- just getting a list of all unpublished courses for your subaccount or something else?

I'm trying to get ready for the fall semester, so no guarantees, but scoping the problem and having a solution in mind would help know if it's even possible.

Community Champion

Yeah, I guess to pull in all unpublished (maybe with the term info in a separate column) so I could then sort the sheet by term. Then I could remove all except what I want to publish. Just trying to have an easy way to pull our courses and publish for the term.

Community Champion

I looked into things and I think I've put in the changes to make it skip terms. Actually, what it does is add a menu option to select All terms. If that option is chosen, then it doesn't bother with trying to lookup terms.

The List active courses in an account endpoint that I'm using allows you to include[]=term. Not including that would make the code get finished faster. Including it may or may not help depending on your permissions. I'm hoping that the term information from this call isn't restricted, but it may not matter.

As it stands now, the script returns the dates that the course begins and ends, but doesn't include the term information because that was a filter to run it (for most people). Would sorting by the dates allow you to accomplish what you need or do you really need the term names?

I don't have an only-sub-account-admin account setup to test things (I probably should look into that, but this is the first time in 5 years of using Canvas that I've needed it), so depending on how important having the term is to you, can you test this at the end of your instance URL?

/api/v1/accounts/YourSubAccountID/courses?published=0&include[]=teachers&include[]=term

If it doesn't work with the &include[]=term at the end, the question of whether or not I can include the term becomes academic. If it still doesn't work without the &include[]=term (just published and teachers), then I won't be able to get it to do anything.

By the way, these changes are in a test copy of the spreadsheet I have. I didn't want to break the master while we tinkered. Once we get it figured out, then I'll just update the code in the master so everyone can benefit.

Community Champion

So I am floored that you were able to do something so quickly at this time of the year. Thanks for taking the time to work on my small problems.

I tested placing the string at the end of our URL and was able to return results with the term info. I believe that is a good thing. However I could live with the dates or even just the course name as we title our courses with some term distinguishing identifiers, it is just a little harder to filter a spreed sheet with.

If you need more we can discuss outside of the community via email -> mjennings at uab dot edu.

Thanks again!

Community Coach

Hi  @James ‌

Script for Googlesheet works great for listing unpublished course, but I have run the "Publish Courses (Caution)" script multiple times, and over night, and it has only published 74 of the courses.

Any ideas?

Kelley

Community Champion

I finished the development with  @mjennings   about two weeks ago, but got busy getting ready for the fall term and just got around to updating the spreadsheet. I've updated the documentation above to reflect the changes, but here is a summary:

• It now works for sub-account admins, who are typically missing some of the permissions that were needed to run the program.
• There is an "all terms" option, which is what sub-account admins will have to deal with.
• The term name is now listed, which is redundant when listing just a single term, but necessary when viewing all of them.
• The number of students enrolled in each course is now provided.
• Dates and times are converted to the local time specified in the spreadsheet settings and are no longer given as UTC timestamps.

People make their own copy of the spreadsheet when they use it and these changes will not be reflected on your version unless you make a new copy.

It turned out that sub-account admins don't have full permissions in a couple of areas. They can't get a list of terms and they can't use the API call to bulk publish (up to 500 at a time) courses. They do, however, have permissions to publish one course at a time, so I modified the code to fall back to the one-at-a-time process if the bulk publish fails.

Community Champion

So after 2 years of this working flawlessly for me I am now starting to run into a problem with the number of unpublished course we have causing the script to time out, leaving most of my next semester courses off the list. Since I can still not pull based on terms, is there a way that I can pull by start date or created date so that I am only grabbing the courses that are the most relevant?

Community Champion

I'll have to get back to you on that. To be clear on the problem, you're saying that you have so many unpublished courses that it is timing out before it can load the entire list, not that it is timing out when you're trying to publish the courses? Do I have that right.

The second one is an easy fix. The first one will take some creative thinking and I've got class in a few minutes. Maybe a hybrid approach would work -- download the list from Canvas and then put that information into the Google sheet to actually do the publishing.

Community Champion

Correct. It is when I am loading the list of unpublished courses. I get the following message.

We have a lot of courses that build that we never publish due to the way the university is building the courses from the registration system.

Actually publishing the courses that we want is not the problem.

Community Champion

Matt,

While I look into alternatives -- would it be possible to get your school to delete those courses that were created from the registration system but never published or used?

There are two possible ideas I'm bantering about.

The first is a quick fix and if it works, it should get you back and rolling in short time.

Can you edit the source code for the unpublishedCourses script and add a configuration option for testing? It's in the listCourses() function that starts at line 315 (at least in my master copy it does). There is a var options = {} definition starting around line 322. Add a line in the options block so that it has 'completed' : false

When you're done, it should look like this:

``    var options = {      ':account_id' : accountId,      'published' : false,      'completed' : false,      'include' : ['teachers','term', 'total_students']    };‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍‍``

In theory, that should only return the list of courses that aren't completed (concluded), so if there was an ending date on them, then they may disappear. I had 795 pages of courses (10 per page) and when I added that, it dropped to 119 pages, so I'm hopeful, but I don't have anything as large as yours to test with.

A second route I'm looking into is a more significant change.

Google Apps Scripts (GAS) has a fetch() function that I'm using to get the information from the API. When it completes, there is a Link header that contains the URL to get the next page. That's what Canvas wants us to do. Get a page, fetch the next link, repeat until there's no more pages of results. In my course list, not accounting for just that that are unpublished, there are 759 pages of courses (when fetching the default 10 at a time). I would have to fetch page 1 of the results, then page 2, then page 3, and so on. Every time, I have to wait for one call to finish before going on to the next one. That's what's causing the timeout.

GAS has a fetchAll() function that allows you to make multiple requests at the same time. This would not work when the next URL uses a bookmark, but the list of courses uses the older page=# format. That means that I could make a single call and get the total number of pages and then make the call in batches of 5-6 at a time. I might be able to get more, but you run the risk of requesting too much and causing Canvas to stop responding to your requests. Still, if can make 5 calls in the time it used to make 2, you're still going to see a significant improvement in the download speeds and it should get you back under the 5 minute timeout.

This isn't a permanent fix, though. Eventually, you'll have even more courses that are sitting out there unpublished and it will be too much for even this approach. That's why I asked if you would be able to delete some of those courses that are still out there from before. If not delete, perhaps you could conclude them.

The second route would be beneficial in other areas as well, but I'm a little backlogged and just took on even more duties at work. If the first way doesn't work, then I'll look at the second way when I can make time.

Community Champion

@James  Thank You The first option works. Our past courses do have end dates and get marked concluded which has significantly paired down the list. I have been asking about removing these old unpublished courses for the last year and a half or more, but sometimes my voice is to far away from those that make that decision at the university level. Thanks again for the amazing turn around time and helpfulness.

Community Champion

You're welcome. I'm glad the first one worked.

I'm going to hold off putting it into the main code just yet. For people with root account admin access, they get to pick past, present, or future terms and this might keep people from getting past term data if it applied as I described to you. Maybe I can figure out how to work it into the Select Term menu.

Community Champion

I did not use the Google sheet, but not being exactly a high-powered Excel user, I found the basic approach described in Method 1 to be really helpful.

I made a worksheet with sheets/tables for Users, Courses, and Enrollments, and linked the Enrollments sheet to the Users and Courses sheets using the formulas "=MATCH([@[canvas_user_id]],users[canvas_user_id],0)" and "=MATCH([@[canvas_course_id]],courses[canvas_course_id],0)" to create "users_row" and "courses_row" columns with the corresponding rows from the Users and Courses sheets.  That let me create a "view" in the Enrollments sheet with "=INDEX(users,@[users_row],<column number of desired column>" to pull the user information like "full_name" and "email" over into the Enrollments sheet, and similarly, "=INDEX(courses,@[courses_row],<column number>" pull Courses long_name and status.   That way, I could do all the viewing and filtering from the Enrollments sheet.

For our needs--finding instructors who were not currently making use (or much use) of Canvas--we found we wanted some other information as well, so we added two additional sheets pulled from Canvas reports:

--Accounts, pulled from the provisioning report "accounts".  We have sub-accounts for each college, then further sub-accounts for each department/subject, which is where the courses themselves are.  This let me match the "account_id" from the Courses sheet to locate the matching row in the Accounts sheet, then use the index formula to display the "parent_account_id", which is the college that the course fell under.

--Last_login, pulled from the "Last user access" report.  This let me match the "canvas_user_id" from the Enrollments sheet against the "user id" in the last_login sheet, and the index to display the "last access at" date for the user.  This helped us figure which instructors were actively logging into Canvas even if they had one or more courses for the current term that were not published.

With all this information displayed in the Enrollments sheet, I could filter on role "teachers" and course status "unpublished", then copy the whole sheet over to a new worksheet that could be further filtered, sorted, and cleaned up (extraneous columns removed) for administrative consumption.

Ideally, all this would probably be in a relational database for more powerful querying capabilities, but as a relatively quick way to get at the information we wanted, it was pretty useful.