久しぶりにまとまった休暇が取れたので、Google Docsのスプレットシートに記述された表データにGoogle Spreadsheets API経由でアクセスしてマスタ管理を行う方法をまとめてみました。
マスタデータの用意
今回は例として、上記のようなマスタデータをGoogle Docs上に用意してあります。
この表は共有設定を行ってURLを伝えれば、関係者が同時に編集する事が可能です。そのURLのパラメータに"key=“から始まる数十文字の英数字があるかと思いますが、これがこのドキュメントにアクセスするためのIDになるためメモしておきます。
OAuth 2.0クライアントIDの準備
Google Cloud ConsoleのRegisterd appsメニューより、上記マスタデータにアクセスするためのOAuth 2.0クライアントIDを新規登録します。今回、上記マスタデータへはコンソールプログラム(PHP)からアクセスするため、Platformには"Native"を選択します。登録が成功するとクライアントIDの他にクライアントシークレットやリダイレクトURIなどの情報が表示されます。
コンソールプログラムからのOAuth 2.0認証と実行
以下のコンソールプログラムを用意して実行すると、“authorization_url:“の下にURLが表示されます。これは、このプログラムからマスタデータにアクセスするための許可をユーザに求める画面のURLになります。これをブラウザに貼り付けて、表示された画面で【承認する】を選択すると、”このコードをコピーし、アプリケーションに切り替えて貼り付けてください”というメッセージに下に英数字のコードが表示されますので、それをコンソールに貼り付けてENTERを押すとAPIを経由してマスタデータへアクセス（TSVでデータをエクスポート）が実行されます。
<?php
$client_id = '（クライアントID）';
$client_secret = '（クライアントシークレット）';
$redirect_uri = '（リダイレクトURI）';
$scope = 'https://spreadsheets.google.com/feeds';
$key = '（マスタデータのキー文字列）';
$gid = array(
'od6' => 0, 'od7' => 1, 'od4' => 2, 'od5' => 3, 'oda' => 4, 'odb' => 5, 'od8' => 6, 'od9' => 7, 'ocy' => 8, 'ocz' => 9, 'ocw' => 10,
'ocx' => 11, 'od2' => 12, 'od3' => 13, 'od0' => 14, 'od1' => 15, 'ocq' => 16, 'ocr' => 17, 'oco' => 18, 'ocp' => 19, 'ocu' => 20,
'ocv' => 21, 'ocs' => 22, 'oct' => 23, 'oci' => 24, 'ocj' => 25, 'ocg' => 26, 'och' => 27, 'ocm' => 28, 'ocn' => 29, 'ock' => 30,
'ocl' => 31, 'oe2' => 32, 'oe3' => 33, 'oe0' => 34, 'oe1' => 35, 'oe6' => 36, 'oe7' => 37, 'oe4' => 38, 'oe5' => 39, 'odu' => 40,
'odv' => 41, 'ods' => 42, 'odt' => 43, 'ody' => 44, 'odz' => 45, 'odw' => 46, 'odx' => 47, 'odm' => 48, 'odn' => 49, 'odk' => 50,
'odl' => 51, 'odq' => 52, 'odr' => 53, 'odo' => 54, 'odp' => 55, 'ode' => 56, 'odf' => 57, 'odc' => 58, 'odd' => 59, 'odi' => 60,
'odj' => 61, 'odg' => 62, 'odh' => 63, 'obe' => 64, 'obf' => 65, 'obc' => 66, 'obd' => 67, 'obi' => 68, 'obj' => 69, 'obg' => 70,
'obh' => 71, 'ob6' => 72, 'ob7' => 73, 'ob4' => 74, 'ob5' => 75, 'oba' => 76, 'obb' => 77, 'ob8' => 78, 'ob9' => 79, 'oay' => 80,
'oaz' => 81, 'oaw' => 82, 'oax' => 83, 'ob2' => 84, 'ob3' => 85, 'ob0' => 86, 'ob1' => 87, 'oaq' => 88, 'oar' => 89, 'oao' => 90,
'oap' => 91, 'oau' => 92, 'oav' => 93, 'oas' => 94, 'oat' => 95, 'oca' => 96, 'ocb' => 97, 'oc8' => 98, 'oc9' => 99
);
$auth_url_base = 'https://accounts.google.com/o/oauth2/auth';
$params = http_build_query(array(
'response_type' => 'code',
'client_id' => $client_id,
'redirect_uri' => $redirect_uri,
'scope' => $scope,
));
echo 'authorization url:' . PHP_EOL;
echo $auth_url_base . '?' . $params . PHP_EOL;
echo PHP_EOL;
echo 'authorization_code: ' . PHP_EOL;
$authorization_code = trim(fgets(STDIN));
$token_url = 'https://accounts.google.com/o/oauth2/token';
$params = array(
'client_id' => $client_id,
'client_secret' => $client_secret,
'code' => $authorization_code,
'redirect_uri' => $redirect_uri,
'grant_type' => 'authorization_code',
);
$context = stream_context_create(array(
'http' => array(
'method' => 'POST',
'header' => 'Content-Type: application/x-www-form-urlencoded',
'content' => http_build_query($params)
)
));
$token = json_decode(file_get_contents($token_url, false, $context));
$api_url = 'https://spreadsheets.google.com/feeds/worksheets/' . $key . '/private/full';
$context = stream_context_create(array(
'http' => array(
'method' => 'GET',
'header' => 'Authorization: Bearer ' . $token->access_token,
)
));
$xml = file_get_contents($api_url, false, $context);
$xml = simplexml_load_string($xml);
foreach ($xml->entry as $entry) {
if (strstr($entry->id, $key) !== false) {
$worksheet_id = array_pop(explode('/', $entry->id));
break;
}
}
$api_url = 'https://docs.google.com/feeds/download/spreadsheets/Export?key=' . $key . '&exportFormat=tsv&gid=' . $gid[$worksheet_id];
$context = stream_context_create(array(
'http' => array(
'method' => 'GET',
'header' => 'Authorization: Bearer ' . $token->access_token,
)
));
$data = file_get_contents($api_url, false, $context);
echo $data;
このサンプルだと毎回OAuth認証を手動で行う必要があるため、$tokenの内容をファイルなどに保存しておいて次回以降のアクセスにはrefresh_tokenを使うなど改修を行えば、トークンが有効な間（結構長い）は認証なしでAPIを呼び出すことができるため、$dataをSQL文などに整形すればマスタデータの登録を自動化することも可能です。
参考：
Using OAuth 2.0 for Installed Applications
Google SpreadSheetで使用されているGIDのテーブル
