Google ドライブのスプレッドシートでFacebookのデータを集めてみる

「特定サイトの複数のURLのFacebookのいいねされた数を集計してソートできないか？」と、友だちから頼まれたので、Google Apps Script（以下、GAS）で実装してみました。そこでつかった小技をメモがてらに残そうと思う。

この記事にはこんなことをざっくりと載せてます。

PHPでFacebookのいいねとかコメント数、シェア数を取得する
GASでJSONパーサー
スクリプトからスプレッドシートに書き込む
トリガーを設定して自動的に実行する
スクリプトを5分以上起動させたい時は
JavaScriptで取得したスプレッドシートを読み込んでウェブサイトに埋める

とりあえず状況。

対象のサイトの状況

特定のサイトの複数URLに対するFacebookのいいねを集計する
集計をソートして、多いもの順に閲覧したい
対象となるURLは日々増える
対象となるURLは依頼がきた段階で60件ほど

つくるべきスクリプト

そのサイトにアクセス
対象となるURLを集める
2. のURLのFacebookのいいねを取得
取得した情報を保存する
保存した情報はどこからでも見られるように。見やすいように。

こんな具合か。で、つくったスクリプトが現在うごいてるわけですが、API制限にひっかかりつつもチューニングしたおかげでなんとか安定して動いてくれそうです。

まとめてみる

Facebookのいいね数を取得するやつ

これもGASで実装・・・と思いきや、GASの動作するサーバーはFacebookから規制をかけられてて（たぶんいろんな人がつかってるから？）、取得することができません。なので仕方なくPHPをこしらえて自前のサーバーに置いときました。

Facebook Graph API

URLを受け取る
取得する

複数のURLをカンマ区切りで受け取って処理できるようにしています。ただし大量のURLはさばけないので、30件くらいずつ取得（数はテキトー）

// 受け取ったカンマ区切りのURLを配列にする
$urls = explode(",", $_POST['url']);
$return = array();
$array = array();
$len = count($urls);
for ($i = 0; $i < $len; ++$i) {
	array_push($array, $urls[$i]);
	// n 件ごとに一旦取得の処理を
	if($i > 0 && $i%30 === 0) {
		/*
			FQL文をつくる。
			[SELECT url, normalized_url, share_count, like_count, comment_count, total_count,commentsbox_count, comments_fbid, click_count FROM link_stat WHERE url="hoge1" OR url="hoge2" OR url="hoge3"…]
			SQLっぽく、複数のURLも指定できて便利なん。
		*/

		// 配列をつなげて
		$query = join('" OR url="', $array);
		// クエリにさっきのFQLを載せて
		$url = 'https://graph.facebook.com/fql?q=' . urlencode('SELECT url, normalized_url, share_count, like_count, comment_count, total_count,commentsbox_count, comments_fbid, click_count FROM link_stat WHERE url="' . $query . '"');

		// ゲットだぜ
		$json = file_get_contents($url);

		// JSONで返ってくるから適切に処理して、用意しといた配列につっこむ
		$json_array = json_decode($json, TRUE);
		foreach($json_array['data'] as $row) {
			array_push($return, $row);
		}
		// URLの配列を初期化
		$array = array();
	}
}

// 余ったURLも忘れずに処理する
if(!empty($array)) {
	$query = join('" OR url="', $array);
	$url = 'https://graph.facebook.com/fql?q=' . urlencode('SELECT url, normalized_url, share_count, like_count, comment_count, total_count,commentsbox_count, comments_fbid, click_count FROM link_stat WHERE url="' . $query . '"');
	$json = file_get_contents($url);
	$json_array = json_decode($json, TRUE);
	foreach($json_array['data'] as $row) {
		array_push($return, $row);
	}
}

/*
集めたデータは扱いやすいようにJSONで返す。
※APIと構造を合わせるため連想配列してます。
*/
echo json_encode(array('data' => $return));

ちなみにFacebookのこのAPIは公式にはリクエスト数制限は公開されておらず、リクエストを送りすぎると蹴られます。だいたい600リクエスト / 600秒との噂。

GASでJSONパーサー

さて、これでFacebookのデータを取れるようになったのでGASに移ります。

先ほどのPHPからJSONを取得して、JSONパーサーで解析する

// 取得するURLをカンマ区切りにしてPOSTデータをこしらえる
var options = {
	"method" : "post",
	"payload" : {'url': urls.join(",")}
};
// さっきのPHPへPOST
json = UrlFetchApp.fetch("sakkino.php", options);
// 返ってきたテキストを取得して、JSONパーサーを通す
res = Utilities.jsonParse(json.getContentText());
// 以上。

投げたURLは順番通り返ってくるので、 urls[0]はres.data[0]、urls[1]はres.data[1]、urls[2]はres.data[2] という構造になってるはず。

※同月30日追記 jsonParseが非推奨となり実行されなくなりました。（ドキュメント Deprecated methods 参照） JSON.parse に変わったらしい(‘A`)

取得したデータからシートに挿入するデータをつくる

var date = new Date(); // 記録用に今の日時を保持
/*
さきほどのデータをシートに入力するためのデータ（配列）に変える。
*/
var val = []; // データをいれる変数
// 複数行に同時に入力するため、配列1つが1行分のデータになる。
for(var i = 0; i < urls.length; i++) {
	val.push([
		urls[i],
		res.data[i].share_count,
		res.data[i].like_count,
		res.data[i].comment_count,
		res.data[i].total_count,
		res.data[i].commentsbox_count,
		res.data[i].click_count,
		date
	]);
}
// 以上。

データをシートに記入する。

入力するデータがそろったので、特定のシートに入力する。

// 記入するシートを取得
var sheet = SpreadsheetApp.getActive().getSheetByName('シート1');
// データの入力されてる最終行を得る
var LastRow = sheet.getLastRow();

// 入力する範囲を指定。データの列と行（行と列の数が合わなかったらエラーを吐くから注意）
// データのある次の行[A(LastRow + 1)]から、一番右（H）のデータの行数分[H(LastRow + val.length + 1)]
var range = sheet.getRange("A"+(LastRow + 1)+":H" + (LastRow + val.length + 1))
range.range.setValues(val); // 入力する

これで、スクリプトが実行されればFacebookのデータがスプレッドシートに入力されるようになった。

トリガーを設定して自動的に実行する

スクリプトを自動実行するにはトリガーを設定する。データのリアルタイム性をどこまで保つか、APIの利用制限などを考えて適切に設定しよう。今回のスクリプトは定期的に人間がチェックして状況を把握するためそこそこの更新頻度は必要なことから、30分に一度更新することにした。

スクリプトでトリガーを生成して、起動時間の制限をぶち破る

GASでスクリプトが動く時間は最大300秒。これを超えると強制的に停止される。また5分以内でも１アカウント内で１日の実行時間が長すぎると止められる模様。大量の処理を行う場合、5分では済まないことなんてザラ。スクリプト内でトリガーを生成すれば、一度処理を停止して実行し直すことになるため、この制限時間のカウントダウンをリセットして、スクリプトが停止するのを回避できる。制限時間内におわる程度の処理を書いて、最後に必要な処理がすべて終わってるかチェック。まだ処理するデータが残っているならトリガーを設定する。

// 取得した行数が最後の行に達していなければ実行
if(LastRow > GetRow) {
  // 時間ベースで60000ミリ秒（1分後）にトリガーを設定
  ScriptApp.newTrigger("run").timeBased().after(1000 * 60).create();
}

発火するタイミングはミリ秒で指定できるが、あまりに直近すぎるとトリガーが設定されるよりも先に起動するタイミングがきてしまうようで発火しなくなるので注意。 1分先を指定しておけば、だいたい動いてる。

とりあえず完成

無事に取得できてる模様。

HTMLにする

スプレッドシート上で集計したりはできるけど、もっとそれっぽく、どこからでも見れるように普通のウェブページにすることにした。

JavaScriptでスプレッドシートを読み込む

GoogleのAPIを使えば、JavaScriptでスプレッドシートの内容を読み込むことも簡単にできる。 ※ただしJavaScriptのあるページを見ている人がそのスプレッドシートを閲覧できなければ動作しない。

// APIを使う準備
google.load("visualization", "1");
google.setOnLoadCallback(function() {
	// APIが読み込まれれば実行される

	// スプレッドシートを指定する
	var query = new google.visualization.Query('https://spreadsheets.google.com/tq?key={スプレッドシートのキー}&gid=0');
	// 読み込む
	query.send(handleResponse);
	function handleResponse(response) {
		// スプレッドシートが読み込めたら実行される
		// データを取得。
		var data = response.getDataTable();
	}
}

これでスプレッドシートのデータが読み込めた。

JavaScriptで取得したスプレッドシートのデータを利用する

// データの行数分だけループ
for (var row = 0; row < data.getNumberOfRows(); row++) {
    // row 行目の0番目の列(A1)を取得する
    var A1 = data.getFormattedValue(row, 0);
    // row 行目の1番目の列(B1)を取得する
    var B1 = data.getFormattedValue(row, 1);
}

こういう具合に取得できる。あとはテーブルで出力するなりして、わかりやすく表示させるだけ。