Google Sheets APIを使ってPHPでスプレッドシートを操作

2023年 9月13日　Posted　野々瀨(フロントエンドエンジニア)

GoogleにはさまざまなAPIを利用することができます。そのうちの一つにGoogle Sheets APIというものがあります。Google Sheets APIはスプレッドシートをRestfulなAPIとして操作することができるAPIです。今回は、そんなGoogle Sheets APIをPHPから利用する方法をご紹介します。

Google Sheets APIとは

Google Sheets APIは、Googleが提供しているAPIの一つで、Restful APIとしてスプレッドシートを操作を可能にします。扱えるのはスプレッドシート形式のみで、Excel（.xls, .xlsx）などの形式には対応していませんので、ご注意ください。また、Google Sheets APIにはバージョンが幾つか存在しますが、ここではv4を基にしてご紹介します。

Google Sheets API - Google for Developers

Google APIs Client Library for PHPとは

Google APIs Client Library for PHPは、Google APIをPHPで利用可能にするのGoogle公式のPHPライブラリです。今回はこのライブラリを使用してスプレッドシートを操作します。

Google APIs Client Library for PHP - GitHub

事前準備

次の内容を事前にご準備ください。

• Google Cloud Platformのアカウントの作成
• プロジェクトの作成
• PHPのComposerのインストール

APIの有効化

Google Cloud consoleでプロジェクトを選択し、トップ画面からクイック アクセスの「APIとサービス」リンクを押します。

「+APIとサービスの有効化」リンクを押します。

検索の入力欄に「Google Sheets API」を入力し、Enterキーを押します。

結果一覧にある「Google Sheets API」を押します。

「有効にする」ボタンを押します。

Google Sheets APIの詳細画面が表示されれば完了です。

サービスアカウントの作成

Google Cloud consoleでプロジェクトを選択し、トップ画面からクイック アクセスの「IAMと管理」リンクを押します。

左側のサイドメニューにあります「サービス アカウント」メニューを押します。

「+サービス アカウントを作成」リンクを押します。

「サービス アカウント名」、「サービス アカウント ID」を入力し、「作成して続行」ボタンを押します。この時、「サービス アカウント ID」は「サービス アカウント名」を入力することで、自動的に作成されますので、任意で決めなくても、そのまま自動生成されたIDで作成することも可能です。

なお、後ほど使用するため、「サービス アカウント ID」の下に表示されているメールアドレスをコピーしてメモしておいてください。（後からでも確認・コピーすることは可能です）。

サービスアカウントにプロジェクトへの付与する権限を指定することができます。今回は「続行」ボタンを押します。

サービスアカウントへアクセスを許可するユーザーを指定することができます。今回は「完了」ボタンを押します。

一覧に戻りますと、サービスアカウントが作成され、完了となります。

メールアドレスをコピーし忘れた場合はここでコピーするか、「メール」列のリンクを押すと詳細画面が表示されますので、そちらでもコピーすることができます。

認証情報をダウンロード

作成したサービスアカウントの詳細画面を開き、「キー」タブを押します。

「鍵を追加」ボタンを押して、表示されたドロップダウンメニューから「新しい鍵を作成」メニューを押します。

「キーのタイプ」項目の選択肢から「JSON」を選択し、「作成」ボタンを押します。

作成されるとダウンロードが自動的に行われます。完了しますと、「秘密鍵がパソコンに保存されました」と表示されますので、「閉じる」ボタンを押します。

スプレッドシートの共有

操作を行いたいスプレッドシートを開き、画面右上にある「共有」ボタンを押します。

入力画面に先ほどメモしておいたサービスアカウントのメールアドレスを入力します。予定している操作のうち取得系のみなら「閲覧者」、何かしら変更も行う場合は「編集者」を選択します。「共有」ボタンを押します。

「アクセスできるユーザー」の一覧に表示されたら共有は完了です。

スプレッドシートIDの確認

スプレッドシートを開いたとき、URLは次のようになっています。

https://docs.google.com/spreadsheets/d/スプレッドシートID/edit#gid=シートID

この「スプレッドシートID」部分をコピーしてメモしておいてください。

PHPライブラリのインストール

Google APIs Client Library for PHPのインストールを行います。プロジェクトのディレクトリーへ移動し、次のコマンドを実行してライブラリをインストールします。

・ プロジェクト自体がなければ、適当なディレクトリーを作成して、そこで行います。

composer require google/apiclient

なお、PHP7.4.xで動作させたい場合は、環境によっては最新バージョンでは動作しないことがあります（特に既にcomposerでのパッケージを利用している場合、依存パッケージで動作しないことがあります）。

README.mdにも書いてあるバージョンを2.12.xでインストールすることで、PHP7.4.xでも正常に動作させることができます。

composer require google/apiclient:^2.12.1

使い方

Google APIs Client Library for PHPの使い方のご紹介です。

• 変数は説明をまたいで使用されます。
• セル取得や値の設定など一部の使い方のみご紹介します。

準備

次のようにオートロードでライブラリを読み込みます。

require_once __DIR__.'/vendor/autoload.php';

Google\Clientで、googleのサービスを利用するためのクライアントのインスタンスを生成します。

$client = new Google\Client();

setAuthConfigメソッドを使用して、ダウンロードしたサービスアカウントの認証情報のファイルパスを第一引数に指定します。

$client->setAuthConfig('xxxxxxxxxx.json');

setScopesメソッドを使用して、利用するするサービスを指定します。
今回はスプレッドシートですので、Google_Service_Sheets::SPREADSHEETSを第一引数に指定します。

$client->setScopes(Google_Service_Sheets::SPREADSHEETS);

ちなみにsetScopesメソッドは、引数に配列を指定することで、複数の利用するサービスを指定することができます。

$client->setScopes([
	Google_Service_Sheets::SPREADSHEETS, // スプレッドシート
	Google_Service_Drive::DRIVE          // ドライブ
]);

Google_Service_Sheetsで、スプレッドシートのインスタンスを生成します。
第一引数にはクライアントのインスタンスを指定します。

$service = new Google_Service_Sheets($client);

スプレッドシートの取得

spreadsheets->getメソッドを使用して、スプレッドシートを取得します。メモしておいたスプレッドシートIDを第一引数に指定します。なお、スプレッドシートIDは他の操作でも使用する場合がありますので、変数などで保管しておくとよいでしょう。

$spreadsheet_id = 'xxxxxxxxxx';

$response = $service->spreadsheets->get($spreadsheet_id);

スプレッドシートのプロパティ情報を取得

spreadsheets->getメソッドで取得したスプレッドシートをもとに、propertiesメソッドを使用すると、タイトルなどスプレッドシート自体のプロパティ情報を取得することができます。

echo $response->properties->title; // タイトル

全てのシートを取得

getSheetsメソッドを使用して、スプレッドシートにある全てのシートを取得することができます。

$sheets = $response->getSheets();

またシートはインデックス番号で一つのシートを取得することができます。

$sheet = $response->getSheets()[0];

シートのプロパティ情報を取得

propertiesメソッドを使用しますと、タイトルなどシート自体のプロパティ情報を取得することができます。

$sheet = $response->getSheets()[0];

echo $sheet->properties->title;

セルの値に関する情報を取得

spreadsheets_values->getメソッドを使用して、セルの値に関する情報を取得することができます。第一引数にスプレッドシートID、第二引数にシート名!セル番号を指定します。

$response = $service->spreadsheets_values->get($spreadsheet_id, 'Sheet 1!A1');

第二引数は範囲を指定することもできます。

$response = $service->spreadsheets_values->get($spreadsheet_id, 'Sheet 1!A1:G10');

指定の仕方は次のような感じになります。

セルの値を取得

spreadsheets_values->getメソッドで取得したセルを基に、getValuesメソッドを使用しますと、セルの値を取得することができます。戻り値は二次元配列で、一次元目を行、二次元目を列として返ってきます。

$rows = $response->getValues();

echo '<table border="1">';

foreach ($rows as $i => $row) {
	if ($i === 0) {
		echo '<thead>';
		echo '<tr>';
		echo '<th>'.implode('</th><th>', $row).'</th>';
		echo '</tr>';
		echo '</thead>';
	} else {
		if ($i === 1) echo '<tbody>';

		echo '<tr>';
		echo '<td>'.implode('</td><td>', $row).'</td>';
		echo '</tr>';
	}
}

echo '</tbody>';
echo '</table>';

なお、最後の列が空欄だった場合（最後から連続して空欄だった場合も）、その列（二次元目の配列）のインデックス番号は存在しません。

セルに値を代入（設定）

セルに値を代入（設定）するにはまず、Google_Service_Sheets_ValueRangeクラスで設定する値を準備するインスタンスを生成します。セルに代入（設定）する値は二次元配列で指定し、一次元目を行、二次元目を列で指定します。

$data = new Google_Service_Sheets_ValueRange([
	'values' => [
		['あいうえお', 'かきくけこ'],
		['さしすせそ', 'たちつてと']
	]
]);

次にspreadsheets_values->updateメソッドを使用することで、セルに値を設定することができます。第一引数にスプレッドシートID、第二引数に設定するセル番号、第三引数にGoogle_Service_Sheets_ValueRangeのインスタンス、第四引数にオプションを指定します。

$service->spreadsheets_values->update($spreadsheet_id, 'Sheet 1!A1', $data, [
	'valueInputOption' => 'USER_ENTERED'
]);

第四引数のオプションでvalueInputOptionを指定可能な値は次のとおりです。

行を追加してセルに値を代入（設定）

spreadsheets_values->appendメソッドを使用して、行を追加してセルに値を代入（設定）することができます。記述方法はspreadsheets_values->updateと同じです。

$data = new Google_Service_Sheets_ValueRange([
	'values' => [
		['あいうえお', 'かきくけこ'],
		['さしすせそ', 'たちつてと']
	]
]);

$service->spreadsheets_values->append($spreadsheet_id, 'Sheet 1!A1', $data, [
	'valueInputOption' => 'USER_ENTERED'
]);

セルを空にする

セルを空にするにはまず、Google_Service_Sheets_ClearValuesRequestクラスで削除用のインスタンスを生成します。

$request = new Google_Service_Sheets_ClearValuesRequest();

次にspreadsheets_values->clearメソッドを使用することで、セルを空にすることができます。セルを空にするだけなので、その行のセル全てが空だったとしても、その行は削除されずに残ります。

$service->spreadsheets_values->clear($spreadsheet_id, 'Sheet 1!A1:B2', $request);

複数のセルや選択範囲に値を代入（設定）

セルに値を代入するGoogle_Service_Sheets_ValueRangeクラスを複数同時に実行することができます。

まず、Google_Service_Sheets_ValueRangeクラスのインスタンスを一つの配列に格納します。

$datas = [];

$datas[] = new Google_Service_Sheets_ValueRange([
	'range'  => 'Sheet 1!A1', // シートとセル（選択範囲）
	'values' => [             // 挿入（設定）する値
		['あいうえお', 'かきくけこ'],
		['さしすせそ', 'たちつてと']
	]
]);

$datas[] = new Google_Service_Sheets_ValueRange([
	'range'  => 'シート2!A1',
	'values' => [
		['アイウエオ', 'カキクケコ'],
		['サシスセソ', 'タチツテト']
	]
]);

次にGoogle_Service_Sheets_BatchUpdateValuesRequestクラスで複数同時に実行するためのインスタンスを生成します。引数のdataプロパティに先ほどの配列を指定します。

$request = new Google_Service_Sheets_BatchUpdateValuesRequest([
	'data'             => $datas,
	'valueInputOption' => 'USER_ENTERED'
]);

そしてspreadsheets_values->batchUpdateメソッドで実行します。
第一引数にスプレッドシートID、第二引数にGoogle_Service_Sheets_BatchUpdateValuesRequestのインスタンスを指定します。

$service->spreadsheets_values->batchUpdate($spreadsheet_id, $request);

これで複数の代入（設定）が実行されます。

行または列の挿入

行または列の挿入を行うにはまず、Google_Service_Sheets_Requestクラスで挿入するためのインスタンスを生成します。引数は連想配列でさまざまな設定を指定します。簡単な説明はコード内のコメントアウトをご覧ください。

$request = new Google_Service_Sheets_Request([
	// 機能名（行または列の挿入する機能である'insertDimension'を指定）
	'insertDimension' => [
		'range' => [
			'sheetId'    => xxxxxxxxxx, // 対象のシートのシートID
			'dimension'  => 'ROWS',     // 挿入する方向（行の場合：'ROWS'　列の場合：'COLUMNS'）
			'startIndex' => 0,          // 挿入を開始するインデックス番号
			'endIndex'   => 3           // 挿入を終了するインデックス番号
		]
	],
	'inheritFromBefore' => true // 直前のスタイルなどを継承するかどうか（例えば罫（けい）線など）
]);

次にGoogle_Service_Sheets_BatchUpdateSpreadsheetRequestクラスでバッチ処理を行うためのインスタンスを生成します。

$batchRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
	'requests' => [
		$request
	]
]);

次にspreadsheets->batchUpdateメソッドでバッチ処理を実行します。第一引数にスプレッドシートID、第二引数にバッチ処理のインスタンスを指定します。

$service->spreadsheets->batchUpdate($spreadsheet_id, $batchRequest);

これで挿入が実行されます。

行または列の削除

行または列の削除を行うにはまず、Google_Service_Sheets_Requestクラスでインスタンスを生成します。引数は連想配列でさまざまな設定を指定します。簡単な説明はコード内のコメントアウトをご覧ください。

$request = new Google_Service_Sheets_Request([
	// 機能名（行または列の削除する機能である'deleteDimension'を指定）
	'deleteDimension' => [
		'range' => [
			'sheetId'    => xxxxxxxxxx, // 対象のシートのシートID
			'dimension'  => 'ROWS',     // 削除する方向（行の場合：'ROWS'　列の場合：'COLUMNS'）
			'startIndex' => 0,          // 削除を開始するインデックス番号
			'endIndex'   => 3           // 削除を終了するインデックス番号
		]
	]
]);

次にGoogle_Service_Sheets_BatchUpdateSpreadsheetRequestクラスでバッチ処理を行うためのインスタンスを生成します。

$batchRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
	'requests' => [
		$request
	]
]);

次にspreadsheets->batchUpdateメソッドでバッチ処理を実行します。第一引数にスプレッドシートID、第二引数にバッチ処理のインスタンスを指定します。

$service->spreadsheets->batchUpdate($spreadsheet_id, $batchRequest);

これで削除が実行されます。

シートを追加

シートを追加するにはまず、Google_Service_Sheets_Requestクラスでインスタンスを生成します。引数は連想配列でさまざまな設定を指定します。簡単な説明はコード内のコメントアウトをご覧ください。

$request = new Google_Service_Sheets_Request([
	// 機能名（シートを追加する機能である'addSheet'を指定）
	'addSheet' => [
		// シートのプロパティ情報（任意で設定）
		'properties' => [
			'title'   => 'New Sheet 1', // シート名
			// 'sheetId' => 123,           // シートID
			// 'index'   => 0,             // 挿入先のインデックス番号（指定しないと最後に追加される）
			// 'hidden'  => true,          // 非表示にするかどうか
			'tabColorStyle' => [        // タブの色
				'rgbColor' => [
					'red'   => 1,
					'green' => 0.5,
					'blue'  => 0.2,
					'alpha' => 1
				]
			]
		]
	]
]);

次にGoogle_Service_Sheets_BatchUpdateSpreadsheetRequestクラスでバッチ処理を行うためのインスタンスを生成します。

$batchRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
	'requests' => [
		$request
	]
]);

次にspreadsheets->batchUpdateメソッドでバッチ処理を実行します。第一引数にスプレッドシートID、第二引数にバッチ処理のインスタンスを指定します。

$service->spreadsheets->batchUpdate($spreadsheet_id, $batchRequest);

これでシートの追加が実行されます。

セルの罫線の装飾

セルの罫線の装飾を行うにはまず、Google_Service_Sheets_Requestクラスでインスタンスを生成します。引数は連想配列でさまざまな設定を指定します。簡単な説明はコード内のコメントアウトをご覧ください。

$request = new Google_Service_Sheets_Request([
	// 機能名（罫線の装飾をする機能である'updateBorders'を指定）
	'updateBorders' => [
		// セルの範囲
		'range' => [
			'sheetId'          => xxx, // シートID
			'startRowIndex'    => 1,   // 開始行のインデックス番号
			'endRowIndex'      => 5,   // 終了行のインデックス番号
			'startColumnIndex' => 1,   // 開始列のインデックス番号
			'endColumnIndex'   => 5    // 終了列のインデックス番号
		],

		// 上の罫線
		'top' => [
			// 罫線の種類
			'style' => 'SOLID',

			// 罫線の色
			'colorStyle' => [
				'rgbColor' => [
					'red'   => 1,
					'green' => 0.5,
					'blue'  => 0.2,
					'alpha' => 1
				]
			]
		],

		// 左の罫線
		'left' => [
			'style' => 'SOLID'
		],

		// 右の罫線
		'right' => [
			'style' => 'SOLID'
		],

		// 下の罫線
		'bottom' => [
			'style' => 'SOLID'
		],

		// 内側の水平の罫線
		'innerHorizontal' => [
			'style' => 'SOLID'
		],

		// 内側の垂直の罫線
		'innerVertical' => [
			'style' => 'SOLID'
		]
	]
]);

次にGoogle_Service_Sheets_BatchUpdateSpreadsheetRequestクラスでバッチ処理を行うためのインスタンスを生成します。

$batchRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
	'requests' => [
		$request
	]
]);

次にspreadsheets->batchUpdateメソッドでバッチ処理を実行します。第一引数にスプレッドシートID、第二引数にバッチ処理のインスタンスを指定します。

$service->spreadsheets->batchUpdate($spreadsheet_id, $batchRequest);

これで罫線の装飾が実行されます。

Google_Service_Sheets_Requestについて

行列の挿入や削除で記述したGoogle_Service_Sheets_Requestクラスは、スプレッドシートやシートに対してさまざまな処理をより細かく処理することができるクラスです。次のようなさまざまな機能が用意されています。

詳しくは次のページをご覧ください。

https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request?hl=ja

Google_Service_Sheets_BatchUpdateSpreadsheetRequestについて

Google_Service_Sheets_BatchUpdateSpreadsheetRequestクラスは、Google_Service_Sheets_Requestクラスのインスタンスを複数同時に処理することができるクラスです。

$batchRequest = new Google_Service_Sheets_BatchUpdateSpreadsheetRequest([
	'requests' => [
		new Google_Service_Sheets_Request([
			'insertDimension' => [
				..
			]
		]),
		new Google_Service_Sheets_Request([
			'insertDimension' => [
				...
			]
		])
	]
]);

例外について

Google APIs Client Library for PHPライブラリは、幾つかの処理で例外が発生します。
例えば次のような内容で発生します。

• スプレッドシートへのアクセスに権限がない
• 形式がスプレッドシート以外
• スプレッドシートIDの指定でスプレッドシートが見つからない
• シート名の指定でシートが見つからない
• 閲覧者でセルを変更しようとした場合など操作権限がない

なので例外が発生する可能性がある場合は、try - catchなどで対応すると無難です。

try {

	$response = $service->spreadsheets_values->get(...);

} catch (Google\Service\Exception $e) {

	// echo json_decode($e->getMessage())->error->message;

}

なお、catchで$e->getMessage()によって受け取った内容は、次のようにJSON文字列を返します。

{
	"error": {
		"code": 404,
		"message": "Requested entity was not found.",
		"errors": [
			{
				"message": "Requested entity was not found.",
				"domain": "global",
				"reason": "notFound"
			}
		],
		"status": "NOT_FOUND"
	}
}

最後に

ライブラリで扱えるようにするまでの準備が少々手間ではありますが、ライブラリからのスプレッドシートの操作は比較的簡単に実装することができます。今回ご紹介した使い方は一部だけのご紹介でしたが、これ以外にもさまざまな機能があります。

また、Google Sheets APIはPHP以外にもRestful APIとしてのリクエストはもちろん、JavaScriptやPython、Javaなどでも扱うことができます。環境に応じてさまざまな方法でGoogle Sheets APIを利用することができるかと思います。