[PHP] Google Sheets API を使ってスプレッドシートに値を書き込む

2019-07-312020-02-01PHPGoogle

Google Sheets API を使うと、 PHP とか JavaScript とかで作られた Web アプリから Google スプレッドシートを操作することができる。今回は、Google Sheets API の導入と、スプレッドシートに値を書き込む方法。
API を使わない方法も存在するが、ここでは Sheets API v4 を使っている。
書き込みには、更新と追記の 2 つの方法があり、両方のサンプルコードを記載している。

Google スプレッドシートにアクセスする方法

調べると 2 つの方法が出てきて、 Google Sheets API を使う方法と、スプレッドシートをウェブ公開にしてシートの URL からアクセスする方法があった。

スプレッドシートをウェブ公開する方法は、お手軽で簡単に導入できそうだが、公開範囲を限定できる（同一アカウントでログインしている人のみ表示が選択可能）とはいえウェブ上に公開されるので、今回扱うデータの特性上よろしくない。

API を使う方法は、スプレッドシートにアクセスするシステムに対し、アクセス権限の認証を行うため、手間はかかるがスプレッドシートを公開することなくデータを取得することができる。よって、今回は API を使う方法を採用した。

Google Sheets API の導入

G メールとか Google Drive とか Google がもつサービスを、自分のアプリケーションで使うとか、その他のサービスから利用できるようにしてくれる Google API ていうのがあって、この API の 1 つでスプレッドシートを扱えるようにしてくれるのが Sheets API である。

認証について

Google API を使う場合、 Google サービスやデータアクセス時に認証が必要となる。
認証方法には、 API キー、 OAuth 2.0 、サービスアカウントによる認証があり、いずれかを使う。今回は、サービスアカウントを用いた認証方法を使う。

Google のドキュメントに、「サービスアカウントは、アプリケーションや仮想マシンインスタンスで使用される特別なアカウント」と書かれているので、アプリケーションから Google サービスに置いてあるデータにアクセスする場合には、サービスアカウントの利用が向いていると思われる。
認証方法の使い分けについて、参考として公式ガイド -認証の概要-にある認証の推奨方法を貼り付けておく。

プロジェクトの作成

Google API Console の Google Sheets API のアプリケーション登録画面から「プロジェクトを作成」またはプルダウンから既存プロジェクトを選択し、「続行」ボタンをクリック。

「認証情報の追加」までの操作は、 Google Cloud Platform でもできるみたい。

API が有効になる。「認証情報に進む」をクリック

認証情報の追加

API を呼び出す場所の種類
7 つから選ぶことができた。

サービスアカウント名を入力し、役割を選択する。（サービスアカウントは自動で作られた）
キーのタイプは「 JSON 」 を選択。
「次へ」をクリック。

JSON ファイルがダウンロードされるので、保存しておく。

スプレッドシートの共有

値を取得したいスプレッドシートの「共有」をクリック。
ユーザーのところに、コピーしたメアドを入力し「送信」クリック。

(この時点で、Google Sheets API が無効になっている場合は、 「ダッシュボード」 → 「 API とサービス」 → 「 Google Sheets API 」の画面で有効にできそう。)

API Client Library のインストール

composer を使ってインストールする。
composer がない場合は、先に composer をインストールする。 composer のインストール方法は、 Amazon Linux に composer をインストール を参照してください。
（サーバーに置いて使う場合は、 GitHub にある公式ライブラリ googleapis/google-api-php-client からダウンロードする。）
プロジェクトのルートディレクトリに移動してから、コマンド実行。

$ php composer.phar require google/apiclient:^2.0

vendor フォルダと、 composer.json 、composer.lock というファイルが作成される。

「認証情報の追加」でダウンロードされた json ファイルを credentials.json というファイル名にして( しなくてもいい )、アプリケーションからはアクセスできるけど、外部からはアクセスできない安全な場所に格納する。例えば、ドキュメントルート以外の場所。

テスト環境で、ただの動作確認だけならプロジェクトのルートディレクトリに置いてもいいが、セキュリティ上よくない。

( AWS のパラメータストアで管理したかったが、 Google API では認証時に、この json ファイルのファイルパスを使う必要があり、使えなかった。 json ファイルの中身のデータを直接プログラムに渡す方法は、 Python だと見つかったけど、 PHP では見つからなかった。
前は Google_Auth_AssertionCredentials クラスを使ってキーとかが渡せたみたいだが、現在は廃止された。 Google API Client Upgrade Guide)

スプレッドシートに値を書き込む

スプレッドシートのセルの値を更新する

ファイル名 「test」 にアクセスして、シート名 「シート1」 の A2:B7 の範囲にあるセルを更新するコード。

require_once __DIR__ . '/vendor/autoload.php';
define('APPLICATION_NAME', 'test');
define('CLIENT_SECRET_PATH', './PATH/credentials.json');
// スコープの設定
define('SCOPES', implode(' ', array(
  Google_Service_Sheets::SPREADSHEETS)
));
// アカウント認証情報インスタンスを作成
$client = new Google_Client();
$client->setScopes(SCOPES);
$client->setAuthConfig(CLIENT_SECRET_PATH);
// シートのインスタンスを生成
$service = new Google_Service_Sheets($client);
try {
  // スプレッドシートの ID
  $spreadsheetId = 'スプレッドシートのID';
  // 更新するシートの名前とセルの範囲
  $range = 'シート1!A2:B7';
  // 更新するデータ
  $values = [
      ["A1", "B1"],
      ["2019/1/1", "2020/12/31"],
      ["アイウエオ", "かきくけこ"],
      [10, 20],
      [100, 200],
      ['=(A5+A6)', '=(B5+B6)']
  ];
  $updateBody = new Google_Service_Sheets_ValueRange([
      'values' => $values
  ]);
  // valueInputOption を指定（ USER_ENTERED か RAW から選択）
  $params = [
      'valueInputOption' => 'USER_ENTERED'
  ];
  $result = $service->spreadsheets_values->update($spreadsheetId, $range, $updateBody, $params);
  // 更新したセルの数が返ってくる
  echo $result->getUpdatedCells();
} catch (Google_Exception $e) {
  // $e は json で返ってくる
  $errors = json_decode($e->getMessage(),true);
  $err = "code : ".$errors["error"]["code"]."
";
  $err .= "message : ".$errors["error"]["message"];
  echo "
Google_Exception".$err;
}

setAuthConfig() には、 json ファイルのパスを渡す。

または、下記の方法で認証する。 Google は環境変数を使う方法を推奨している。

putenv('GOOGLE_APPLICATION_CREDENTIALS=/path/to/credentials.json');
$client->useApplicationDefaultCredentials();

echo の結果
12

結果

書き込みを行う際は、 SCOPES は SPREADSHEETS にする。
読み取りのみにしたい場合は SPREADSHEETS_READONLY とする。

valueInputOption は、値をどんな形式で書き込むかを指定するパラメータ。
RAW と USER_ENTERED のどちらかを指定する必要がある。

RAW
更新するデータで書いた通りに出力される。計算式を書き込んだら、計算式の形で出力。

USER_ENTERED
スプレッドシートに普通に入力したときと同じように出力される。計算式を書き込んだら、計算結果を出力。

スプレッドシートに値を追記する

スプレッドシートに新しい値を書き込む。追記は、シートの最後行（値がない最初の行）から書き込まれていく。

require_once __DIR__ . '/vendor/autoload.php';
define('APPLICATION_NAME', 'test');
define('CLIENT_SECRET_PATH', './PATH/credentials.json');
// スコープの設定
define('SCOPES', implode(' ', array(
  Google_Service_Sheets::SPREADSHEETS)
));
// アカウント認証情報インスタンスを作成
$client = new Google_Client();
$client->setScopes(SCOPES);
$client->setAuthConfig(CLIENT_SECRET_PATH);
// シートのインスタンスを生成
$service = new Google_Service_Sheets($client);
try {
  $spreadsheetId = 'スプレッドシートのID';
  $range = 'シート1';
  // 追記するデータ
  $values = [
      ["Aに追記", "Bに追記"],
      [1000, 2000]
  ];
  $appendBody = new Google_Service_Sheets_ValueRange([
      'values' => $values
  ]);
  $params = [
      'valueInputOption' => 'USER_ENTERED'
  ];
  $result = $service->spreadsheets_values->append($spreadsheetId, $range, $appendBody, $params);
  echo $result->getUpdates()->getUpdatedCells();
} catch (Google_Exception $e) {
  // $e は json で返ってくる
  $errors = json_decode($e->getMessage(),true);
  $err = "code : ".$errors["error"]["code"]."\r\n";
  $err .= "message : ".$errors["error"]["message"];
  echo "
Google_Exception".$err;
}

echo の結果
4

結果

範囲を指定する $range に「シート名」しか指定しない場合、シートの中の最終行に追記される。

$range にセルの範囲を指定した場合、指定したセルの最終行から値が書き込まれる。
$range = 'シート1!A:B';
※ $range = 'シート1!A1:B1' と指定しても同じ結果になる。（値が存在しているセルには書き込まれない。）

以上。