Updated November 29, 2016, to reflect Version 3
The spreadsheet now processes group discussions. They take a lot longer to process since you have to make calls for each group.
If you were a user of the older version and want to upgrade to support discussions, you'll need to make a new copy of the spreadsheet. New downloads will automatically get the new version. If you are a user of version 2 and don't use group discussions, then you do not have to upgrade to version 3.
Updated December 31, 2015, to reflect Version 2
Introduction
There are several places in the community where people have asked about counting student posts within discussions:
Basically, they want some form of a cross-tabulation of student and discussion, with the values containing the number of posts made by a particular student for a particular discussion.
The discussion counts are real, the names have been anonymized using random suggestions from NameThingy.
Background
This can be done through the API. You get a list of students, you get a list of discussions, you then iterate through each discussion topic, getting the full view for the discussion topic, and count how many times each combination of student and discussion occurred.
There has also been a lot of hype discussion recently about using Google Sheets to obtain information. APIs are difficult for the non-programmer and so this leaves out most teachers who would like the information like this report provides. As I answer questions in the community, it seems a lot of them explain how to do something but then I feel I've lost people because it's too complicated for the masses. Putting it into a Google Spreadsheet that someone could copy, specify a few parameters, and then run themselves is a great way to make it available to more people.
I've written my own Canvas API libraries, first in PERL and then in PHP. They're not complete, they have the functionality I needed to accomplish the task, and they're heavily tailored to the needs and setup of our institution. In other words, they're not easy to share.
Google Sheets uses a form of Javascript, and I'm not a Javascript programmer by any stretch of the mind. I can figure stuff out, but a lot of it is knowing what I want to accomplish and then looking it up in the documentation to figure out how to code it in Javascript. For experienced programmers who can write this stuff, you'll take a look at it and go "What school did he graduate from? Let's revoke their accreditation!" With that disclaimer out of the way, I didn't see any professional programmers contributing these things to the community, so I thought I would go ahead and put it out there.
Instructions
So I am making available a Google Spreadsheet that people can use until something better comes along.
- Make sure you're logged into Google Drive and then open the spreadsheet Discussion Counts
- Go to File and choose Make a Copy. Name it whatever you like.
- There will be a new menu item after Help called Canvas. This is where you need to do everything.
- A one-time setup is necessary to authorize Google to access your Canvas information. Go to Canvas > Configure API Settings. These will get stored in the Google userProperties(), which is specific to this spreadsheet and your user account and so you can share this file with others without sharing your credentials.
- Authorize Google to access your Canvas information.
- Enter your Canvas hostname. The easiest way is probably just to copy/paste a URL from your course into the box. Alternatively, you may enter just the hostname like richland.instructure.com or if your site ends in instructure.com, you may just enter the first part like richland.
- Enter your Access Token. If you don't have an access token, then see How do I obtain an API access token for an account?
- Choose Canvas > Specify Course and enter the Canvas Course ID for your course. This is the number that follows the /course/ portion in your course homepage URL. Alternatively, you may just copy/paste the URL from a page in your course.
- Choose Canvas > Refresh All Discussions and wait. For my course with 42 students, 14 discussions, and about 2000 posts, it took about 45 seconds.
You can also watch the video I made that demonstrates this.
Changes since Version 1
Version 1 of the spreadsheet downloaded and counted the information itself and you had to edit the code if you wanted to make changes like including instructors. Version 2 creates a pivot table and you can just change the filters to specify what roles or status to include.
This is made possible by actually storing the data in the spreadsheet rather than counting it internally within memory. You'll see a page called Data that looks like this after you run the script.
You'll notice that the UserID and DiscussionID are stored as numbers as well. This is to help eliminate ambiguity if you have to items with the same name. There is also a CourseID and CourseName field. This would allow you to gather information for more than one course. The normal process would be to just make multiple copies of the spreadsheet. One possible use would be if you're documenting student learning outcomes and want a single file. You could also add a pivot table filter for the course if you like.
There is a menu item called Append New Discussions that would need to be used if you have multiple courses in the same file. It's also intended to be a quick way to add new discussions to the count without going through and refreshing the entire discussion. You may find this helpful if you grade throughout the course rather than waiting until the end. Note that this option will not check any previously-scanned discussions for new posts, so it's not appropriate if you have on-going discussions throughout the course.
There is a menu item called Forget API Settings that will erase all your personal information from the User Properties within Google Sheets. This should not be necessary with the new interface, and User Properties are unique to each user and each spreadsheet, so sharing the spreadsheet with someone should keep your credentials safe. This option was primarily for my testing, but I left it in there for people who want to make sure their credentials are not shared.
Version 1 also failed to properly count nested discussions. It would count top level posts and replies to top level posts, but not replies to replies. That has been corrected. When I wrote the original code, I was just learning how to use Google Sheets and program JavaScript and this version incorporates a lot of what I've learned in the 5 months since then. If you never saw version 1, you won't really appreciate how much easier version 2 is.
Google is deprecating support for their default (native) sandbox mode on June 16, 2016. Version 2 uses the new mode (iframe) so it should continue to work after that date.
Original Disclaimers from July 31, 2015
I've only tested this with two courses. Both were using threaded discussions. Your mileage may vary, but it works for me.
I was intending to use this as a framework for other projects because it seems a lot of my responses in the Community are "Yes, you can do that through the API and here's how" and then I write a really technical explanation that may never get implemented. But if there was an easy way to share it, like a Google Spreadsheet, it might encourage people more.
This is, however, my first time sharing code like this and I really have no idea if I did it right or not. I'm also not a Javascript programmer, so I basically spent a lot of time looking at the Google Apps Scripts codes and then the Mozilla Javascript documentation. I originally decided I would look at someone else's code and then found it too complicated for me to follow, so I wrote my own from scratch. I didn't do the sums through the spreadsheet, I found them by hand within my code. It definitely could be improved upon, but it's a first attempt. The problem is that I tend to be a perfectionist and so things never get done because they're not perfect. I'm stepping out of my comfort zone here, so hopefully the code works for you.
Only the GET functionality of the API has been tested. It might work, but until I have a need for a POST, PUT, or DELETE, I won't know for sure.
People can do what they would like with the code, but it would be really awesome if someone who did know Javascript or Google Apps wanted to collaborate and improve it. I would like for there to be a single copy of the canvasAPI.gs file that is reasonably up to date and then modify the other script to do the actual processing. The only thing I ask is that you don't sue me if it doesn't work.
