[PHP] Google Sheets APIでシートから値を読み取るコード

2019-08-11PHPGoogle

Google Sheets API を使って PHP でスプレッドシートからセルの値を読み取る方法。
範囲を 1 つ指定する場合と、異なる複数の範囲から 1 回で値を読み取るとき、および取得値のフォーマットの指定オプションに関するサンプルコード。

プログラムからスプレッドシートにアクセス可能で、 API ライブラリのインストール等は完了していることが前提です。
API を使えるようにする方法は、 [PHP] Google Sheets API を使ってスプレッドシートの値を取得 を参照してください。

スポンサーリンク

範囲 1 つの読み取り

範囲 1 つの場合のデータ読み込みには、 spreadsheets.values.get メソッドを使う。
オプションを使うと、行ごとに読み込むのか、列ごとに読み込むのかなどの設定を行える。
行、列の切り替えは、 majorDimension というオプションを使用する。デフォルト(オプションを設定しない)は、 ROWS で行ごとの読み込みとなる。

行で読み込み

このスプレッドシートの青い範囲( B2 から D6 まで)にあるセルの値を読み取る。

Sample Spreadsheets1
Sample Spreadsheets1

PHP コード
//シート情報を操作するインスタンスを生成
$sheet = new Google_Service_Sheets($client);

$spreadsheetId = 'スプレッドシートのID';
$range = 'シート1!B2:D';
$response = $sheet->spreadsheets_values->get($spreadsheetId, $range);
$values = $response->getValues();

foreach ($values as $val) {
  echo "<pre>";
  echo var_dump($val);
  echo "</pre>";
}

このメソッドの引数には、スプレッドシート ID と範囲( range )を渡す。戻り値は ValueRange オブジェクトが返される。

$range のとこでデータを読み取る範囲を指定している。
範囲は、 ‘シート名!セルの範囲’ とする。
セルの範囲は、セル 1 つなら「 'シート1!B2' 」と書く。 値が入力されている最後のセルまでと指定したい場合は、「 'シート1!B2:D' 」のようにアルファベットだけ書く。 「 'シート1!B:D' 」と書くこともでき、この場合は、セル B1 から始まる。

ループの中で取得している( $val に入ってくるデータ)のは、 ValueRange オブジェクトなので、各セルの値を取り出すには、 $val[0] のように添字でアクセスする。

出力結果

array(3) {
  [0]=>
  string(9) "セル B1"
  [1]=>
  string(9) "セル C1"
  [2]=>
  string(8) "セルD1"
}
array(3) {
  [0]=>
  string(9) "セル B2"
  [1]=>
  string(0) ""
  [2]=>
  string(8) "セルD2"
}
array(3) {
  [0]=>
  string(9) "セル B3"
  [1]=>
  string(0) ""
  [2]=>
  string(8) "セルD3"
}
array(3) {
  [0]=>
  string(9) "セル B4"
  [1]=>
  string(0) ""
  [2]=>
  string(8) "セルD4"
}
array(2) {
  [0]=>
  string(9) "セル B5"
  [1]=>
  string(9) "セル C5"
}

D 列の最後のセルは値が存在せず、配列の要素自体が存在しない。
C 列の中間には値がないセルが存在している。この場合、配列の要素は存在するが、値が空文字となっている。

列で読み込み

オプション majorDimension の値を、 'COLUMNS' に設定する。

PHP コード
//シート情報を操作するインスタンスを生成
$sheet = new Google_Service_Sheets($client);

$spreadsheetId = 'スプレッドシートのID';
$range = 'シート1!B2:D';
$options = [
    'majorDimension' => 'COLUMNS'
];
$response = $sheet->spreadsheets_values->get($spreadsheetId, $range, $options);
$values = $response->getValues();

出力結果

array(5) {
  [0]=>
  string(9) "セル B1"
  [1]=>
  string(9) "セル B2"
  [2]=>
  string(9) "セル B3"
  [3]=>
  string(9) "セル B4"
  [4]=>
  string(9) "セル B5"
}
array(5) {
  [0]=>
  string(9) "セル C1"
  [1]=>
  string(0) ""
  [2]=>
  string(0) ""
  [3]=>
  string(0) ""
  [4]=>
  string(9) "セル C5"
}
array(4) {
  [0]=>
  string(8) "セルD1"
  [1]=>
  string(8) "セルD2"
  [2]=>
  string(8) "セルD3"
  [3]=>
  string(8) "セルD4"
}

複数範囲からの読み取り

連続していない範囲を複数読み取りたい場合は、 spreadsheets.values.batchGet メソッドを使う。

このスプレッドシートの緑の部分( A2 から A の値がある最終セルと E6 から E の値がある最終セル)の値を読み取る。

Sample Spreadsheets2
Sample Spreadsheets2

PHP コード
//シート情報を操作するインスタンスを生成
$sheet = new Google_Service_Sheets($client);

$spreadsheetId = 'スプレッドシートのID';
$params = array(
  'ranges' => [
      'シート1!A2:A',
      'シート1!E2:E'
  ]
);
$response = $sheet->spreadsheets_values->batchGet($spreadsheetId, $params);
$values = $response->getValueRanges();

foreach ($values as $val) {
  echo "<pre>";
  echo var_dump($val->getValues());
  echo "</pre>";
}

このメソッドの引数には、スプレッドシート ID と範囲( range )の配列を渡す。
戻り値は、 ValueRange オブジェクトのリストが返される。そのため、ループで ValueRange オブジェクトを取得し、さらに getValues() メソッドを使ってセルのデータを取り出す。

出力結果

array(5) {
  [0]=>
  array(1) {
    [0]=>
    string(9) "セル A1"
  }
  [1]=>
  array(1) {
    [0]=>
    string(9) "セル A2"
  }
  [2]=>
  array(1) {
    [0]=>
    string(9) "セル A3"
  }
  [3]=>
  array(1) {
    [0]=>
    string(9) "セル A4"
  }
  [4]=>
  array(1) {
    [0]=>
    string(9) "セル A5"
  }
}
array(3) {
  [0]=>
  array(1) {
    [0]=>
    string(9) "セル E1"
  }
  [1]=>
  array(1) {
    [0]=>
    string(9) "セル E2"
  }
  [2]=>
  array(1) {
    [0]=>
    string(9) "セル E3"
  }
}

オブジェクトを要素とする二次元配列となっているため、セルの値を取り出すには二重ループが必要。
こんな感じで、セルの値を取り出すことができる。

foreach ($values as $col) {
  foreach($col->getValues() as $i => $val ) {
    echo $val[0];
  }
}

取得する値のフォーマットを設定する

ValueRenderOption

オプション ValueRenderOption を使うと、取得する値のフォーマットを変えることができる。

オプション値説明
FORMATTED_VALUE (デフォルト値)関数や式が使われていれば結果値、セルの「表示形式」が通貨などに設定されていれば、フォーマットされた値が返ってくる。
UNFORMATTED_VALUE式が使われていれば結果値を返すが、フォーマットは適用されない。
FORMULA式が使われている場合、結果値ではなく、式そのものが返ってくる。

DateTimeRenderOption

オプション DateTimeRenderOption を使うと、日付フォーマットを指定ことができる。

オプション値説明
SERIAL_NUMBER1899 年 12 月 30 日から数えて経過した日数が値となる。
FORMATTED_STRINGyyyy/mm/dd のようなフォーマットされた値が返ってくる。

ValueRenderOption が FORMATTED_VALUE の場合、 DateTimeRenderOption を SERIAL_NUMBER に設定しても適用されない。
ValueRenderOption が FORMATTED_VALUE ではない場合、 DateTimeRenderOption のデフォルト値は SERIAL_NUMBER となる。

オプションのサンプル

例えば、次のシートの場合、
Google Sheets API Option Sample Sheet
Google Sheets API Option Sample Sheet
セル表示形式
A2通貨(端数切り捨て)9.99
A3通貨1.11
A4パーセント10.12
A5日付1900/01/01

セル B2 から B5 は、それぞれ =A2 から =A5 となっている。

UNFORMATTED_VALUE を指定した場合

$sheet = new Google_Service_Sheets($client);
$spreadsheetId = 'スプレッドシートのID';
$range = 'シート1!A2:B5';
$options = [
    'valueRenderOption' => 'UNFORMATTED_VALUE'
];
$response = $sheet->spreadsheets_values->get($spreadsheetId, $range, $options);
$values = $response->getValues();

foreach ($values as $val) {
  echo "Aのセル:".$val[0]." /  Bのセル:".$val[1];
  echo "<br>";
}

出力結果


Aのセル:9.99 / Bのセル:9.99
Aのセル:1.11 / Bのセル:1.11
Aのセル:0.1012 / Bのセル:0.1012
Aのセル:2 / Bのセル:2

通貨記号なし、および端数切り捨ても適用されていない、パーセントはパーセント換算前の値となっている。
日付は、1899 年 12 月 30 日から数えて 2 日後なので、 2 となっている。

ValueRenderOption に FORMULA , DateTimeRenderOption に FORMATTED_STRING を指定した場合

$options = [
    'valueRenderOption' => 'FORMULA',
    'dateTimeRenderOption' => 'FORMATTED_STRING'
];

出力結果


Aのセル:9.99 / Bのセル:=A2
Aのセル:1.11 / Bのセル:=A3
Aのセル:0.1012 / Bのセル:=A4
Aのセル:1900/01/01 / Bのセル:=A5

B のセルの取得値は、式形式となっている。日付は、日付フォーマットのまま取得されている。

Posted by Agopeanuts