Cntlog > 運用 > 【サンプル有】【簡単】スプレッドシートで記事のシェア数を一覧で取得する方法

【サンプル有】【簡単】スプレッドシートで記事のシェア数を一覧で取得する方法

Webサイトを運営していると、PVとかはGA(GoogleAnalytics)とかで一気にチェック出来ますが、SNSのシェア数のチェックは中々難しいですよね。

PVや離脱率も大事ですが、反響という意味でシェア数を一つの指針に加えるのはとても重要なことです。
私はシェア=心を動かされた数字として見ています。

今回はスプレッドシートを使って、SNSのシェア数を自動で取得する方法を紹介します。
とても簡単です。

スプレッドシートで取得する。

指定のURLの「ツイート数」「はてブ数」「いいね数」を取得します。
2015-08-09_23h43_57
完成品のスプレッドシート。

スプレッドシートではJavaScriptが動かせるのでWebサイト同様URLに対してAPIからの値を格納しているだけです。
ねっ、簡単でしょ。

書き方

ツールにある「スクリプトエディタ」を立ち上げて、それぞれのシェア数を取得するコードを書きます。

2015-08-18_00h28_15
コードはこちら

////////////////////////////////////
//いいね数を取得
////////////////////////////////////
function FbCount(url) {
var cache = CacheService.getPublicCache();
var cacheKey = "fb:like:" + url;
var likeNum;
likeNum = cache.get(cacheKey);
if (likeNum == null) {
var apiUrl = "http://api.facebook.com/method/fql.query?format=json&query=select+total_count+from+link_stat+where+url%3D%22";
apiUrl += url;
apiUrl += "%22";
var responseJson = UrlFetchApp.fetch(apiUrl);
var items = Utilities.jsonParse(responseJson.getContentText());
likeNum = items[0].total_count;
if (isFinite(likeNum)) {
cache.put(cacheKey, likeNum, 60 * 60 * 8);
}
}
return likeNum;
}
////////////////////////////////////
//tweet数を取得
////////////////////////////////////
function Tweets(url) {
var jsondata = UrlFetchApp.fetch("http://urls.api.twitter.com/1/urls/count.json?url=" + url);
var object = Utilities.jsonParse(jsondata.getContentText());
return object.count;
}
////////////////////////////////////
//はてぶ数を取得
////////////////////////////////////
function Hatebu(url) {
var hatena_response = UrlFetchApp.fetch("http://b.hatena.ne.jp/entry/jsonlite/?url=" + url);
if (hatena_response != 'null')
{
return JSON.parse(hatena_response)['count'];
} else {
return 0;
}
}

これでこの関数を実行させたセルに数字を取ることができますので
あとは実行させたいセルに関数を書き込みだけです。

2015-08-18_08h42_33
 

【例】C列7行のセルにURLがある場合
ツイート数を取得する場合
=IF(ISBLANK(C7),””,Tweets(C7))
はてぶ数を取得する場合
=IF(ISBLANK(C7),””,hatebu(C7))
いいね数を取得する場合
=IF(ISBLANK(C7),””,FbCount(C7))

少しコードを説明します。

=IF(ISBLANK(C7),"",FbCount(C7))

↑のコードはJavaScriptではなく、Google Apps Scriptといいます。要はエクセルと同じです。

上記のコードは「もし、C7のセルになにもなかったら、何も書かないけど、何かあったら、その値を元に”FbCount”って関数を動かすね」ってお願いをしています。

“FbCount”はいいね数を取得するって関数なので、C7にURLが書いてあれば、「このURLのいいね数を取ってくるね」ってなります。
※”FbCount”はスクリプトエディタを使って自分で作ったものです。
他の、”hatebu”、”Tweets”も同じですね。

これで一つのURLに対してのシェア数を取得する条件が整いました。

RSSフィードを使って最新記事を一覧表示する

2015-08-18_08h40_00
URL一つ一つ見るのも良いですが、用途としては直近の記事を見て比較する事が多いと思います。
記事が更新される度に、URLを追記していくのも良いですが、私はめんどくさがりなので記事一覧を作るのもプログラムに任せちゃいました。

【例】 B列3行のセルにRSSフィードのURLがあり、B列4行のセルに何件分取得するかの数字がある場合
記事タイトルの取得
=importfeed(B3,”items title”,true,B4)

記事URLの取得
=importfeed(B3,”items url”,true,B4)

上記のコードを使って、タイトルとURLを書き出して、そのシェア数を一覧することが出来ます。

完成品のスプレッドシート。

ねっ、簡単でしょ。

もっと簡単に取得する方法(WordPress編)

WordPressを使っている場合はプラグインを使ってもっと簡単に取得出来ます。
SNS Count Cache
このプラグインをいれるだけで下記5つのソーシャルシェア数が取れます。

  • Twitter
  • Google+
  • Facebook
  • Pocket
  • Hatebu

2015-08-18_00h59_08

最後に

シェア数がすべてって事はないけれど、モチベーションアップや人が反応するものの傾向を掴むためるように作りました。

スプレッドシートは最近覚え始めたのですが色々な事ができてとても面白いです。
私プログラマじゃ無いけどそれでもコピペで色々できるので、スプレッドシートの奥深さを感じています。

9割くらい自分のために作ったシートだけど誰かのお役に立てれば幸いです(*ゝω・*)。

関連記事

WEBアンケートをGoogle Driveで作って良かった... 簡単なアンケートや申し込みフォームを必要な場合は、Google Docs の「フォーム」を利用すれば簡単にできました。 気に入ったところ 簡単 すぐ作れる。 集計も取れる。 フォームのタイプが豊富 必須項目もつけれる 回答者が回答後も再編集可能な点 不満 デザイ...
Webデザイナもアクセス解析を覚えるとハッピーになれる話|Web解析 Advent Calendar... Web解析 Advent Calendar 2015の9日目の担当です。 このAdvent Calendarきちんとスケジュール通り記事書かれてなかったり、リンク切れてたり、過去の記事にリンク張っただけの人いたり、Web解析の人って自由な方多いのかしらと思い、真面目で堅物な私が書いて良いのかしらっ...
【チェックシート付】品質アップにつなげるWebサイト公開時のチェック項目... 皆さん、新規のサイトを公開するとき、チェックってしてますか? 私もチェックはするのですが、なんだかんだで漏れがあったりでミスする事がよくあるのと、人にお願いするときどこをどうチェックしてもらえばいいか頼むのに結構苦労したのでチェックシートを作りました。 私仕様の規約で作成しているので、ここの...
炎上は嫌だ…チームで使える進捗管理表(作りました)... 制作スケジュールって立てたは良いけど守れたりしてますか? そもそも気がついたら遅れてるなんて事ありませんか? 私はちょくちょくあります(´;ω;`) スケジュール管理の難しさ スケジュール管理ができなくなる原因を考えてみるとすごく単純でスケジュールが共有されていない&勝手に順調に...