【簡単にできた!】記事のはてブ数・Tweet数を一覧化!GoogleスプレッドシートでサイトダッシュボードをDIYする!

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る

Googleスプレッドシートとクラウドサービスの連携でDIY

Googleスプレッドシートはブラウザから無料で利用できるGoogle Drive内の表計算アプリケーションです。Excelと同じような表計算やグラフを表示する機能に加えて、クラウドサービスとの連携を簡単に行える関数が用意されています。

例えば「A1」セルに日本語を入れて、「B1」セルに以下の関数を入力すると「A1」に入力した文字列を英語に翻訳した結果が表示されます。

=IF(A1<>””, GOOGLETRANSLATE(A1,“ja”,“en”),“”)

一度作った関数は参照先を変えてコピーできるため、複数の単語を一気に翻訳することも可能です。(※ GOOGLETRANSLATEの詳細な利用方法はこちら)

GOOGLETRANSLATE

GOOGLETRANSLATE関数自体に翻訳機能が実装されているのではなく、内部的にはGoogleスプレッドシートからGoogle翻訳が呼び出され、その結果がセルに格納されています。GoogleスプレッドシートにはExcelを操作する感覚でクラウドサービスと連携できるという優れた特長があるのです。

そこで今回は、Googleスプレッドシートの連携機能を活用しながら、自身の運営するサイトのタイトルやSNSのシェア状況を一覧表示させるサイトダッシュボードをDIYする方法をご紹介します。

さっそくサイトダッシュボードを作り始めよう

はじめにGoogleスプレッドシートの右端にある「+」ボタンをクリックして、新しいスプレッドシートを作成。サイトの情報を所得したいRSSのURLをA1セルに貼り付けます。

スプレッドシートを作成

RSSから情報取得にはIMPORTFEED関数を利用します。B1列に以下の関数を入力することでサイトのタイトルが表示されます。(※ IMPORTFEEDの詳細な利用方法はこちら

=IMPORTFEED(A1,“feed title”)

IMPORTFEED

上記の関数を実行するとオンライン上のRSSを読み取ってタイトルが自動的に取得されます。

次に以下の関数をA2列に入力して最新ページのURLリストを取得します。

=IMPORTFEED(A1,“items url”)

items url

A2列に関数を入力するだけで下側のセルにまで値が入力されます。Excelに慣れていると戸惑うもしれませんが、Googleスプレッドシートの関数の中には入力したセルの外側に影響を与えるものもあります。

オンライン上のRSSが更新された場合は、その時点でRSSに出力されているURLのリストでGoogleスプレッドシート上のセルが自動更新されます。RSSの最大アイテム数が例えば20個である場合、Googleスプレッドシートにおいては20行のなかで更新されます。

B2列にはページのタイトルを表示させます。

=IMPORTFEED(A1,“items title”)

items title

ここまででダッシュボードを作成するための基本シートが完了しました。IMPORTFEED関数を用いることで、サイトの情報を簡単に表示できることをご理解いただけたのではないでしょうか。

Google Apps Scriptではてなブックマーク数取得関数を作ろう

ここまででURLのリストを取得することができましたため、URLに紐付いたSNSからの反応を取得していきましょう。と、いっても各種SNSの情報を自動的に取得するための関数は用意されていません。

Google Apps Scriptを利用してオリジナルの関数を作成します。Google Apps Scriptは、JavascriptがベースのGoogleスプレッドシート用のスクリプト言語で、Googleスプレッドシート用の関数を簡単に作成することができます。

メニューバーの「ツール」→「スクリプト エディタ」をクリックしてスクリプトエディタを起動します。

スクリプトエディタ

SNSにはWebAPIという仕組みが用意されており、WebAPIが指定するURLにパラメータを付与することで、値を表示させることができます。例えば、指定のURLに紐づく「はてなブックマーク」の数を表示させるAPIは以下の通りです(※はてなブックマークAPIの仕様はこちら)。

http://b.hatena.ne.jp/entry.count?url={エンコード済みURL}

上記のWebAPIをセルに自動入力されたURLを使って起動する関数を作成しましょう。

WebAPI

まずはスクリプトエディタに以下のスクリプトを追加して、WebAPIの汎用結果取得関数を作成します。

// example import_api(“http://b.hatena.ne.jp/entry.count?url=”, “http://google.com”)
function import_api(api_url, target) {
if(target == “”) {
return “”;
}
var res;
try {
res = UrlFetchApp.fetch(api_url + encodeURIComponent(target)).getContentText();
} catch(e){
return “”;
}
return res;
}

行われている処理としては、以下の通りです。

  • targetが存在しなかったら空文字を返す
  • targetの文字列をエンコード
  • api_uriとエンコード済みのtargetでWebAPIの呼び出しURLを生成
  • 呼び出しAPIの結果を返す

上記で作成した関数を利用して、はてなブックマーク数の取得を行う関数を完成させるために、下記の関数をスクリプトエディタに追加します。

function import_hateb(target) {
var api_url = “http://b.hatena.ne.jp/entry.count?url=”
var res = import_api(api_url, target)
if(res == “”) {
return 0;
}
return parseInt(res);
}

スクリプトエディタのメニューバーで「ファイル」→「保存」をクリックしてスクリプトを保存します。プロジェクト名を聞かれますが任意の名前で構いません。

スプレッドシートの画面に戻り、さきほど作成した関数を利用します。セルC2に以下の記述をします。

=import_hateb(A2)

import_hateb

以上の手順で、はてなブックマーク数を表示することができました。セルの右下をドラッグして伸ばすと参照先が自動的に変更されてブックマーク数が取得されていきます。

ブックマーク数を取得

SNSに適した方法でシェア数を取得

つづいてTwitterやFacebookのシェア数を取得したいところなのですが、Twitterからは公式のWebAPIが提供されておらず、FacebookのAPIをGoogle Apps Scriptから利用するためにはアプリケーション利用の審査や認証処理の作り込みが必要となります。

このような場合、サードパーティが提供しているWebAPIを利用することが考えられます。

count.jsoon は 株式会社 ディジティ・ミニミが運営する、Twitterに代わりWEBページのツイート数を提供するサービスです。

widgetoon.js & count.jsoon | digitiminimi

count.jsoonはTwitterが停止した指定ページのツイート数を提供してもらえるサービスです。サイトの利用申請を行うことでシェア数が収集されるようになり、下記のWebAPIで取得可能となります。

http://jsoon.digitiminimi.com/twitter/count.json?url={エンコード済みURL}

こちらのWebAPIからシェア数を取得するためのGoogle Apps Scriptは下記のとおりです。

function import_twitter(target) {
var api_url = “http://jsoon.digitiminimi.com/twitter/count.json?url=”
var res = import_api(api_url, target)
if(res == “”) {
return 0;
}
var json = JSON.parse(res);
if(json.count != undefined) {
return json.count;
} else {
return 0;
}
}

WebAPIからデータを取得するところまでは、はてなブックマークと同様ですが、結果がcount.jsoonはJSON形式で取得されるため、取得したJSONをパースして「count」の値が取れたらセルに表示するようにします。

JSONをパース

先程と同様に関数を入力することで、Twitterのシェア数を取得します。

function import_pocket(target) {
var api_url = “http://widgets.getpocket.com/v1/button?v=1&count=vertical&src=” + encodeURIComponent(target) + “&url=”;
var res = import_api(api_url, target)
if(res == “”) {
return 0;
}
var regexp = /<em id=”cnt”>(.*)<\/em>/i;
var match = regexp.exec(res);
if(match.length >= 1) {
return parseInt(match[1]);
} else {
return 0;
}
}

公式のシェア数取得方法が提供されていないサービスの場合には、それぞれに適した取得方法を調べたり、考えたりする必要がでてきます。例えば、Pocketのストック数を取得するためには、シェアボタンのHTML内に書き出された数値を解析する方法があります。

取得したデータをグラフ化しよう

サイトに公開しているページとSNSからのシェア状況を取得することとができましたので、自分好みの見栄えになるようダッシュボードを編集します。罫線やセルの色付けなどExcelと同様の見栄え編集が可能です。

Excelと同様の見栄え編集

Googleスプレッドシートにはグラフ描画機能があるため、SNSのシェア状況をグラフ化してみましょう。ダッシュボードを作成する際には数字の羅列だけでなく、推移を見える化していくことが大切です。

推移を見える化

タイトルとシェア数を選択した状態で「挿入」→「グラフ」を押下

グラフ

今回は3つのサービスのシェア数で積み上げグラフを作成しましょう。

グラフを作成

作成したグラフを広げることで、すべてのタイトルに対するシェア数の積み上げグラフが作成できます。

まとめ

以上まででサイトダッシュボードがDIYできました。サイトのRSSが更新されると、以下の更新処理が連鎖的に行われて最新状況に自動更新されていきます。

  • RSSが取得するURLが更新
  • RSSが取得するタイトルが更新
  • シェア数が更新
  • タイトルとシェア数のグラフが更新

自身で作成したダッシュボードはグラフや連携対象を増やすことや、見栄えの編集作業を自由自在に行えます。本稿を読んだ皆さんもGoogleスプレッドシートを活用して、サイトダッシュボードをDIYしてみてはいかがでしょうか。

執筆者:池田仮名

プロフ

ITエンジニア/ブロガー
個人ブログ「太陽がまぶしかったから」を運営。

Twitter:@bulldra
ブログ:「太陽がまぶしかったから」

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る