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

2019-07-31PHPGoogle

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

Google Sheets API created wizard
Google Sheets API Created Wizard

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

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

turn on Google API
turn on Google API

認証情報の追加

以下を入力し、「必要な認証情報」をクリック。
使用する API : Google Sheets API
API を呼び出す場所 : ウェブサーバー ( node.js, tomcat など)
アクセスするデータの種類 : アプリケーションデータ
App Engine または Compute Engine でこのAPIを使用する予定はありますか : いいえ、使用していません。
Add credentials page 1
Add credentials page 1
API を呼び出す場所の種類
7 つから選ぶことができた。
API を呼び出す場所の種類
API を呼び出す場所の種類

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

Add credentials page2
Add credentials page2

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

サービス アカウントとキーが作成されました
サービス アカウントとキーが作成されました

スプレッドシートの共有

「IAMと管理」 → 「サービスアカウント」のメールアドレスをコピー。
IAM と管理のサービスアカウント
IAM と管理のサービスアカウント

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

(この時点で、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 というファイル名にして、プロジェクトのルートディレクトリ( composer.json と同じ階層)に置く。

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

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

ファイル名 「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"]."<br>";
  $err .= "message : ".$errors["error"]["message"];
  echo "<br>Google_Exception".$err;
}

echo の結果
12

結果
google sheets api update
Google Sheets API update

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

スコープを SPREADSHEETS_READONLY に設定して、書き込み処理を実行すると、下記のエラーとなる。
code : 403
message : Request had insufficient authentication scopes.

valueInputOption は、値をどんな形式で書き込むかを指定するパラメータ。
RAWUSER_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 "<br>Google_Exception".$err;
}

echo の結果
4

結果

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

Google Sheets API Append 1
Google Sheets API Append 1

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

Google Sheets API Append 2
Google Sheets API Append 2

以上。

Posted by Agopeanuts