Dashboarding activity on public JISCMail lists using Google Sheets (Spreadsheets)

I’ve written a very long blog post which I’ll publish soon on text-mining public JISCMail (Listerv) lists using OpenRefine. It concludes with displaying list activity, posts over time and individual activity. The technique I used isn’t straight forward but as the output might be of benefit to other people like Brian Kelly who reported the Decline in JISCMail Use Across the Web Management Community I wondered if there was a better way of doing it. Here’s my answer:

*** JISCMail Public List Activity Overview Template ***
[Give it 30 seconds to render the results] 

JISCMail Public List Activity Overview Template

By making a copy of this spreadsheet and entering the url of the homepage of a public JISCMail List like OER-DISCUSS, it goes off and collects each months archives for almost the last 3 years, graphs the overall list activity as well as letting you see individual contributions (a limitation is matching variations in display names so in the OER-DISCUSS example Pat Lockley and Patrick Lockley get counted separately even though they are the same person).

How it works

On the data sheet cell A2 uses importXML to grab all the archive links. In cell B2 the importHTML function is used to grab the table of posts on each month’s archive page and does a QUERY to return post author names, the values being turned into a string from an array using JOIN. In cell A53 a UNIQUE list of author names (minus ‘) is generated using a combination of SPLIT and JOINS. This data is then used on the Dashboard sheet (to get the SPARKLINES I had to write a custom function using Google Apps Script.

function getActivity(user,source) {
  var output = [];
  for (i in source){
    var rows = source[i][0];
    var count = rows.match(new RegExp(user,"g"));
    if (count){
      output.push([count.length]);
    } else {
      if (source[i][0]!=""){
        output.push([0]);
      }
    }
  }
  output.reverse();
  return output;
}

If you are interested in learning more about the functions used I recently posted Feeding Google Spreadsheets: Exercises in using importHTML, importFeed, importXML, importRange and importData (with some QUERY too). You should be able to use this template with any other public JISCMail list. Any questions get in touch.

3 thoughts on “Dashboarding activity on public JISCMail lists using Google Sheets (Spreadsheets)

  1. Pingback:

  2. Pat

    Patrick Lockley and Pat Lockley are different people. Patrick Lockley was quasi-banned from the list, so Pat Lockley appeared.

    1. Post author

      Hi Pat (if that is your really name) I thought it was because there was a rip is space and time and in an alternate universe there was another Patrick Lockley on a JISCMail list taking about OER and as list owner insisted that everyone used their fully given name (fname lname) to prevent duplicate entries in the metadata ... but your reason makes me sense

Comments are closed.