RSS Feed Social Share Counting Google Spreadsheet

Sheila MacNeill recently shared Some thoughts on web analytics using our work on analytics in which she mentioned a tool I developed which allows us to quickly get social share and page view counts for out blog, category or project feeds. The solution was a quick add-on to Using Google Spreadsheets to combine Twitter and Google Analytics data to find your top content distributors, recycling some other bits of code I had lying around (mostly from Introducing a RSS social engagement tracker in Google Apps Script #dev8d, which has never really worked). The reworking is much simpler and more reliable so if you would like to extract social counts from your own (or other people’s) RSS feeds here’s:

*** RSS Feed Social Share Counter ***

If you are using this with your Google Analytics account you’ll need to authenticate access, otherwise you can just enter a RSS feed in cell B5 and see what you get back.

Social Counts/Page Views for OOH-ER

Limitations/usage notes

As JISC CETIS uses WordPress for it’s blogs it’s easy to get feeds for particular projects. For example I can get a RSS feed for the OER Visualisation Project using The spreadsheet is using the importFeed formula you’re limited to 20 items. Usually this is enough for us as most project feeds fairly go over that.  How meaningful are the numbers? At the end of the day page views probably have the most weight (tweets are cheap ;), I do find social counts useful as a way to find posts worth further investigation to find out who said what when – further work is required though to turn that data into actionable decisions.


Join the conversation

comment 14 comments
  • How JISC CETIS dashboard social activity around blog posts using a splash of data science JISC CETIS MASHe

    […] Collecting social share counts is something I’ve written about a couple of times so I won’t go into too much detail. The challenge is this project was to get results back from over 100 urls. I’m still not entirely sure if I’ve cracked it and I’m hoping more result caching helps. The issue is because the custom formula used in this appears in each row of column H on the Data sheet, when the spreadsheet opens it simultaneously tries to do 100 UrlFetches at the same time which Apps Script doesn’t like (Err: Service has been invoked too many times). {I’m wondering if away around this would be to set a random sleep interval (Utilities.sleep(Math.floor(Math.random()*10000))… just tried it and it seems to work} […]

  • adam

    Just trying to implement this for our website (love the idea!). It’s getting all the information in columns a-d fine, but I’m getting ‘unknown function name’ in columns f and q, and columns g-p are blank. Any ideas? Thanks!

    • Martin Hawksey

      My guess is Google didn’t make a complete copy of the spreadsheet. If you open Tools > Script editor and it’s blank or just has function myFunction() then I’d try making a fresh copy of th template. If that deosn’t work let me know

      • adam

        Thanks… you’re right, it hasn’t copied properly… the script editor just reads: function myFunction().
        I’ve tried making 3 new copies but thr same thing has happened each time

  • adam

    Thanks Martin – that’s working great now. Really useful! Are there any simple instructions we can follow to get the page views column working as well?

    • adam

      …sorry, I’ve poked around a little more since this comment. I’ve opened the script editor and ‘run > authenticategoogleanalytics’. It seemed to work fine and so I went back to the spreadsheet and clicked on ‘social share count’. It popped up with a message:
      ‘Select a Google Analytics account to analyse referrer data
      Google Analytics account: (our account)’
      …all the information seems correct and I can save that configuration, but I still get an error in that column.
      If I go back into the script editor and run ‘getSharedCount’, ‘getGAPageViews’ or ‘extractlink’ I get the error:
      TypeError: Cannot call method “match” of undefined. (line 124)
      and if I run twDate I get the error:
      Cannot find method formatDate(. (line 228)

      • Martin Hawksey

        hmmm match and formatDate are built-in apps script functions I’m at a lose as why they wouldn’t work unless it was temporary server issues at google

Comments are closed.