Is it possible to export a student list with Group information?

Jump to solution
chun_li
Community Contributor

Hello,

Is it possible to export a student list to a spreadsheet with group information in it?

Thank you,

Chun

2 Solutions
James
Community Champion

 @chun_li ​

What Kona meant when she said "no" is "not through the web interface" or "not easily". Another way to look at it is "How badly do you want it?" If you have small classes, it may be easier just to get the information from the People pages where the groups are listed. If have a large class with lots of groups, it may be worth your time to continue.

That said, there are a lot of things that can be done through the API that can't be done through the web interface.

There are some group-related reports that may be useful that we can get from the API and although the API generates JSON and not a spreadsheet, there are online converters that will convert your JSON into a tab-delimited format that you can then copy/paste into Excel or Google Sheets.

Some definitions before we start may help

  • A group category is the structure that houses groups. When you use groups in your class and it asks you what group you want to use for the assignments, it's talking about a group category. A group category can hold several groups. Group categories belong to a context, which is either a course or an account. It is identified in the API calls as :group_category_id
  • A group is a collection of people within a group category. A group belongs to a group category. It is identified in the API calls as :group_id.
  • A :course_id is the Canvas code for your course. It's an integer that can be obtained from the URL of your course homepage (and many other places). Every where

Each of the three :id's need replaced by actual numbers in the API calls.

The Process

This is a multipart process, but may be shortened depending on how complex your group structure is.

The basic flow is like this:

  1. Get a list of group categories for a course
  2. Get a list of groups for a group category
  3. Get a list of the users within each group

Note that there is an API call that will generate a list of all users for group categories, but it doesn't say which individual groups they belong to and for me, at least, turned out to just be a list of people in the class, even though not all of them were in a group (but because they had all submitted assignments that were part of that group category).

Step 1: Get a List of the Group Categories for a Course

You need to use the Group Categories API to do this. There is an endpoint called List group categories for a context​ that you will need to use:

     GET /api/v1/courses/:course_id/group_categories

Hint: If you have more than 10 group categories in your course, add ?per_page=100 to the end of that query to get up to 100 of them at a time and hopefully avoid pagination, which opens another level of complexity to the process, so it's best avoided.

The important fields returned are the id, which is really the :group_category_id that you will need in the next step, and the name, which is what you called the group Category.

Use the name to choose the group category you want the groups for.

Step 2: Get a List of the Groups within a Group Category

There are a couple of ways to go about this, probably the most forward is sticking with the Group Categories API, but this time using the List groups in group category endpoint.

     GET /api/v1/group_categories/:group_category_id/groups

You need to replace the :group_category_id with the id found in step 1.

Hint: The same note about ?per_page=100 from step 1 applies here and to all GET statements.

This will give you a list of all the groups that belong to a particular group category.

The important information to take away from this is the id, which is actually the :group_id that you will need for the next step.

Step 3: Get a List of the Users within a Group

To do this, we will need the Groups API and the List group's users endpoint.

     GET /api/v1/groups/:group_id/users

You will need to iterate over all of the :group_id values found in step 2 to get the entire list of group membership.

Hint: The same note about ?per_page=100 from step 1 applies here and to all GET statements.

This will give you a list of the users within each group. In particular, it will return the name of each student in the group. It also returns other things like sis_user_id and sis_login_id if you want it.

There are other API calls here, one is called Get a Group Membership, but it only returns the Canvas user id's for the members and not their names or login_ids. If you only have one group category in the course, then you can use the List the groups available in a context endpoint and cut out the stuff with the group categories. You'll still need to iterate over each group to get a list of the students in it.

Don't Speak API?

It turns out that everything that I've done here can be done without writing a line of code, but it will take additional work.

Using the Location (URL) within your Browser

You can make API GET calls (which everything here is) without getting a Token and then messing with the code.

You can append the GET statements above to your main Canvas instance.

For example, let's say that you're logged into your course and sitting on the main page and the URL is

     https://mysite.instructure.com/courses/123

Then the 123 is your :course_id

Change the URL to

     https://mysite.instructure.com/api/v1/courses/123/group_categories

and then hit enter.

You'll get the JSON code. There may be an extra

     while(1);

at the beginning of the code. That is not part of the data and when you copy/paste it into a converter, make sure you don't copy that part.

Using the Live API

If your comfortable obtaining an authorization token, then there is a menu-driven interface to all of this. Point your browser to

     https://richland.instructure.com/doc/api/live

Enter your token at the top. Then scroll down until you find the Group Categories and Group sections. Expand the one you want, choose the appropriate API call, and enter your information.

Note that this doesn't take advantage of the per_page=100 because it's mainly for testing to see what would happen, but it does give the URL it called and you can manually append it.

Don't Speak JSON?

Now moving on to the final part of the puzzle. You said you wanted this in a spreadsheet and the API calls return JSON.

There is a JSON to CSV converter that I found online (with Google's help).

You paste the JSON from your browser (make sure you don't copy the while(1); part) into the box.

For settings, I recommend using an Output Field Separator of a Tab (also known as tab delimited) so that you can copy/paste directly into a spreadsheet. The first time you paste the results, you'll probably want to include header's in the first row, but if you're copying additional group users, for example, you don't want the headings.

Then click Convert JSON to CSV. You will get a textbox that you can highlight and then copy/paste into a spreadsheet.You will also get an HTML table of the information below that so you can see what it looks like.

According to their website, the convert runs completely using client-side Javascript so the data never leaves your computer. I personally have not verified this and I have not used their system before today, but I'm definitely bookmarking the site because I do a lot of API work.

Alternative Methods

Account Reports

If you are really good friends with your Canvas admin (or happen to be him), then you can generate a report that will list group membership.

Go to Account >> Settings >> Reports >> Provisioning and click Configure.

Tell it that you want Group CSV, the Group Membership CSV, and the Users CSV. You'll probably want to limit it to the current term.

While this fetches all of the data, you still have to do some collating of it. The Groups CSV file contains the group names and IDs, the Group Membership CSV contains the group memberships, but only using the Canvas user IDs, which is why you need the User CSV to convert those into names.

Programming Skills

If you have programming skills or know a programmer, you can automate the entire process. I've done that for a project where I need a list of all the group memberships so I can randomly assign peer reviews (Canvas' built-in randomizer is not sufficient for my needs).

That is beyond the scope of this message.


Feature Request

As Kona said, you can always put in a feature request. Those can take a while and may never come to pass.

If you are not technically minded and can't find someone who is, or if you have a small class, it's probably easier just to do it by hand for right now.

View solution in original post

James
Community Champion

@jwillin 

Once you get past the 100, you will have to deal with pagination. For most objects, getting the second page is as simple as adding a page=2 or page=3 parameter, so the query string might look like ?per_page=100&page=2

However, there are some costly API calls like enrollments and page views that use bookmarks instead of page numbers. Looking at the response header from the network call will show a Link header that contains the actual calls that should be made.

Bookmarks are not predictable (like guessing the next page after 2 is 3) so you will have to look at the link header to see what it is. You can go into the developer tools (F12) and look at the network tab to see what network calls are being made.

View solution in original post