Google スプレッドシートのデータを PHP で統合する: 第 2 回

PHP を使用して Google スプレッドシートのデータ・フィードを処理し、Web アプリケーションに統合する

Google スプレッドシートは、よく知られているクラウド・ベースのスプレッドシート・アプリケーションです。Web アプリケーション開発者は、Google Spreadsheets Data API を使用してオンライン・スプレッドシートにアクセスし、データを検索できるようになっています。この記事では Google Spreadsheets Data API を取り上げ、この API を PHP アプリケーションのコンテキストで使用する例として、スプレッドシートのさまざまな要素を追加、変更、そして削除する方法を説明します。

はじめに

よく使われる頭文字語

  • API: Application Program Interface
  • DOM: Document Object Model
  • GUI: Graphical User Interface
  • PDO: PHP Data Object
  • REST: Representational State Transfer
  • RSS: Really Simple Syndication
  • SQL: Structured Query Language
  • URL: Uniform Resource Locator
  • XML: Extensible Markup Language

この連載の第 1 回では、Google Spreadsheets Data API を紹介しました。この REST API を使用すれば、Google スプレッドシート・クラウドにホストされた、ユーザー所有のスプレッドシート・データを使用した新しいアプリケーションを容易に構築することができます。第 1 回ではスプレッドシート・フィード、ワークシート・フィード、リスト・フィード、そしてセル・フィードの基本的な内容についても説明し、これらのフィードを使って、Zend Framework でスプレッドシートの内容を素早く効率的に PHP アプリケーションに取り込む具体的な方法を紹介しました。

けれども、Google Spreadsheets Data API がサポートするのはスプレッドシートの取得と検索だけではありません。記事の締めくくりとなる今回は、この Data API を使用して、スプレッドシートの内容をリモートから操作する方法を説明します。具体的には、スプレッドシートの行、セル、ワークシートをリモート PHP アプリケーションから追加、更新、そして削除する方法です。記事で概説する手法を具体的に理解できるように、この記事では Data API を使用して RSS フィード、SQL 結果セットをそれぞれ読み取って Google スプレッドシートにインポートする 2 つのサンプル・アプリケーションを取り上げます。


ワークシートを追加する

すべてのスプレッドシートは、1 つ以上のワークシートで構成されます。Google Spreadsheets Data API を使えば、スプレッドシートのワークシートにプログラムによってアクセスし、操作することができます。これから、その方法を説明するので、Google スプレッドシート・サービスにアクセスしてログインし、空のスプレッドシートを作成してください。その際、スプレッドシートのキーを忘れずに書き留めておいてください (キーは URL に含まれています)。空のスプレッドシートは、図 1 のように表示されます。

図 1. 新しい空のスプレッドシート
新しい空の Google スプレッドシートのスクリーン・キャプチャー

このスプレッドシートに新しいワークシートを追加するには、新しいワークシートのエントリーを Atom フォーマットで作成し、そのエントリーを指定のスプレッドシートのワークシートの URL に POST 送信します。リスト 1 に、このプロセスを示します。

リスト 1. 新規ワークシートの追加
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Creating worksheets</title>
    <style>
    body {
      font-family: Verdana;      
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get spreadsheet entry
      $ssEntry = $service->getSpreadsheetEntry(
        'https://spreadsheets.google.com/feeds/spreadsheets/ssid');

      // get worksheet feed for this spreadsheet
      $wsFeed = $service->getWorksheetFeed($ssEntry);

      // create new entry
      $doc  = new DOMDocument();
      $doc->formatOutput = true;
      $entry = $doc->createElement('atom:entry');
      $entry->setAttributeNS('http://www.w3.org/2000/xmlns/' ,
        'xmlns:atom', 'http://www.w3.org/2005/Atom');
      $entry->setAttributeNS('http://www.w3.org/2000/xmlns/' ,
        'xmlns:gs', 'http://schemas.google.com/spreadsheets/2006');
      $doc->appendChild($entry);

      // add title, row and column counts
      $title = $doc->createElement('atom:title', 'Jan 2011');
      $entry->appendChild($title);
      $rows = $doc->createElement('gs:rowCount', '10');
      $entry->appendChild($rows);
      $cols = $doc->createElement('gs:colCount', '10');
      $entry->appendChild($cols);

      // insert entry
      $entryResult = $service->insertEntry($doc->saveXML(), 
        $wsFeed->getLink('self')->getHref());
      echo 'The ID of the new worksheet entry is: ' . $entryResult->id;

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>

  </body>
<html>

このリストの始まりの部分では、Zend_Gdata_Spreadsheets コンポーネントがワークシートを追加または更新するネイティブ・メソッドを (まだ) 指定していないことに注意してください。ネイティブ・メソッドを指定するのは、今からでも可能です。それには、XML ワークシート要素を手動で作成し、汎用の insertEntry() メソッドを使って、この要素を正しい URL に POST 送信します。

リスト 1 はまず、指定のスプレッドシート・エントリーへの参照を取得し、それからそのエントリーのワークシート・フィードを取得します。次に、実際のエントリーを表現する DOMDocument オブジェクトを作成して、このオブジェクトにワークシートのタイトル、行の数、列の数をそれぞれ指定する要素を追加します。最後に、insertEntry() メソッドでこのワークシート・フィードの URL に XML フラグメントを送信することによって、変更をスプレッドシートにコミットします。

上記のスクリプトを実行してから Google スプレッドシート GUI に戻ると、空のスプレッドシートに新しく作成したワークシートが含まれているはずです (図 2 を参照)

図 2. 新しいワークシートが追加されたスプレッドシート
「Jan 2011」というラベルが付けられた新規ワークシートが追加されたスプレッドシートのスクリーン・キャプチャー。

このタスクを行うには、Zend_Gdata_Spreadsheets_WorksheetEntry オブジェクトのインスタンスを作成してそのプロパティーを設定し、このエントリー・オブジェクトをワークシート・フィードの URL に POST 送信するという方法もあります。最終的な結果は同じですが、この方法に従ったほうが、いくらか作業が単純になります。それは、ワークシート・エントリー XML を生成する際の詳細は、Zend_Gdata_Spreadsheets に任せられるためです。リスト 2 では、このもう 1 つの方法によってリスト 1 と同等の出力を生成しています。

リスト 2. 新規ワークシートの追加
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Creating worksheets</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get spreadsheet entry
      $ssEntry = $service->getSpreadsheetEntry(
        'https://spreadsheets.google.com/feeds/spreadsheets/ssid');

      // get worksheet feed for this spreadsheet
      $wsFeed = $service->getWorksheetFeed($ssEntry);

      // create new entry
      $wsEntry = new Zend_Gdata_Spreadsheets_WorksheetEntry();
      $title = new Zend_Gdata_App_Extension_Title('Jan 2011');
      $wsEntry->setTitle($title);
      $row = new Zend_Gdata_Spreadsheets_Extension_RowCount('10');
      $wsEntry->setRowCount($row);
      $col = new Zend_Gdata_Spreadsheets_Extension_ColCount('10');
      $wsEntry->setColumnCount($col);

      // insert entry
      $entryResult = $service->insertEntry($wsEntry, 
        $wsFeed->getLink('self')->getHref());
      echo 'The ID of the new worksheet entry is: ' . $entryResult->id;

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>

  </body>
<html>

リスト 2 は、ワークシートのタイトル、行の数、列の数のそれぞれに対応する Zend_Gdata_Spreadsheets_Extension オブジェクトを作成した後、これらのオブジェクトを基底オブジェクト Zend_Gdata_Spreadsheets_WorksheetEntry に追加します。この手法は理解しやすいだけでなく、実装する手間も少なくなります。最終的なエントリー・オブジェクトをワークシート・フィードの URL に送信するために使用するのは、前に説明した insertEntry() メソッドです。


ワークシートを更新、削除する

Google Spreadsheets Data API は、ワークシートの追加だけでなく、プログラムによるワークシートの更新と削除もサポートします。更新操作と削除操作ではいずれも、ユーザーが更新または削除対象のワークシート・エントリーを取得し、そのエントリーを必要に応じて変更してから、変更を実行するためにサーバーにエントリーを戻すという作業が必要になってきます。ワークシート・エントリーが更新されるのか、削除されるのかは、リクエストの性質 (PUT または DELETE) によって決まります。

この操作がどのように実行されるかを確認するには、リスト 3 を見てください。リスト 3 は、リスト 1 で作成したワークシートのタイトルを更新する例です。注意する点として、このリストを含む以降のリストでは、ワークシートのキーが必要です。ワークシートのキーは、スプレッドシート・フィード、または insertEntry() メソッドによって返されたエントリー・オブジェクトから入手することができます。

リスト 3. ワークシートの更新
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Updating worksheets</title>
    <style>
    body {
      font-family: Verdana;      
    }
    table, td {
      border: 1px solid black;
      vertical-align: top;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get worksheet entry
      $query = new Zend_Gdata_Spreadsheets_DocumentQuery();
      $query->setSpreadsheetKey('ssid');
      $query->setWorksheetId('wsid');
      $wsEntry = $service->getWorksheetEntry($query);
      $title = new Zend_Gdata_App_Extension_Title('Feb 2012');
      $wsEntry->setTitle($title);

      // update entry
      $entryResult = $service->updateEntry($wsEntry, 
        $wsEntry->getLink('edit')->getHref());
      echo 'The ID of the updated worksheet entry is: ' . $entryResult->id;

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>

  </body>
<html>

リスト 3 は、前の 2 つの例でも使用した getWorksheetEntry() メソッドを使用して、特定のワークシート・エントリーをその一意の ID によって取得します。続いてエントリー・オブジェクトの setTitle() メソッドを使用してワークシートのタイトルを変更し、変更後のエントリーを updateEnry() メソッドによって返します。更新されたエントリーの送信先は、エントリー・オブジェクトが指定する編集用の URL でなければならないことに注意してください。

このプロシージャーを実行すると、Google スプレッドシート GUI には更新された状態のワークシートが表示されます (図 3 を参照)。

図 3. 新しいワークシートが更新されたスプレッドシート
新規ワークシートの名前が「Feb 2012」に更新されたスプレッドシートのスクリーン・キャプチャー

ワークシートを削除するには、対応するワークシート・エントリーの delete() メソッドを呼び出すだけでよいのです。リスト 4 に、リスト 3 のワークシートを削除する例を示します。

リスト 4. ワークシートの削除
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Deleting worksheets</title>
    <style>
    body {
      font-family: Verdana;      
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {  
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // get worksheet entry
      $query = new Zend_Gdata_Spreadsheets_DocumentQuery();
      $query->setSpreadsheetKey('ssid');
      $query->setWorksheetId('wsid');
      $wsEntry = $service->getWorksheetEntry($query);

      // delete entry
      $service->delete($wsEntry->getLink('edit')->getHref());
      echo 'The worksheet entry has been deleted';

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

前の例と同じく、このスクリプトはワークシート・エントリーを取得した後、delete() メソッドを呼び出して DELETE リクエストをエントリーの編集用の URL に送信します。このリクエストを Google API サーバーが解釈して実行することにより、ワークシートがスプレッドシートから削除されます。このステップの結果は図 4 を見れば明らかです。このように、スプレッドシートは元の状態に戻っています。

図 4. 新しいワークシートが削除された後のスプレッドシート
新しいワークシートが削除された後のスプレッドシートのスクリーン・キャプチャー

ワークシートに行を追加する

Google Spreadsheets Data API では、ワークシートに行を追加することもできますが、この場合、Zend_Gdata_Spreadsheets コンポーネントには行の追加操作を行うネイティブ・メソッドはありません。行を追加するには、その行を表す新規 Zend_Gdata_Spreadsheets_ListEntry オブジェクトを作成して、そのオブジェクトに値を取り込んでから、リスト・フィードの URL に POST 送信します。リスト 5 に一例を示します。

リスト 5. ワークシートへの行の追加
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Adding worksheet rows</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // create row content
      $row = array(
        "date" => "24-12-2010", 
        "task" => "Server reconfiguration", 
        "hours" => "3.5"
      );

      // insert new row
      $entryResult = $service->insertRow($row, $ssKey, $wsKey);
      echo 'The ID of the new row entry is: ' . $entryResult->id;
      
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

リスト 5 は対象のスプレッドシートとワークシートを設定した後、列と値のマッピングを含めて行データの配列を作成します。この配列を insertRow() メソッドに渡すと、このメソッドによって必要なエントリー・オブジェクトが作成されてフィード URL に POST 送信されます。図 5 に、Google スプレッドシート GUI での結果を示します。

図 5. 新しい行が追加されたワークシート
新しい列見出しと新しいデータ行が追加されたスプレッドシートのスクリーン・キャプチャー

行をプログラムによって追加するときには、以下の重要な注意事項があります。

  • 対象のワークシートがすでに存在していて、見出し行のフィールドが定義済みでなければなりません。
  • insertRow() に渡す行の配列には、対象のワークシートの列に対応するキーを組み込む必要があります。単に値を提供するだけでは不十分です。値だけを提供した場合、名前空間エラーが発生します。
  • 対象のワークシートの見出し行と一致するキーを持つフィールドのみが挿入されます。
  • 行の配列のキーは大文字にしないでください。大文字にすると、通常はサーバー・エラーが発生し、行の挿入に失敗します。

ワークシートの行を更新、削除する

Data API を使用して行を更新、削除することも可能です。その場合、変更する行エントリーを取得して変更してから、リスト・フィード URL に返します。リクエストが PUT として送信されるか、または DELETE として送信されるかにより、行が変更されるか、あるいはワークシートから削除されるかが決まります。

リスト 6 に、ワークシートの行を更新するプロセスを示します。

リスト 6. ワークシートの行の更新
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Updating worksheet rows</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get rows matching query
      $query = new Zend_Gdata_Spreadsheets_ListQuery();
      $query->setSpreadsheetKey($ssKey);
      $query->setWorksheetId($wsKey);
      $query->setSpreadsheetQuery('salesunits > 25000')
      $listFeed = $service->getListFeed($query);

      // iterate over matching rows
      // increase sales units by 50%
      // write updated rows back to spreadsheet
      foreach ($listFeed as $listEntry) {
        $rowData = $listEntry->getCustom(); 
        $newRow = array();
        foreach($rowData as $field) {
          $newRow[$field->getColumnName()] = $field->getText();
          if ($field->getColumnName() == 'salesunits') {
            $newRow[$field->getColumnName()] = $field->getText()*1.5;
          }
        }
        $entryResult = $service->updateRow($listEntry, $newRow);
      }
      echo 'Rows successfully updated.';

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

リスト 6 で作成しているリスト・クエリーは、ワークシートの行のうち、特定の値を上回る売り上げ個数が含まれるすべての行をリスト・フィードとして返します。このクエリーによってリスト・フィードが返されると、スクリプトはそのリスト・フィードを繰り返し処理し、各行を順に処理して行ごとの特定のセルの値を調整します。そして updateRow() メソッドにより、更新後の行がスプレッドシートに書き込まれるという仕組みです。

図 6 に、更新前と更新後のスプレッドシートを示します。

図 6. 一部の行が更新されたワークシート
一部の行が更新されたワークシートのスクリーン・キャプチャー

行を削除する場合のプロセスも上記と同様ですが、ただし、サービス・オブジェクトの updateRow() メソッドを呼び出す代わりに、行エントリーの delete() メソッドを呼び出すという点が異なります。リスト 7 に、指定のワークシートの最後の行を削除する例を示します。

リスト 7. ワークシートの行の削除
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Deleting worksheet rows</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get list feed
      $query = new Zend_Gdata_Spreadsheets_ListQuery();
      $query->setSpreadsheetKey($ssKey);
      $query->setWorksheetId($wsKey);
      $listFeed = $service->getListFeed($query);  

      // get and delete last row
      $lastEntry = $listFeed->offsetGet($listFeed->count()-1);
      $lastEntry->delete(); 
      echo 'Row successfully deleted.';

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

図 7 に、更新前と更新後のスプレッドシートを示します。

図 7. 1 つの行が削除されたワークシート
1 つの行が削除されたワークシートのスクリーン・キャプチャー

サンプル・アプリケーション: RSS コンバーター

行を追加する方法と削除する方法がわかったところで、これらの操作を実演する単純なアプリケーションを作成してみましょう。これから作成するのは、RSS をスプレッドシートに変換するコンバーターです。このコンバーターはリモート RSS フィードから新着のすべてのニュース記事を取得し、フィードから記事のメタデータを抽出してスプレッドシートの行と列に配置します。

作業を開始する前に、Google スプレッドシート GUI に戻って、典型的な RSS フィードに含まれるフィールドに対応する見出し行を設定した空のスプレッドシートを新規に作成してください。このスプレッドシートは、図 8 のようになります。

図 8. 空のワークシート
列見出しがあるだけで、データが含まれていない空のワークシートのスクリーン・キャプチャー

ここでサンプル・フィードとして使用するのは、BBC の UK (United Kingdom) ニュース・フィードです。このフィードには、英国のトップ・ニュース記事の要約が含まれます。Zend Framework の Zend_Feed コンポーネントを使えばいとも簡単に、このニュース・フィードを構文解析して情報を抽出し、Data API を使ってその情報を Google スプレッドシートに書き込むことができます。リスト 8 に、そのためのコードを記載します。

リスト 8. RSS フィードからワークシートへの行のインポート
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Importing worksheet rows from an RSS feed</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');
    Zend_Loader::loadClass('Zend_Feed_Rss');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // consume RSS feed
      // insert each channel item as a spreadsheet row
      // if large feed, increase PHP script execution time
      $channel = new Zend_Feed_Rss('http://feeds.bbci.co.uk/news/uk/rss.xml');
      foreach ($channel as $item) {
          $row = array();
          $row['title'] = $item->title();
          $row['description'] = $item->description();
          $row['link'] = $item->link();
          $row['date'] = $item->pubDate();
          $entryResult = $service->insertRow($row, $ssKey, $wsKey);
      }    

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

このように、Zend_Feed コンポーネントでは極めて簡単に RSS フィードを構文解析することができます。RSS フィードの URL をオブジェクト・コンストラクターに渡すだけで、コンストラクターがフィードを取得して構文解析し、繰り返し処理できる一連のオブジェクトに変換してくれます。リスト 8 はこの処理を行っているだけに過ぎず、フィードに含まれる項目を繰り返し処理して、項目ごとに配列を作成します。そしてこの配列が、insertRow() メソッドによって行としてスプレッドシートに挿入されます。このプロセスは、RSS フィード内のすべての項目が処理されるまで続きます。

図 9 に、この処理による結果を示します。

図 9. RSS フィードから行が挿入されたワークシート
RSS フィードからデータ行が取り込まれたワークシートのスクリーン・キャプチャー

Data API では現在のところ、バッチ処理による行データの挿入を許可していないことに注意してください。ただし、行の順番が重要でなければ、複数のリクエストを並行して送信することで処理を迅速化するという方法を使うことも可能です。


ワークシートのセルの内容を変更する

ワークシートの行の内容を変更できるのと同様に、ワークシートのセルの内容についても変更することができます。ご想像の通り、セルの内容を変更するには、更新対象のセルの詳細を含めて変更したエントリーをワークシートのセル・フィードに送信します。

セルを更新する場合に使うのは、サービス・オブジェクトの updateCell() メソッドです。このメソッドが取る引数には、行のインデックス、列のインデックス、セルの値、スプレッドシートのキー、そしてワークシートのキーの 5 つがあります。行と列のインデックスは 1 から始まります。リスト 9 に、セルの更新がどのように実行されるのかを示します。

リスト 9. ワークシートのセルの更新
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Updating worksheet cells</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // update cell at row 6, column 5
      $entry = $service->updateCell('6', '5', 'Hello, world', $ssKey, $wsKey);
      echo 'Updated cell ' . $entry->getTitle()->getText() . '
'; // clear cell at row 1, column 1 $entry = $service->updateCell('1', '1', '', $ssKey, $wsKey); echo 'Cleared cell ' . $entry->getTitle()->getText(); } catch (Exception $e) { die('ERROR: ' . $e->getMessage()); } ?> </body> <html>

updateCell() を呼び出すと、セル・エントリー・オブジェクトが返されます。このオブジェクトを、特定の情報を対象に構文解析することができます。特に注目に値する点は、セル・エントリー・オブジェクトのタイトルが、更新されたセルの座標を英数字で返すことです。

図 10 に、上記のスクリプトによる出力を示します。

図 10. セルの更新結果
セルのインデックスの更新結果を示すスクリーン・キャプチャー。「Updated cell E6」、「Cleared cell A1」と示されています。

図 11 に、Google スプレッドシートの GUI での結果を示します。

図 11 セルが更新されたワークシート
セル (E6 および A1) が更新されたワークシートのスクリープ・キャプチャー

別の手段として、セル・クエリーを使用して特定のセルを返し、そのセルを setInputValue() メソッドによって更新することもできます。また、静的な値ではなく、数式を入力できることにも注意してください。リスト 10 にその一例を記載します。

リスト 10. ワークシートのセルの更新
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Updating worksheet cells</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get cell feed
      // restrict feed to single cell E4
      $query = new Zend_Gdata_Spreadsheets_CellQuery();
      $query->setSpreadsheetKey($ssKey);
      $query->setWorksheetId($wsKey);
      $query->setMinRow(4);      
      $query->setMaxRow(4);      
      $query->setMinCol(5);      
      $query->setMaxCol(5);      
      $cellFeed = $service->getCellFeed($query);  

      // get cell from query
      $cellEntry = $cellFeed->offsetGet(0); 

      // update cell value and save back to spreadsheet
      $cellEntry->getCell()->setInputValue('=B2');
      $service->updateEntry($cellEntry, $cellEntry->getLink('edit')->getHref());
      echo 'Updated cell ' . $cellEntry->getTitle()->getText();      
    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

上記の例では、セルの値が、別のセルを参照する数式に設定されています。更新後のエントリーを書き込むために使用されているのは、サービス・オブジェクトの updateEntry() メソッドです。


サンプル・アプリケーション: データベース・インポーター

これまで説明したすべての情報を踏まえた上で、別のサンプル・アプリケーションを検討します。このアプリケーションは、MySQL データベースからスプレッドシートのデータをインポートするアプリケーションです。データベース構造は、図 12 のようになっているとします。

図 12. サンプル・データベース
サンプル・データベースのスクリーン・キャプチャー
サンプル・データベースのテキストのみを表示したもの (図 12)
          title                                author
A Basket of Surprises                       Enid Blyton
A Book of Naughty Children                  Enid Blyton
A Dangerous Fortune                         Ken Follett
A Dictionary of Geography                   W.G. Moore
A Matter of Honour                          Jeffrey Arthur
A Perfect Spy                               John le Carre
A Simple Plan                               Scott Smith
A Spy At Mill Green                         Alison Prince
A Study In Scarlet                          Arthur Conan Doyle
A Time to Kill                              John Grisham
Adrian Mole: From Minor To Major            Sue Townsend
Adrian Mole: The Wilderness Years           Sue Townsend
Adventure of The Strange Ruby               Enid Blyton
Adventure Stories                           Enid Blyton
Adventure of The Wishing Chair              Enid Blyton
Adventure on Willow Farm                    Enid Blyton
Agaton Sax and The Scotland Yard Mystery    Nils-Olof Franzenn
Agent in Place                              Helen MacInnes

SQL クエリーを実行して、そのクエリーの結果セットの各フィールドを基にスプレッドシートのセルを作成するのは、ごく簡単なことです。リスト 11 に、実際の例を示します。

リスト 11. データベースからワークシートへのセルのインポート
<!DOCTYPE html 
  PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
  "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
  <head>
    <title>Importing worksheet cells from a database</title>
    <style>
    body {
      font-family: Verdana;
    }
    </style>    
  </head>
  <body>
    <?php
    // load Zend Gdata libraries
    require_once 'Zend/Loader.php';
    Zend_Loader::loadClass('Zend_Gdata_Spreadsheets');
    Zend_Loader::loadClass('Zend_Gdata_ClientLogin');

    // set credentials for ClientLogin authentication
    $user = "someuser@gmail.com";
    $pass = "somepass";

    // create PDO connection
    $dbh = new PDO('mysql:host=localhost;dbname=library', 'user', 'pass');
    $sql = "SELECT title, author FROM library";

    try {
      // connect to API
      $service = Zend_Gdata_Spreadsheets::AUTH_SERVICE_NAME;
      $client = Zend_Gdata_ClientLogin::getHttpClient($user, $pass, $service);
      $service = new Zend_Gdata_Spreadsheets($client);

      // set target spreadsheet and worksheet
      $ssKey = 'ssid';
      $wsKey = 'wsid';

      // get results from database
      // insert header row
      $rowCount = 1;
      $result = $dbh->query($sql, PDO::FETCH_ASSOC);
      for ($x=0; $x<$result->columnCount(); $x++) {
        $col = $result->getColumnMeta($x);
          $service->updateCell($rowCount, ($x+1), $col['name'], $ssKey, $wsKey);
      }
      $rowCount++;
      // insert each field of each row as a spreadsheet cell
      // if large result set, increase PHP script execution time
      foreach($result as $row) {
        $colCount=1;
        foreach ($row as $k=>$v) {        
          $service->updateCell($rowCount, $colCount, $v, $ssKey, $wsKey);
          $colCount++;
        }
        $rowCount++;
      }

    } catch (Exception $e) {
      die('ERROR: ' . $e->getMessage());
    }
    ?>
  </body>
<html>

リスト 11 はまず、必要なクラスをインクルードして、Data API のクレデンシャルを設定します。次に、MySQL データベースとの PDO 接続を作成し、作者とタイトルのリストを返すクエリーを作成します。さらに Spreadsheets Data API に接続して、認証済みの接続を作成します。

次に、このスクリプトは PDO の getColumnMeta() メソッドを使用して結果セットの列名を取得し、updateCell() メソッドによって、これらの列名を見出し行としてスプレッドシートに書き込みます。その後、結果セットを繰り返し処理し、各フィールドを個々のセルとしてスプレッドシートに書き込み、それが完了すると次の行に移るという処理を続けます。このようにして、結果セット全体の内容がスプレッドシートに移されるというわけです。

図 13 に、Google スプレッドシート GUI での結果を示します。

図 13. データベースの結果セットから生成された Google スプレッドシート
図 12 のデータベース結果セットから生成された Google スプレッドシートのスクリーン・キャプチャー

まとめ

Google Spreadsheets Data API を使えば、PHP アプリケーションから Google スプレッドシート・クラウド内にあるデータを変更することができます。ワークシート、行、およびセルの追加、変更、削除などの一般的なタスクはすべてサポートされていて、これらのタスクは Zend_Gdata を使用して実行することができます。この記事では、このすべてのタスクを行う例を記載するとともに、スプレッドシートに RSS フィードとデータベース・クエリーの結果をそれぞれインポートする 2 つのサンプル・アプリケーションを紹介しました。

記事に記載したサンプル・コードから明らかにわかるように、Google Spreadsheets Data API は、クラウド内のスプレッドシートのデータを使って新しい創造的なアプリケーションを構築することを目指す開発者にとって、強力かつ柔軟なツールとなります。現在、この Data API の開発は活発に進められています。今後、さらに興味深い機能が追加されることをご期待ください。

参考文献

学ぶために

製品や技術を入手するために

  • Zend Framework: Zend Framework をダウンロードして、よりセキュアで信頼性に優れ、幅広く利用可能な API を備えた最新の Web 2.0 アプリケーションおよび Web サービスを構築してください。
  • IBM 製品の評価版: DB2®、Lotus®、Rational®、Tivoli®、および WebSphere® のアプリケーション開発ツールとミドルウェア製品を体験するには、評価版をダウンロードするか、IBM SOA Sandbox のオンライン試用版を試してみてください。

議論するために

コメント

developerWorks: サイン・イン

必須フィールドは(*)で示されます。


IBM ID が必要ですか?
IBM IDをお忘れですか?


パスワードをお忘れですか?
パスワードの変更

「送信する」をクリックすることにより、お客様は developerWorks のご使用条件に同意したことになります。 ご使用条件を読む

 


お客様が developerWorks に初めてサインインすると、お客様のプロフィールが作成されます。会社名を非表示とする選択を行わない限り、プロフィール内の情報(名前、国/地域や会社名)は公開され、投稿するコンテンツと一緒に表示されますが、いつでもこれらの情報を更新できます。

送信されたすべての情報は安全です。

ディスプレイ・ネームを選択してください



developerWorks に初めてサインインするとプロフィールが作成されますので、その際にディスプレイ・ネームを選択する必要があります。ディスプレイ・ネームは、お客様が developerWorks に投稿するコンテンツと一緒に表示されます。

ディスプレイ・ネームは、3文字から31文字の範囲で指定し、かつ developerWorks コミュニティーでユニークである必要があります。また、プライバシー上の理由でお客様の電子メール・アドレスは使用しないでください。

必須フィールドは(*)で示されます。

3文字から31文字の範囲で指定し

「送信する」をクリックすることにより、お客様は developerWorks のご使用条件に同意したことになります。 ご使用条件を読む

 


送信されたすべての情報は安全です。


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=60
Zone=XML, Open source
ArticleID=619613
ArticleTitle=Google スプレッドシートのデータを PHP で統合する: 第 2 回
publish-date=12142010