How to Count Student Discussion Posts

James
Community Champion
58
54566

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.

149458_pastedImage_2.png

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.

  1. Make sure you're logged into Google Drive and then open the spreadsheet Discussion Counts
  2. Go to File and choose Make a Copy. Name it whatever you like.
  3. There will be a new menu item after Help called Canvas. This is where you need to do everything.
  4. 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.
    1. Authorize Google to access your Canvas information.
    2. 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.
    3. 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?
  5. 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.
  6. 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.

149810_pastedImage_15.png

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.

58 Comments
Stef_retired
Instructure Alumni
Instructure Alumni

This is incredibly cool,  @James ​. I can't wait to try it.

kona
Community Coach
Community Coach

stefaniesanders​, I know for me it sounded a little complicated, but once I sat down and just followed the directions (and watched the video) it was super simple! Smiley Happy

Stef_retired
Instructure Alumni
Instructure Alumni

 @James , your elegant solution sparked a memory in the deep recesses of my mind. When I took a Canvas training a few years back one of the participants designed beautiful graphical displays of "course participation" using Google Sheets and course feeds. If I can get my hands on the original sheets, and share them with you in Drive, would you have time to look at them?

James
Community Champion
Author

stefaniesanders​, If someone has already done it, then I guess I'm confused as to the purpose of me looking at it.

kona
Community Coach
Community Coach

Maybe to see if it can be done easier or better? Smiley Wink

Stef_retired
Instructure Alumni
Instructure Alumni

 @James ​, what Kona said--and also because using it would require reverse engineering and an explanation of the process. I can see the end result, and I have access to the Google Sheet, but I don't know how to make it do its thing.

James
Community Champion
Author

Ahh, thanks. That helps clarify the purpose. Pretty sure I won't have time to reverse engineer it (some of the scripts are quite extensive), but I would probably be able to find time to figure out how to make it do it's thing.

Stef_retired
Instructure Alumni
Instructure Alumni

Thanks,  @James . Over the next few days I'll wade through the entire course (it's a big old course with a lot of activity) to get the author's name so we can provide attribution and to get as much information as I can about the sheet's mechanism; when I have more I'll reach out to you for your Google Drive info.

kmeeusen
Community Champion

Thanks, James - Way too cool for our School!

Can we add this to our Canvas Hacks classroom? We created an API module, which is where this will fit bestest!

Again, thanks!

James
Community Champion
Author

 @kmeeusen ​, I'm not familiar with the best place to put things in the Community, but I put a disclaimer in the code that people could do with it what they want. I don't really consider it a hack, but if you think it will help reach people who can use it by being in Canvas Hacks, then go for it.

My hope is that this will be the first of many similar things or that it sparks some interest in someone else to develop something better. My concern is that someone will ask me a question about it and I'll never get that question because it's in a place I don't have access to myself. So, I guess if you're putting it into Canvas Hacks, maybe you could add me to that?

clong
Community Champion

Great Post  @James ​. I think stefaniesanders​ was referring to a Google Spreadsheet Add On developed by Martin Hawksey that made a really nice dashboard of a Canvas Discussion using the RSS/Atom Feed for it.

See: https://mashe.hawksey.info/2013/02/lak13-recipes-in-capturing-and-analyzing-data-canvas-network-disc...

I would think this could be done via API as well.

James would you be able to help me with learning how to use Google Spreadsheet to Help Manage  special user accounts​ ?

kmeeusen
Community Champion

 @James ​:

I've been hoping to entice you into the Canvas Hacks Demo Course for awhile. This course is not actually in the Community, it is in Instructure Resources. We enroll folks either as: Students, so that they can learn some simple techniques for enhancing their Canvas experience using coding - which are primarily HTML snippets that can be used by almost anyone but progress up to some much more advanced techniques; or we enroll the more experienced folks as Teachers, so that they can contribute to our growing body of knowledge!

The Canvas Hacks Demo Course is truly a growing entity, and as such will never be completed. We currently have 180 participants, of which 76 are enrolled as teachers.

If you would like to be enrolled, shoot me or Scott Dennis your email, and we will get you hooked up. Tell us what permission-level you would prefer (I vote for Teacher).

James
Community Champion
Author

 @clong ​, stefaniesanders​ sent me the link a few weeks ago and I mumbled some things that seemed to satisfy her. The approach that Martin Hawksey took required that the teacher enable the podcast feature, which may or may not be an issue. What he did could be done through the API as well, but he didn't have access to it. For someone with API access, it would be quicker that way.

I think it would be relatively easy to spin one of my existing Google Sheets off to do the manage special accounts. I've made some improvements to the interface since the last one, but then got sidetracked with user scripts and and never got back to update the existing scripts. Canvas did finally make the blogs editable without requiring additional moderation, which would help with updates.  I'll post further comments over on the page itself, but I've got some grading that needs done first and I've been sick since Monday, so finding productive time has been rare this week.

James
Community Champion
Author

 @kmeeusen ​,  @kona ​ added me to it a few weeks ago after you copy/pasted the code over there, but I need to create a Canvas account to get access. I just got another invite today.

I need to set up a repository on GitHub or something to manage the code. Right now, if I update the blog, then the Canvas Hacks copy you made is out of date and people may not know it. There needs to be one place that all the others link to so people can be sure they get the most recent version. Setting it up on GitHub would also encourage other people to contribute to the code to make it better. In theory at least.

I'm not sure what the point of Canvas Hacks Demo is about. You said primarily HTML snippets, but that's not what I'm doing. If it Is it to demonstrate the things you do, then some (most?) of what I write is not demonstrable because it requires installing a user script. If they're going to do that, then they might as well do it on their machines and benefit for real.

That's not to say Canvas Hacks doesn't serve a purpose. It's more to say I'm still figuring out where to live. I'm a jack-of-many-trades, but sometimes those many hats make me look funny when I show up to the royal ball wearing a baseball cap.

clong
Community Champion

Thanks James,

No rush, it's just something on my Canvas bucket list that I'd love to learn more about. Catch Up with your grading and we can continue the convo when things are less crazy :smileylaugh:

Stef_retired
Instructure Alumni
Instructure Alumni

Yes, I'm easily placated by a mumble here and there. Smiley Wink

Stef_retired
Instructure Alumni
Instructure Alumni

Spot on, Chris, that's the one. James kindly looked it over for me. It's quite elegant. ​

milesl
Community Contributor

This is fantastic!

Any interest in modifying it to support group discussions? Smiley Happy

James
Community Champion
Author

 @milesl ​,

No interest on my part, but other people are welcome to take it and extend it. One issue with what's here is that this version does the entire course and so it wouldn't be able to have student names associated, but group names, and then only if you used the same groups for the entire semester. Also, I've never used group discussions myself so I have no experience to fall back on in the programming or examples to look at for the data returned.

I am working on revising this script to provide much greater detail, but only for a single discussion. It would be a script that someone with admin privileges could run for a teacher and provide number of reads / unreads, likes / liked, number of children, number of descendants, etc. Unfortunately, I've got too many projects going and not enough time, so I can't give you an ETA. This project may be more useful for a group discussion since it looks at a single discussion rather than all of them.

awilliams
Instructure Alumni
Instructure Alumni

Hey  @James 

I finally got around to trying this today and it seems like it's not saving or respecting my 'host' setting.

Here you can see I've setup and saved the canvasAPI.gs file:

Screen Shot 2015-12-04 at 4.53.18 PM.png

I've entered my token as well but elected not to include that in the screenshot Smiley Happy

Then I run the discussionCount.gs script, go back to the spreadsheet and there's a blank 'Data' worksheet. When I check the logs for errors I see this:

Screen Shot 2015-12-04 at 4.55.49 PM.png

Any idea why it's trying to use "CANVAS" (in all caps just like the script comes with) rather than what I'm entering for host?

awilliams
Instructure Alumni
Instructure Alumni

Hrm, for whatever reason deleting my copy and making a new one fixed it...Leaving this here in case anyone else has the same issue.

James
Community Champion
Author

I saw that you fixed it before I could respond. What you put in there are the DEFAULT values, which is what is used the first time you run it. After that, it saves in the user properties and you can delete that line so if someone accidentally gets your file, they won't be able to do anything with it.

So, I'm guessing you ran it once before you put in the correct values and it saved the defaults that came with the file.

Starting over started with a new file and no saved values.

I really need to get that updated version released.

James
Community Champion
Author

I have done a major overhaul to this spreadsheet. I kept the same URL because several people have linked to it, but the old version is no longer available.

Changes

  • There is a Canvas menu that will appear after the Google Sheets menu. All configuration is done there, there is no more need to edit the source code.
  • All discussions are included. It turns out the first version only counted top level (initial posts) and responses to the initial posts, but not nested replies beyond that. Thanks to  @peeksas ​ for directing me towards this bug.
  • It now creates a Data page that has one entry for each student/discussion combination.
  • It uses Pivot Tables to do the counting. This allows you to filter the data by role (student/teacher/ta) and by status (active/concluded) without modifying the source code.
  • Students who didn't post in a particular discussion show up as missing rather than a 0. That's an artifact of using Pivot Tables.
  • It now uses the IFRAME sandbox mode. That doesn't sound glamorous, but Google has said they will stop supporting the NATIVE mode that I was using on June 16, 2016, so the update was necessary to keep the program working.
  • In theory, you can add multiple courses to the spreadsheet.

​Getting Started

I will try to get an updated video made. The quick start is

  1. Make a copy of the spreadsheet that you can edit
  2. Choose the Canvas > Configure API Settings menu item and put in your Canvas Instance and Access Token.
  3. Choose the Canvas > Specify Course menu item and enter your Canvas Course ID
  4. Choose the Canvas > Refresh All Discussions menu item. This will wipe out any existing data and count all the discussions in your course.

There is also a Canvas > Append New Data menu item. This will skip any discussions previously analyzed and only fetch new discussions (it will not fetch new posts to old discussions). This is useful if you run the report several times a term or you want to fetch data for multiple courses. It is not useful for on-going discussions that have previously been analyzed.

jamely
Community Champion

I really love this tool! Thank you for putting the time into writing it up to be accessible for those with no experience with API's!

mikegdye
Community Novice

Hello James - this is great! I was about to use a script I have to pull data from the gmail notifications I have of the discussion posts into a Google sheet, but this allows me to skip the middleman, however, some of the data I would like is omitted, which brings about my questions:

Would it be easy for you to share with me how to edit to script(s) so that the data sheet also includes:

  1. Each post by each user in each discussion individually (rather than as it is currently with one row per student per discussion) so that the following can also be added:
    • The time and date stamp of the post
    • The text body of the post
      • This is mostly so that I can do a word count, but may be used for other things.

Notes:

  • I only use the normal threaded discussion, no groups
  • I am a BIG google sheets fan and use it for many thing like this, however, somehow I still think I am a between a newbie and average user of functions, formulas, and scripts.

Thank you in advance if you have the time to help Smiley Happy

sonya_corcoran1
Community Contributor

This is BRILLIANT!!! Excellent idea @jamesjones - thank you for sharing!

James
Community Champion
Author

 @mikegdye ​

I can't tell you how easy it would be because when I did it I made some other changes at the same time. This script is about providing a summary of the responses for the entire course because that was the initial request. I took it and modified it to provide details for a single discussion. It provides a summary for each student on one sheet and the details for each post on a second sheet, but only for one discussion at a time. If you happen to have admin rights, it will also attempt to fetch the number of messages the student didn't read and the number of likes they gave (instead of just the ones they received).

I've been using it for a year and started working on incorporating group work around Thanksgiving, but I don't have time to develop it further right now and it's not ready for release. I believe what I have is functional and may do what you need and I could share it with you privately, but I don't want to release it publicly yet, especially if I add group discussion support and people have to re-copy it a week or two after they start.

My latest coding endeavor was to try to move that out of a Google Sheet into a user script so that people could just click a button on the discussion page and download the same information as a CSV file. But end of semester came and now it's vacation time and then start of semester, so I wouldn't hold my breath or go on a hunger fast waiting on that one.

mikegdye
Community Novice

 @James ​

I would love a copy sent privately. I tried to send you a message directly to let you know, however, I found that I cannot message those that are not following me. I am following you, perhaps you can send me a direct message?

James
Community Champion
Author

I shared it with the Gmail address you use to log into the Canvas community, you'll probably get that message before you get this one.

mikegdye
Community Novice

Thank you very much, have a great break Smiley Happy

murphy_1527
Community Contributor

I have just recently found this thread and I cannot tell you how invaluable this tool has been.  @James ‌ thanks a million for creating these sheets. I do have a question though - is there a way to filter if students replied to the teacher's original prompt? Basically I want to distinguish between original reply and reply to peers' postings.

Thanks in advance.

James
Community Champion
Author

That information is available inside Canvas, so it could be written into the program, but that feature does not currently exist. It would be in the portion that saves the details of each response to the Data page and then you could add a filter for that to the Report page. There is a parent_id for each reply and you would need to check to see whether or not that matched the id of the original post.

My experience, as a "post-first" instructor is that for the most part, the number of replies is one less than the number of posts. Although, I suppose if your instructions are "Make a post and two replies" that someone could game the system by making three initial posts.

In the spreadsheet I use for analyzing an individual discussion, I record the parent_id as well as the replies to a reply, but immediate and threaded. That way I can see not only work the students did, but who made posts that stimulated the conversation. That's not in this report though, it was designed to just be a simple count of how many posts people had made.

Boekenoogen
Community Contributor

This is a great tool to use. We are trying to see if we can incorporate into a faculty dashboard to use. 

sally_green
Community Member

I can't believe this isn't an integrated element of Canvas. God, Canvas sux. 

jchapes
Community Explorer

Hello! Thanks for creating this tool that fills a hole that you would expect to be a native part of Canvas. It's been very useful. Nonetheless, I can't seem to get it to see group discussions related to my course. Is there a way that I can tell if I have the updated version, or if I may be missing something to include group discussions?

HalNelson
Community Member

Very cool--works great!

hanley
Community Member

Hi James--

I have used this essential tool for a few years now, and it was working this semester until a couple of weeks ago when I tried to add a new course.

If I try to download the blank Discussion Counts spreadsheet I get a read-only version; and if I try to reconfigure my API settings to re-use an old spreadsheet it won't connect to my new course. (I get a failure to connect message, and then if I try to add a specific course it says something like "this looks like a Canvas course but it has no data.")

After many failures I tried asking it to forget my API settings. That was a bad idea; now my other course--which was working fine--won't connect either.

Any help appreciated.

RH

James
Community Champion
Author

@hanley 

As you were writing this comment, I was responding to the other one you left. I would try the things I suggested there and see if they help.

One thing I didn't mention in that thread is that tokens can expire (or admins can expire them). If you were using a token that you saved to a file a while ago, it's possible that it no longer works. You can check by going to Account > Settings and scrolling down.

bmcgeoghegan
Community Explorer

Hi James,

Is there a preferred Idea Conversation describing all the functionality missing in the Canvas discussion tool or the analysis tool that this process provides? I found Discussion post/reply counts by student for each course is recently added with little detail.

Thanks,

Bryan

steve35
Community Member

 

See comment below

 

 

steve35
Community Member

@James  @hanley 

I am also seeing an error with this Google sheet in that after entering the hostname and access token in the Canvas API Configuration dialog there is no response. Looking at the script there should at least be failure message if the "/api/v1/users/self/profile" endpoint cannot be accessed.

I think the error is with this script because I can successfully access this endpoint using the Canvas API Sheet with the same hostname and access token.

Steve

James
Community Champion
Author

@steve35 

I've had some issues with my older scripts accepting the hostname. For example, our instance is richland.instructure.com. I thought I programmed it so that you could type richland, but have found some cases where that doesn't work, but putting in the whole richland.instructure.com does. You might also try copy/pasting a URL from the course into the hostname.

I don't know if that's the problem you're experiencing, but I am pretty sure that if you do not see the confirmation message, that nothing is going to work.

James
Community Champion
Author

@bmcgeoghegan 

Sorry for the delay in responding.

Canvas is working on a discussions redesign that will change the way things work. I'm not involved in that process (it didn't seem ready for me to use in my classroom yet), but that might be a good place to look for things since that's where the development is happening.

The feature user group is called Discussions/Announcements Redesign and there is a blog post with a timeline and lots of comments. I would start looking there.

DR_EDR
Community Explorer

Help needed!

This is a fantastic resource, which I have been using for years.

But for the first time, it is not working for me.

I am stuck somewhere between steps 4 & 5.

After going to Canvas > Configure API Settings & entering my Canvas hostname & Access Token #, nothing happens. I do not get a confirmation notice window stating this has worked. Instead it just goes straight back to the spreadsheet.

Then, when I go to Canvas > Specify Course, it just asks me to configure the API settings again. It does not ask me to specify the course.

Please help! As I have come to rely heavily on this excellent spreadsheet tool.

Thanks

EDR

DeletedUser
Not applicable

Hi, @DR_EDR I might know some people who could help! 1. @Chris_Hofer  He is very helpful and is a coach (A coach means an achievement, I think. I am new here too!) 2. Groups! Groups are a great way to connect, answer and ask questions yourself! 3. Have you tried the Canvas Question Forum? It is a great way to ask Canvas itself! You can ask me anything! Just click here @DeletedUser 

Chris_Hofer
Community Coach
Community Coach

@DeletedUser ...

You can read more about those of us who are Community Coaches here:

What are the Community ranks and roles? - Instructure Community (canvaslms.com)

DeletedUser
Not applicable

Thanks!

DR_EDR
Community Explorer

Hi @qwertyqwertyqwe & @Chris_Hofer & @steve35 & @James 

Thanks for weighing in!

From @steve35 's post, it seems like maybe we are having the same problem.

@steve35 writes: "after entering the hostname and access token in the Canvas API Configuration dialog there is no response". That's also what's happening to me.

I have tried entering the host name both ways, e.g. "richland.instructure.com" & just 'richland'...

& I am using new access tokens, not old ones...

Are others having this problem as well?

And does anyone have any solution to this issue?

Many thanks for a speedy solution, as final discussion board grades need to be calculated asap.

EDR

steve35
Community Member

@DR_EDRI solved my issue by using Chrome instead of Firefox.

DR_EDR
Community Explorer

@steve35

I've tried this as well. Unfortunately, not working for me. E