ケイノスの日記

2010-01-13 PHPからGoogleAppsのGoogle SpreadsheetsをDBとして利用する

何度か言及しているのですが、Google SpreadsheetsをWEBサイトのデータ更新に利用すると便利です。

つまり、データをGoogleスプレッドシートに記載・管理して、PHPでそれを読み込んで表示する方法です。

Google Spreadsheetsは、オンライン版Excelのようなものなので、WEBサイトのメンテナンス（更新作業）をされる方が、Excelに慣れた人であればBlogシステムをCMSとして使うよりも楽に導入できます。

（※この記事はGoogleAppsを中心としていますが、Googleアカウント/GMailアカウントのGoogleSpreadsheetでも使えます）

APIを使わない簡単な導入例

メアド一覧などの個人情報ではない、ニュースやお知らせといった、そもそも元データを覗かれても問題ないものであれば、GoogleAPIを使わずCSV形式でスプレッドシートを公開し、それを処理する方法が一番手軽だと思います。（GoogleアカウントやGoogleAppsのアカウントのパスワードが脆弱でないことが前提ですが）

更新データを記載した、SpreadsheetをCSV形式で公開する。
ワークシートのデータを配列で取得する。
$aData = str_getCsv( file_get_contents( "CSV形式のURL" ) );
$aDataを処理してWEBサイトに反映させる。

※もちろんXML形式で公開して、SimpleXMLなどでクラスで取得してもOKだと思います。

メリットとしては、手軽であることですが、いくつかデメリットがあります。

１つは、更新時のレスポンスです。更新しても公開先のCSVに反映されるのに若干ライムラグがあるため、せっかちなお客さんは「反映されない」とクレームを言います。その場合は、手動ですぐに反映（再公開）させます。

2つ目は、リクエストが多い場合のスプレッドシートの反応です。リクエストが多いと、たまにスプレッドシート側のレスポンスが悪くなる時がありタイムアウトしたりします。毎回スプレッドシートをチェックしに行くのではなく、キャッシュさせてGoogle側のサーバ負荷を減らす事も検討する必要があります。

最後に、３つ目が重要なのですが、秘密裏というかURLの予測が難しいとはいえ、一般公開には変わりがないわけですから、スプレッドシートのURLがバレてしまえば元データが丸々見えてしまいます。

そのため、認証なしに見えてしまうと困る情報は、この方法では絶対に処理しないこと。

しかし、公開可能な情報であっても元データが見られるのは気持ちが悪いということもあるかと思います。そうなると、スプレッドシートを非公開のまま、API経由で認証してからデータを取得するのが正攻法というものです。

Google Spreadsheets APIを使った方法 (ClientLogin版）

PHPからGoogleAPIを使ってGoogleApps上のデータを取得する記事を何件か書いているのですが、もう少し具体的なスクリプトが欲しいと要望がありました。

どうやら「Zend_Gdataクラス群の各サービスにログインまでは出来ても、特定データを読み書きできない」とのこと。アチキも全てのメソッドを把握しているわけではないのですが、「このスクリプトだけでとりあえず動く」というスクリプトを用意したので、ご覧ください。

また、情報を取得するためには認証を行う必要があるのですが、下記２通りの認証方式があります。

"Client Login"方式：スクリプトにパスワードを埋め込んで認証トークンを取得する方式
"Authsub"方式：ユーザをGoogleのログイン画面に一度飛ばし、ログインしてもらった後、元の画面に戻って来てもらうことで認証トークンを取得する方式

いずれも、認証トークンが取得できれば、データも取得できるので、今回はシンプルな"ClientLogin"で説明します。

※詳しい内容は「PHPでGoogle Apps APIにClientLogin接続して情報を取得する」記事に書いたので、もう少し掘り下げたい方はご一読ください。

GoogleAppsのSpreadsheetにAPIを使ってPHPでデータを書き込むサンプル

■前提

Zend_Gdataをダウンロードして、PHPのinclude_pathを通しておく。

■DBがわりとなるGoogleスプレッドシートの準備

新規スプレッドシートを作成します。
1行目の各列に"col1","col2","col3","col4","col5"と列のタイトルを入れます。
ワークシート名はデフォルトの「シート1」のままでいいです。
「サンプルスプレッドシート1」という名前でスプレッドシートを作成、保存します。

※これはサンプルなので、ここで準備している名前と別のものにしたい場合は、スクリプト内の該当箇所も適宜置き換えて下さい。

■大きな流れ

Zend_Gdataクラスを読み込む。
認証トークン(認証済みHTTPクライアントのオブジェクト）を作成する。 ($oClient)
GoogleSpreadsheetAPIを利用するオブジェクトを認証トークンを使って作成する。
$oSpreadsheet = new Zend_Gdata_Spreadsheets( $oClient );
書き込むデータを配列で用意する。 ($aInputdata)
スプレッドシート名からスプレッドシートのキーを取得する。 ($sSpreadsheetKey)
ワークシート名からワークシートのIDを取得する。 ($sWorksheetId)
Spreadsheetオプジェクトのメソッドを使ってデータを最後尾に挿入する。
$oSpreadsheet->insertRow( $aInputdata, $sSpreadsheetKey, $sWorksheetId );

注意点として、このサンプルスクリプトでは、スプレッドシートのキーを取得するために、ドキュメント一覧にある全てのスプレッドシート情報を取得し、１つ１つチェックして、指定されたスプレッドシート名と同じものが見つかるまで繰り返しますので、すでにスプレッドシートのキーがわかっている場合は、コードはもっと短くなりますし動作も速くなります。

（スプレッドシートのキーは、スプレッドシートのURLのクエリに記載されています）

<?
require_once 'Zend/Loader.php';
Zend_Loader::loadClass('Zend_Gdata');
Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');

/* 書き込みたいスプレッドシートの情報 */
//スプレッドシート名
$sName_spreadsheet = "サンプルスプレッドシート1";
//ワークシート（タブ）名
$sName_worksheet   = "シート1";
// 書き込みたいデータ(１行ぶん。列名=>データ)
$aInputdata = array(
      "col1" => "sample data1",
      "col2" => "サンプルデータ2",
      "col3" => "sample data3",
      "col4" => "サンプルデータ4",
      "col5" => "表示機能テスト",
);

/* 書き込み前の認証準備 */
// ClientAuth 認証用のパラメータ
$sEmail    = "your-googleaps@email.com"; //メアド
$sPassword = "your-pa$$word"; //パスワード
$sService  = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;

// 認証済みHTTPクライアント作成(ClientLogin版)
$oClient = Zend_Gdata_ClientLogin::getHttpClient($sEmail, $sPassword, $sService);

// Spreadsheetサービスのインスタンス作成
$oSpreadsheet = new Zend_Gdata_Spreadsheets($oClient);

/* 書き込み先のスプレッドシートのキーとIDの検索 */

// スプレッドシートのキー検索
//フィード(XML形式のスプレッドシート一覧）の取得
$oSpreadsheetFeed = $oSpreadsheet->getSpreadsheetFeed();
// スプレッドシートのキー取得
$i = 0;
foreach($oSpreadsheetFeed->entries as $oSpreadsheetEntry) {
    if( $oSpreadsheetEntry->title->text===$sName_spreadsheet) {
        $aKey = split('/', $oSpreadsheetFeed->entries[$i]->id->text);
        $sSpreadsheetKey = $aKey[5];
        //echo("スプレッドシートのキー:{$sSpreadsheetKey}<br>");
        break;
    }
    $i++;
}

/* ワークシートのID取得 */
// クエリの作成
$oDocumentQuery = new Zend_Gdata_Spreadsheets_DocumentQuery();
$oDocumentQuery->setSpreadsheetKey($sSpreadsheetKey);

// ワークシートフィードの取得
$oSpreadsheetFeed = $oSpreadsheet->getWorksheetFeed($oDocumentQuery);

// ワークシートの検索
$i = 0;
foreach($oSpreadsheetFeed->entries as $oWorksheetEntry) {
    $worksheetId = split('/', $oSpreadsheetFeed->entries[$i]->id->text);
    if( $oWorksheetEntry->title->text===$sName_worksheet ){
        $sWorksheetId = $worksheetId[8];
        //echo("ワークシートのID:{$sWorksheetId}<br>");
        break;
    }
    $i++;
}
// １行ぶんのデータを最後尾に挿入（追加）する
echo ( $oSpreadsheet->insertRow($aInputdata, $sSpreadsheetKey, $sWorksheetId) ) ? "処理結果:OK<br>" : "処理結果:NG<br>";
?>

スプレッドシートのキーとワークシートのIDがわかっている場合のPHPソースコード

<?
  require_once 'Zend/Loader.php';
  Zend_Loader::loadClass('Zend_Gdata');
  Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
  Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');

/* 書き込み前の認証準備 */
// ClientAuth 認証用のパラメータ
  $sEmail    = "your-googleaps@email.com"; //メアド
  $sPassword = "your-pa$$word"; //パスワード
  $sService  = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
// 認証済みHTTPクライアント作成
  $oClient = Zend_Gdata_ClientLogin::getHttpClient($sEmail, $sPassword, $sService);
// Spreadsheetサービスのインスタンス作成
  $oSpreadsheet = new Zend_Gdata_Spreadsheets($oClient);
// 書き込みたいデータ(１行ぶん。列名=>データ)
  $aInputdata = array(
      "col1" => "sample data1",
      "col2" => "サンプルデータ2",
      "col3" => "sample data3",
      "col4" => "サンプルデータ4",
      "col5" => "表示機能テスト",
  );
//スプレッドシートのキー
  $sSpreadsheetKey = "tdmuG************QpsFyw";
//ワークシートのID
  $sWorksheetId    = "od*";
//１行ぶんのデータを最後尾に挿入（追加）する
  echo ( $oSpreadsheet->insertRow($aInputdata, $sSpreadsheetKey, $sWorksheetId) ) ? "OK" : "NG";
?>

KEINOSの日記