Google APIs Client Library for PHP を使ってスプレッドシートを読み書きする（2）

2015年09月17日 16:28

この記事は賞味期限切れです。（更新から1年が経過しています）

前回につづいて、Googleのライブラリを使ってスプレッドシートと連携する実験です。

Google APIs Client Library for PHP を使ってスプレッドシートを読み書きする

軽いおさらい

前回は Google Developper Console で鍵ファイルを取得し、それを使って認証してアクセストークンを取得、 APIにリクエストを投げてスプレッドシートの情報を引っ張るところまでやりました。

以降のコードは下記の変数が宣言されている前提で書かれています。

$accessToken; // アクセストークン文字列
$listfeed; // listfeedのURL
$cellsfeed; // cellsfeedのURL

シートのデータを取得する（listfeed）

listfeed はテーブルの内容を一行ずつまとめて出力する形式です。セルの住所（A1, C6等）に特に興味がなく、とりあえず一覧用のデータが欲しい場合はこちらの形式が便利ですね。

先ほど取得しておいた “listfeed” のURLへ、同じ要領でリクエストを投げてみます。

$request = new Google_Http_Request(
    $listfeed . "?alt=json",
    "GET",
    array("Authorization" => "Bearer {$accessToken}")
);
$data = json_decode($curl->executeRequest($request)[0]);

例えば次のようなシートを読み込んだ場合…

name	age	email
John	26	john@example.com

次のようなデータが返されます。

{
    "feed": {
        "entry": [
            {
                "title": {
                    "type": "text",
                    "$t": "john"
                },
                "content": "age:26, email:john@example.com"
                "gsx$name": {"$t": "John"},
                "gsx$age": {"$t": "26"},
                "gsx$email": {"$t": "john@example.com"}
            }
        ]
    }
}

“title” は1カラム目の情報が、“content” は2カラム以降の情報が “key:value, key:value” の形式で格納されますが、とりあえずこれらは無視して、“gsx$” の接頭辞が付いている物だけを見ていけば十分でしょう。接頭辞が邪魔で扱いづらい場合は加工してあげましょう。

// データ加工の例
function _parse_listfeed ($entries){
    $list = array();
    foreach($entries as $entry){
        $item = array();
        foreach($entry as $key => $value){
            if(preg_match('/^gsx\$(.+)$/', $key, $m)){
                $item[$m[1]] = $value->{"\$t"};
            }
        }
        array_push($list, $item);
    }
    return $list;
}

$list = _parse_listfeed($data->feed->entry);

結果をJSONではなくXMLで取得した場合、gsx名前空間の要素を取得するのが少々面倒になります。

cf) PHPでXMLの名前空間つきタグを読み込む色々 – Mach3.laBlog

シートのデータを取得する（cellsfeed）

cellsfeed は、セルの値を1つずつ出力してくれる形式です。 listfeed のように一行ずつエントリーとしてまとめたい場合には加工が必要ですが、行・列番号を取得しておきたい場合などにはこちらを使うと良いでしょう。

$request = new Google_Http_Request(
    $cellsfeed . "?alt=json",
    "GET",
    array("Authorization" => "Bearer {$accessToken}")
);
$data = json_decode($curl->executeRequest($request)[0]);

結果は次のようなデータが返されます。

{
    "feed": {
        "entry": [
            {
                "title": {
                    "type": "text",
                    "$t": "A1"
                },
                "content": {
                    "type": "text",
                    "$t": "name"
                }
            }
        ]
    }
}

“title” にはセルの住所、“content” にはその値が格納されています。このエントリーが入力されているセルの数だけ羅列されます。

例えば、（あまり賢い選択とは言えませんが）このデータをリスト形式に整理してみましょう。

// データ加工の例
function _parse_cellsfeed ($entries){
    $fields = array();
    $list = array();

    foreach($entries as $entry){
        preg_match("/^([A-Z]+)(\d+)$/", $entry->title->{"\$t"}, $m);
        $content = $entry->content->{"\$t"};
        $r = (int) $m[2];
        $c = $m[1];

        if($r === 1){
            $fields[$c] = $content;
            continue;
        }
        if(array_key_exists($c, $fields)){
            $list[$r] = is_array($list[$r]) ? $list[$r] : array();
            $list[$r][$fields[$c]] = $content;
        }
    }

    return $list;
}

$list = _parse_cellsfeed($data->feed->entry);

シートに新しいデータを追加する

読み込みの次は、データの書き込みをしてみましょう。 listfeed のURLにXMLの本文付きでPOSTすると、新しい行が追加されます。

既存のテーブルに次のようなデータを追加してみましょう。

name	age	email
Tom	32	tom@example.com

本文の形式は “application/atom+xml” になります。 listfeed の読み込みの際に使用した gsx名前空間を活用して本文を作成しましょう。

$postBody = <<<EOS
<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gsx="http://schemas.google.com/spreadsheets/2006/extended">
    <gsx:name>Tom</gsx:name>
    <gsx:age>32</gsx:age>
    <gsx:email>tom@example.com</gsx:email>
</entry>
EOS;

作成した本文と Content-Type ヘッダを付加してリクエストを投げます。

$request = new Google_Http_Request(
    $listfeed
    "POST",
    array(
        "Authorization" => "Bearer {$accessToken}",
        "Content-Type" => "application/atom+xml"
    ),
    $postBody
);

$res = $curl->executeRequest($request);

成功するとシートの最後の行にデータが追加されます。

<, > を含む値を扱う際、そのまま入力するとXMLの文法が不正になりますので、 htmlspecialchars 等を使って特殊文字を変換してあげると良いでしょう。

行・列番号を指定してセルの値を更新する

行単位ではなく、セル単位で値を変更する場合には cellsfeed を使用します。データの追加と同じく、XMLの本文を作成してPOSTします。

$postBody = <<<EOS
<entry xmlns="http://www.w3.org/2005/Atom" xmlns:gs="http://schemas.google.com/spreadsheets/2006">
    <gs:cell row="3" col="2" inputValue="33"/>
</entry>
EOS;

使用する名前空間は gs になります。ここでは、「3行目の2列目の値を”33″に変更」しています。

$request = new Google_Http_Request(
    $cellsfeed,
    "POST",
    array(
        "Authorization" => "Bearer {$accessToken}",
        "Content-Type" => "application/atom+xml"
    ),
    $postBody
);

$res = $curl->executeRequest($request);

成功するとスプレッドシートのセルの内容が書き換えられます。こちらも値の特殊文字の変換はお忘れなきよう。

めんどくさいですか？

毎度こんな事をしているのは些か面倒なので、この辺の処理を簡略化したライブラリを書いてみました。次回はその紹介となります。

コメントを書く