How to Count Student Discussion Posts

Navigator
52 33 28.8K

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.

33 Comments
Community Team
Community Team

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

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

Community Team
Community Team

james@richland.edu, 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?

Navigator

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

Community Coach
Community Coach

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

Community Team
Community Team

james@richland.edu​, 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.

Navigator

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.

Community Team
Community Team

Thanks, james@richland.edu. 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.

Community Coach
Community Coach

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!

Navigator

kelley.meeusen@cptc.edu​, 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?

Surveyor

Great Post james@richland.edu​. 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​ ?

Community Coach
Community Coach

james@richland.edu​:

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

Navigator

clong@hbuhsd.edu​, 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.

Navigator

kelley.meeusen@cptc.edu​, kona@richland.edu​ 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.

Surveyor

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:

Community Team
Community Team

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

Community Team
Community Team

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

Explorer III

This is fantastic!

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

Navigator

milesl@berkeley.edu​,

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.

Community Member

Hey james@richland.edu

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?

Community Member

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.

Navigator

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.

Navigator

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@jmu.edu​ 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.

Community Member

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!

Community Member

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

Community Member

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

Navigator

mikegdye@gmail.com

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.

Community Member

james@richland.edu

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?

Navigator

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.

Community Member

Thank you very much, have a great break Smiley Happy

Surveyor

I have just recently found this thread and I cannot tell you how invaluable this tool has been. james@richland.edu‌ 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.

Navigator

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.

Learner II

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

About the Author
I'm James Jones. The new Community software Khoros doesn't seem to like people using real names, but I think that names are important part of building community. I'm here trying to make Canvas a better experience for people. I hate repetitive tasks and will spend 13 hours writing a computer program to automate something that takes 5 minutes to do. The last two statements often benefit others in the form of Canvancements, which are my Canvas Enhancments that I contribute to the Canvas Community.