cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
trf122
Community Participant

Is there a way to download all student submissions in a CANVAS discussion to Excel (or spreadsheet format)?

Jump to solution

I am collecting data from individual discussions for several purposes (e.g. word count calculation, grading, identifying trends, summarizing key themes, measuring/analyzing student to student interaction, collecting information to improve instructional design, etc.). Currently, I am manually copying and pasting name, date/time submitted, and comment into an Excel spreadsheet. This is very time consuming and prone to error. I tried a full copy/paste of the entire discussion into a Word document as well as a pdf file to see if it could be manipulated into spreadsheet form, and I wasn't successful. Any suggestions? Or is this something that should be directed to CANVAS as a new feature for vote? 

1 Solution

Accepted Solutions
kblack
Community Champion

Hi  @trf122 ‌ - You are certainly not alone in wanting a Canvas-built solution for this.  There have been a few Feature Ideas in the past that you may want to look over, specifically https://community.canvaslms.com/ideas/5356-download-discussion-board-posts and also https://community.canvaslms.com/ideas/1506-export-discussions .  Both are still open for voting.  There is also  @James ‌ excellent How to Count Student Discussion Posts .

However, I may have some good news for you in regard to your specific question.  There was a very recent post that  @jrboek   replied to that you can read here.  John points to a file that can be used with the browser extension Tampermonkey.  I just now tried John's script for the first time myself in a sample class, and I can tell you that it works like a charm.  All student posts are downloaded into a csv file from the script that runs on the page.  (John, you should really write up a blog post or document on your own that "sells" this script a bit more!)

This may help, Tonya.  Tampermonkey is easy to install as an extension to Chrome and the site that John links to in his reply will download the .js extension directly into Tampermonkey.  

View solution in original post

5 Replies
kblack
Community Champion

Hi  @trf122 ‌ - You are certainly not alone in wanting a Canvas-built solution for this.  There have been a few Feature Ideas in the past that you may want to look over, specifically https://community.canvaslms.com/ideas/5356-download-discussion-board-posts and also https://community.canvaslms.com/ideas/1506-export-discussions .  Both are still open for voting.  There is also  @James ‌ excellent How to Count Student Discussion Posts .

However, I may have some good news for you in regard to your specific question.  There was a very recent post that  @jrboek   replied to that you can read here.  John points to a file that can be used with the browser extension Tampermonkey.  I just now tried John's script for the first time myself in a sample class, and I can tell you that it works like a charm.  All student posts are downloaded into a csv file from the script that runs on the page.  (John, you should really write up a blog post or document on your own that "sells" this script a bit more!)

This may help, Tonya.  Tampermonkey is easy to install as an extension to Chrome and the site that John links to in his reply will download the .js extension directly into Tampermonkey.  

View solution in original post

trf122
Community Participant

Thank you! Got the script up and running this morning. I'll play with formatting the files later today. This was VERY HELPFUL!

trf122
Community Participant

Follow up: due to cyber security issues, Tampermonkey was not a long-term viable option. Copying and pasting into Word, running several homemade macros, and pasting into Excel for further analysis in Nvivo, Tableau and Power BI is time consuming but works. 

bec_plumbe
Community Participant

Posting here as it looks like the discussion that @kblack points to is no longer available, unfortunately.


I've used that Tampermonkey script before with great success, and was sad to find yesterday when I looked for it again that it seems to have been removed from the web, along with the user account of its creator, breid at dartmouth. A colleague had a copy so I'll post it here for anyone else who might be looking for it. I just tried it on a Canvas site and it worked fine.

// ==UserScript==
// @name Canvas-Discussions-Get_Entries
// @namespace http://www.dartmouth.edu/~breid/userscripts
// @version 1.1
// @description Get data for discussions in a Canvas course and write results to three csv files
// @author brian.p.reid@dartmouth.edu, based on work by Jing Qi (jing.qi@dartmouth.edu) and James Jones (james@richland.edu)
// @include /\S+\/courses\/\d+\/discussion_topics/
// @require http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js
// @grant GM_addStyle
// ==/UserScript==

(function() {
'use strict';
var csv_text_all = "course_id,topic_id,topic_title,discussion_type,entry_id,entry_author,entry_message,entry_word_count,reply_id,reply_author,reply_message,reply_word_count\n";
var csv_text_interactions = "from,to,weight,group,reply_message,entry_message\n";
var csv_text_topics = "from,to,weight,group\n";

var topic_results = []; // Storage for topic data
var entry_results = []; // Storage for entry data
var topic_counter = 0;
var entry_counter = 0;
var pending = -1; // used for pagination of data returned from Cavnas APIs
var course_id = getCourseId(); // getCourseID gets the ID from the current page URL
var mode = "all three files"; // Which type of output file to generate.

if ($('#cd_button').length === 0) { // Check to see if a button is already created
setTimeout(add_button,500); // Wait half a sec to let Canvas load the page before adding a button
}

function add_button(){
// Appends a button to the current page. The button will call getTopics to start the whole process.
$('#content').append('<a id="cd_button" class="btn button-sidebar-wide">Userscript: Get Discussion Entries</a>');
$('#cd_button').bind('click', function() {startProcess();});
$('#cd_button').css("margin","0.5em");
}

function startProcess(){
// Starts data collection
// Include a pop-up panel to report progress.
var panel_string = ' <div id="tmPopupContainer"> <p id="console_title">Userscript: Get Discussion Entries</p> ';
panel_string += ' <p><input type="radio" name="mode" value="all three files" checked="checked"> Generate all three output files.<br> ';
panel_string += ' <input type="radio" name="mode" value="file with all data"> Generate one file with all data.<br> ';
panel_string += ' <input type="radio" name="mode" value="topics file"> Generate one file with responses to topics.<br> ';
panel_string += ' <input type="radio" name="mode" value="interactions file"> Generate one file with interactions.<br> ';

panel_string += ' <p id="console"></p> ';
panel_string += ' <button id="tmStartBtn" type="button">Start</button> <button id="tmCloseDlgBtn" type="button">Close</button> </div> ';
$("body").append (panel_string);
$("#tmCloseDlgBtn").click ( function () {
$("#tmPopupContainer").hide ();
} );
$("#tmStartBtn").click ( function () {
getTopics ();
} );

}

function getTopics(){
// Starts data collection by calling discussion_topics api to get topics.
// saveTopicData() will be called for every page of topic data found.
// topicsRetrieved() will be called after all topic data has been retrieved.
// Include a pop-up panel to report progress. Also write progress to console.
console.log("Starting data collection in Canvas-Discussions-Get_Entries script...");
mode = $("input[name='mode']:checked").val();
$("#console").html("Starting data collection in Canvas-Discussions-Get_Entries script to get "+mode);
var api_url = "/api/v1/courses/"+course_id+"/discussion_topics?per_page=50"; // the API "endpoint"
getApiData(api_url, 0, saveTopicData, topicsRetrieved);
}

function saveTopicData(dummy_id,page_data){
// Puts topic dat in topics_results array.
Array.prototype.push.apply(topic_results,page_data);
}

function topicsRetrieved(){
// Called after the topics have been obtained and starts the process of getting the submissions for each discussion.
topic_counter = 0; // The current topic number is kept in topic_counter.
getEntries();
}

function getEntries(){
// Gets the entries for one topic; after the entries are obtained, entriesRetrieved will be called.
// writeEntries() will be called for every page of entry data found.
// entriesRetrieved() will be called after all entries been retrieved.
if (topic_counter < topic_results.length){
var api_url = "/api/v1/courses/"+course_id+"/discussion_topics/"+topic_results[topic_counter].id+"/entries?per_page=50";
getApiData(api_url, topic_counter, writeEntries, entriesRetrieved);
}
}

function writeEntries(topic_id, entry_data){
// Adds the entry data to the strings csv_text_all and csv_text_topics. Saves entry data in entry_results array.
$.each(entry_data, function(index, entry){
topic_results[topic_id].title = cleanString(topic_results[topic_id].title);
entry.user_name = formatName(entry.user_name);
entry.message = cleanString(JSON.stringify(entry.message));
csv_text_all += course_id+","+topic_results[topic_id].id+","+topic_results[topic_id].title+","+topic_results[topic_id].discussion_type+","+entry.id+",";
csv_text_all += entry.user_name+','+entry.message+','+entry.message.length+',';
csv_text_all += "-1,-1,-1,-1\n";
csv_text_topics += entry.user_name+','+topic_results[topic_id].title.substring(0,20)+','+entry.message.length+','+topic_results[topic_id].title.substring(0,20)+'\n';
entry_results.push({"topic_id":topic_results[topic_id].id,"topic_title":topic_results[topic_id].title,"discussion_type":topic_results[topic_id].discussion_type,"id":entry.id,"entry_author":entry.user_name,"message":entry.message,"length":entry.message.length});
});
}

function entriesRetrieved(topic_id){
// Keeps calling get_entries until all topics have been covered; then the process to get replies is started.
console.log("Entries from "+(topic_id+1)+" of "+topic_results.length+" topics retrieved.");
$("#console").html("Entries from "+(topic_id+1)+" of "+topic_results.length+" topics retrieved...");
topic_counter++;
if (topic_counter < topic_results.length) getEntries();
else getReplies();
}

function getReplies(){
// Gets the replies to one discussion entry; after the replies are obtained, replies_retrieved will be called.
// writeReplies() will be called for every page of reply data found.
// repliesRetrieved() will be called after all reply data has been retrieved.
if (entry_counter < entry_results.length){
var api_url = "/api/v1/courses/"+course_id+"/discussion_topics/"+entry_results[entry_counter].topic_id+"/entries/"+entry_results[entry_counter].id+"/replies?per_page=50";
getApiData(api_url, entry_counter, writeReplies, repliesRetrieved);
}
}

function writeReplies(entry_id, reply_data){
// Adds the reply data to the strings csv_text_all and csv_text_interactions.
$.each(reply_data, function(index, reply){
var reply_message = cleanString(JSON.stringify(reply.message));
csv_text_all += course_id+","+entry_results[entry_id].topic_id+","+entry_results[entry_id].topic_title+","+entry_results[entry_id].discussion_type+","+entry_results[entry_id].id+",";
csv_text_all += entry_results[entry_id].entry_author+","+entry_results[entry_id].message+","+entry_results[entry_id].length+",";
csv_text_all += reply.id+","+formatName(reply.user_name)+","+reply_message+','+reply.message.length+"\n";
csv_text_interactions += formatName(reply.user_name)+','+ entry_results[entry_id].entry_author+','+reply.message.length+','+entry_results[entry_id].topic_title.substring(0,20)+',';
csv_text_interactions += entry_results[entry_id].message+','+ reply_message+'\n';
});
}

function repliesRetrieved(entry_id){
// Calls getReplies until the final reply has been handled; then writes the results to three csv files.
console.log("Replies from "+(entry_id+1)+" of "+entry_results.length+" entries retrieved...");
$("#console").html("Replies from "+(entry_id+1)+" of "+entry_results.length+" entries retrieved...");
entry_counter++;
if (entry_counter < entry_results.length) getReplies();
else {
console.log("Downloading csv files...");
var csvData = 'data&colon;text/csv;charset=utf-8,\ufeff' + encodeURIComponent(csv_text_all);
var csvData_topics = 'data&colon;text/csv;charset=utf-8,\ufeff' + encodeURIComponent(csv_text_topics);
var csvData_interactions = 'data&colon;text/csv;charset=utf-8,\ufeff' + encodeURIComponent(csv_text_interactions);
var download_link = document.createElement('a');
download_link.style.display = 'none';
document.body.appendChild(download_link);
var report_string = "Done. Look for downloaded csv file/s: ";
if (mode == "all three files" || mode == "file with all data") {
$("#console").html("Downloading discussion-report-all_data.csv file.");
download_link.setAttribute('download', 'discussion-report-all_data.csv');
download_link.setAttribute('href', csvData);
download_link.click();
report_string += "<br> discussion-report-all_data.csv";
}
if (mode == "all three files" || mode == "topics file") {
console.log("Downloading discussion-report-topics.csv file");
download_link.setAttribute('download', 'discussion-report-topics.csv');
download_link.setAttribute('href', csvData_topics);
download_link.click();
report_string += "<br> discussion-report-topics.csv";
}
if (mode == "all three files" || mode == "interactions file") {
console.log("Downloading discussion-report-interactions.csv file");
download_link.setAttribute('download', 'discussion-report-interactions.csv');
download_link.setAttribute('href', csvData_interactions);
download_link.click();
report_string += "<br> discussion-report-interactions.csv";
}
document.body.removeChild(download_link);
$("#console").html(report_string);
}
}

// ---- Utility Routines -----

function getApiData(api_url, call_id, page_function, return_function) {
// Generic implementation of API call to Canvas using jQuery.
// api_url = url of the API!.
// call_id = integer used to identify the call.
// page_function(call_id, data) = function to call after each page of data is retrieved.
// return_function(call_id) = function to call when done.
try {
pending++;
$.getJSON(api_url, function (the_data, status, jqXHR) {
page_function(call_id, the_data); // Do something with this page of data.
api_url = nextURL(jqXHR.getResponseHeader('Link')); // Make sure we get all pages of data.
if (api_url) getApiData(api_url, call_id, page_function, return_function); // If there is a link in the header, call this routine recursively.
pending--;
if (pending < 0) return_function(call_id); // Call this function when completely done.
}).fail(function () {
pending--;
return_function(call_id);
throw new Error('Failed to get API data '+api_url);
});
}
catch (e) { console.log(e); alert(e); }
}

function nextURL(linkTxt) {
// Test for paginated data - from https://github.com/jamesjonesmath/canvancement.
var n_url = null;
if (linkTxt) {
var links = linkTxt.split(',');
var nextRegEx = new RegExp('^<(.*)>; rel="next"$');
for (var i = 0; i < links.length; i++) {
var matches = nextRegEx.exec(links[i]);
if (matches) { n_url = matches[1]; }
}
}
return n_url;
}

function getCourseId() {
// Get course ID from current URL - from https://github.com/jamesjonesmath/canvancement.
var courseId = "";
try {
var courseRegex = new RegExp('/courses/([0-9]+)');
var matches = courseRegex.exec(window.location.href);
if (matches) { courseId = matches[1]; }
else { throw new Error('Unable to detect Course ID'); }
}
catch (e) { errorHandler(e); }
return courseId;
}

function formatName(input_text){
// Format names with first name and last initial.
var space_loc = input_text.indexOf(" ");
var first_name = input_text.substring(0,space_loc);
var last_initial = input_text.charAt(space_loc+1);
return first_name+last_initial;
}

function cleanString(input_text){
// Remove commas and html tags from text
return input_text.replace(/,/g,'').replace(/<(.*?)>/g,'').replace(/\\n/g,'');
}

//--- CSS styles for pop up console log ---
// By Brock Adams
// http://stackoverflow.com/questions/11668111/how-do-i-pop-up-a-custom-form-dialog-in-a-greasemonkey-s...

var style_string = ' #tmPopupContainer { ';
style_string += ' position: fixed; ';
style_string += ' top: 30%; ';
style_string += ' left: 20%; ';
style_string += ' padding: 2em; ';
style_string += ' background: #BBEEDD; ';
style_string += ' border-radius: 5px; ';
style_string += ' z-index: 777; ';
style_string += ' } ';
style_string += ' #tmPopupContainer button{ ';
style_string += ' cursor: pointer; ';
style_string += ' margin: 1em 1em 0; ';
style_string += ' border: 1px outset buttonface; ';
style_string += ' } ';
style_string += ' #cd_button{ ';
style_string += ' background: #BBEEDD; ';
style_string += ' } ';
style_string += ' #console_title { ';
style_string += ' color: #000000; font-weight: bold;';
style_string += ' } ';

GM_addStyle (style_string);

})();

bowmanr
Community Champion

In case anyone else stumbles on here.  The post mentioned in the answer is now:

https://community.canvaslms.com/t5/Question-Forum/Can-you-please-tell-me-an-easy-way-to-download-dis...

and the script referenced is available from the link below

@bec_plumbe 

I decided to email breid at Dartmouth to see if he was still there.  He is, and he was kind enough to send a reply with information that Dartmouth changed where their local web pages were located.  Here is the new web address for the script in question and a few others:

https://breid.host.dartmouth.edu/userscripts/

Ron