[PHP] Google Sheets API でスプレッドシートの値を取得

2019-08-11PHPGoogle

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

範囲 1 つの読み取り

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

行で読み込み

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

//シート情報を操作するインスタンスを生成
$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 "
";
  echo var_dump($val);
  echo "
";
}

このメソッドの引数には、スプレッドシート 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' に設定する。

//シート情報を操作するインスタンスを生成
$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 の値がある最終セル）の値を読み取る。

//シート情報を操作するインスタンスを生成
$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 "
";
  echo var_dump($val->getValues());
  echo "
";
}

このメソッドの引数には、スプレッドシート 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 を使うと、取得する値のフォーマットを変えることができる。

DateTimeRenderOption

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

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

オプションのサンプル

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 "
";
}

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 のセルの取得値は、式形式となっている。日付は、日付フォーマットのまま取得されている。