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

[chun_li]

Hello,

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

Thank you,

Chun

[James · Accepted solution]

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

[kona]

 @chun_li ​, unfortunately no, there is no way to do this. There is a feature idea - - that is slightly similar to what you are looking for. It's for a printable course roster (and something for groups), so not exactly, but similar. If you don't think this is close enough you can create a new Canvas Feature Idea​ for your specific need.

[Beth_Young]

This idea is similar, too:

[James · Accepted solution]

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

[e_t_r]

https://gist.github.com/jaclynsaunders/3e6d8ca943d1b9215cfb66f9f7476bc4

This tool also works for a quick python notebook solution!

[jwillin]

@James ,

Wow, James. As always, this is so helpful and clearly written. Thank you so much.

I have a question about result sets that exceed 100 rows. How do I move to page 2 of a result set that exceeds 100 items?

Thank you!

John Willingham

[James · Accepted solution]

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

[jwillin]

Thank you, @James! I really appreciate it. I’ll give that a try. Very helpful!

[JosephCamm]

This seems like something that should be trivially available, without having to go through this process.

Is there anyway this can be included as a feature? All it would need is a column in the gradebook that states what group a student is assigned to for each of the group types that have been set up (usually 1).

[kbink]

Actually this is possible now and pretty easy.  If you follow the instructions at How do I import groups in a group set, When you download the roster CSV you get all the student names and all the groups they are already in. 

[JosephCamm]

Thank you kbink, I also realized this route was possible yesterday after much frustration. The '+ Import' button on the Groups area needs to be renamed to what it does: i.e. something like 'View or Edit Groups Roster'. Best wishes.

[ja46272]

I too would like to see this functionality, to export a group set as a csv, with the touch of a button.  I can't even seem to find a way to search for a student within a group set (for instance, if John Smith asks me which group he's in, I have to check all of my 66 groups manually to find him.)

[elm]

I wrote a short Python 3 script that prints the group name followed by login ID for each student in the group.  It requires that you know the course ID (easy enough to find in the URL), the name of the group category (from the Group tab), and an access token for the API allowed to see the information.

Please email me at elm@ucsc.edu if you want a copy of the script, which requires Python 3 and the requests and json Python 3 libraries.

[l_p_bamwo]

Currently, there is no way of exporting groups out of Canvas. However, you could set up a group assignment (assign marks to whole group) 0 point, non-submission assignment, place in 0% weighting group and make sure that it has no due date and the grade posting policy is set to manual. You can then score the group with a number in speedgrader and then export the csv from the gradebook. You will then be able to sort them into groups by the group score given. 

[vanzandt]

That's genius!  Thank you for sharing.

[sjwebb15]

Hi Everyone,

Thanks for the great discussion here! I made my own Python solution. Possibly not as robust as what is linked in the previous post but it seems to get the job done and is simpler (to my mind).

import requests
import json
import sys

class_id = id    #Get this from Canvas for each class (number in url)
write_file_name = sys.path[0] + '\Section 1.csv'    #Or whatever you want
writeFile = open(write_file_name,'w')
fileHeader = 'Pre-assign Room Name,Email Address\n'
writeFile.write(fileHeader)


token = "put your token here" #Authorization token. Set this up in your Canvas profile
headers = {"Authorization": "Bearer "+token} #Authentication headers so you can access Canvas
category_end = "/api/v1/courses/%s/group_categories" %class_id
base_url = "https://institution.beta.instructure.com"    #Edit this to reflect your institution's main Canvas URL. Here I've used the 'beta' instance of Canvas for testing purposes.
req = requests.get(base_url + category_end,headers=headers) #This gets the raw data from Canvas regarding what groups there are in class_id

for group_cat in req.json():    # This for-loop runs through all the group categories and gets the category_id from each one
    category_id = group_cat['id']
    group_end = "/api/v1/group_categories/%s/groups?per_page=100" %category_id
    gid_req = requests.get(base_url + group_end,headers=headers)
        
    for group in gid_req.json(): # This for-loop runs through all the groups in each category and gets the group_id for each group
        group_id = group['id']
        room_name = group['name'].encode("utf8")
        user_end = "/api/v1/groups/%s/users?per_page=100" %group_id
        user_req = requests.get(base_url + user_end,headers=headers)
        
        for user in user_req.json():    #This for-loop runs through each user within each group and pulls their login email. It also writes the Group Name and login to a file
            username = user['login_id'].encode("utf8")
            outstring = "%s, %s\n" %(room_name, username)
            writeFile.write(outstring)
            
writeFile.close()   #Close the output file

[kbink]

Hi John,

Canvas a has a new feature for importing groups via CSV that actually comes with a hidden feature the exports a roster with the groups also listed!  Here's the information : https://community.canvaslms.com/t5/Instructor-Guide/How-do-I-import-groups-in-a-group-set/ta-p/41779....  If you already have students assigned to groups in Canvas and click on "download course roster CSV", you get a a list of students with their groups!

Kalli

[jwillin]

Thanks, Kalli!

[JohnToenjes]

Perfect!

[JasonKu]

Here is a quick workaround. Open your Group listing page and expand all the groups so that all the student names are visible. Then copy and paste the following code into your browser's javascript console. It will print a string back to the console listing the group names followed by their users separated by line breaks, which you can then copy and paste into a spreadsheet.

const A = [];
for (const group of document.getElementsByClassName("group")) {
    const name = group.getElementsByClassName("group-name")[0].innerText;
    A.push(name);
    for (const user of group.getElementsByClassName("group-user-name")) {
        A.push(user.innerText);
    }
}
console.log(A.join("\n"));

[EI_Helpdesk]

Super useful @JasonKu  - you can download the csv with the group members, but it doesn't help identify the Group Leaders (that would be super super useful too @canvas  ) so I modified yours to show group leaders (and the groups don't have to be expanded).

const A = [];
for (const group of document.getElementsByClassName("group")) {
const name = group.getElementsByClassName("group-name")[0].innerText;
A.push(name);
for (const user of group.getElementsByClassName("group-leader")) {
A.push(user.innerText);
}
}
console.log(A.join("\n"));