#ocTEL Using Google Spreadsheets for a basic analytic to find your fledgling bloggers

One of the nice things about open courses like ocTEL is whilst having your own blogging space wasn’t a mandatory requirement we were aware that a number of participants had setup one up anyway. Conscious that activities like blogging can be very lonely and also aware that this is often a critical moment in motivating people to engage in reflective writing we’ve been encouraging tutors to show these blogs some extra love, which seems to have gone down well.
This raises the question that given blogging is optional and we haven’t asked people to self-declare if they are new to blogging how do we identify this community? With this question in mind I quickly (initial pass took me about 5 minutes thanks in part to earlier work in this area) put together a spreadsheet which took the participant blog list and got feed counts.
post table

How it was made

If you look at Sheet 1 you’ll see there is a static list of Feed urls (Column B). This was obtained by using the Chrome Screen Scrape extension. The reason it is static is I wanted a column where tutors could make comments and a dynamic import would screw this up. Looking at Sheet 2 cell A1 you can see how to do a dynamic list using the importXML function (see this post for more examples of spreadsheet import options).
To get the post counts and dates some Google Apps Script was required to write a custom function (custom function, you ask? Where have you been, read my blog). After that it was a bit of conditional formatting.

What’s missing

It would be useful to have a dynamic list of feeds which could retain comment cells (ideas on how to do this very welcome. My thought was to do a cron job to read the sheet, fetch more feeds then output the result)
Another metric it would be useful to add a comment count. So 1 post 3 comments would be identified as a lower priority than 1 post 0 comments.
So do you think this spreadsheet is potentially a useful tool in the open course toolbox? How would you improve it?

chevron_left
chevron_right

Join the conversation

comment 2 comments
  • Approaches, concepts and a few new faces | OCTEL

    […] to mention is our aim to encourage and engage with “fledgling” bloggers. Martin Hawksey has blogged about how we’re identifying such blogs. So if you have started a new or a first blog for ocTEL, we hope to find and include you! Please […]

  • Signposts | OCTEL

    […] It’s important to emphasise that this should not be taken ay any kind of pecking order of ocTEL participants. In parallel with this, and possibly more importantly, we are  working hard to support people making their first few posts. […]

Comments are closed.

css.php