Google Sheets APIでセルの値を読み込む方法

Googleスプレッドシートは[Sheets API](https://developers.google.com/sheets/api/)を使用してプログラムから操作することが可能です。
APIにはセル値を取得する方法が **3種類** 用意されています。
本記事はそれぞれの[使用方法] [違い] [どれを使えばいいか]などをまとめた記事です。


# 前提条件
Sheets APIの基本的な使い方を理解していること。
知らない方は以下の記事を参照。
[Googleスプレッドシートをプログラムから操作 - Qiita](http://qiita.com/howdy39/items/ca719537bba676dce1cf)

# 方法その1 spreadsheets.get
[Method: spreadsheets.get  |  Sheets API  |  Google Developers](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/get)
 
`{spreadsheetId}`をパスに指定するリクエストです。
`GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}`


## includeGridDataでセルの情報も取得
そのままではセルの情報は取得できません。シートのタイトルなど全般的な情報だけです。
そのためGETパラメータに`includeGridData=true`を付与します。

`GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}?includeGridData=true`

## rangesでセルの範囲を指定
範囲を指定しないと全てのシートの全てのセル情報を取ることになります。
シートが3つだけで値をほとんど書いていないスプレッドシートですら、10,000行のJSONが返ってきます。
セルが初期値のままでも画面上に存在しているセル情報を取得してしまうためです。

そこで`ranges`パラメータを使用します。
`ranges=シート1!A1:A2`のように付与します。

`GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}?includeGridData=true&ranges=シート1!A1:A2`

※`ranges`パラメータは複数送ることが可能です。


## fieldsで情報を絞る
値を書いただけの1セル情報だけでも、以下のような膨大な情報量になります。

```json
{
    "userEnteredValue": {
        "stringValue": "シート1のA1"
    },
    "effectiveValue": {
        "stringValue": "シート1のA1"
    },
    "formattedValue": "シート1のA1",
    "effectiveFormat": {
        "backgroundColor": {
            "red": 1,
            "green": 1,
            "blue": 1
        },
        "padding": {
            "top": 2,
            "right": 3,
            "bottom": 2,
            "left": 3
        },
        "horizontalAlignment": "LEFT",
        "verticalAlignment": "BOTTOM",
        "wrapStrategy": "OVERFLOW_CELL",
        "textFormat": {
            "foregroundColor": {},
            "fontFamily": "arial,sans,sans-serif",
            "fontSize": 10,
            "bold": false,
            "italic": false,
            "strikethrough": false,
            "underline": false
        },
        "hyperlinkDisplayType": "PLAIN_TEXT"
    }
}
```

そこでレスポンスのjsonを削るために`fields`パラメータを使用して必要な部分だけに絞ります。
`fields=sheets.data.rowData.values(userEnteredValue,effectiveValue,formattedValue)`

```json
{
    "userEnteredValue": {
        "stringValue": "シート1のA1"
    },
    "effectiveValue": {
        "stringValue": "シート1のA1"
    },
    "formattedValue": "シート1のA1"
}
```

fieldsパラメータの使い方は以下の記事を参照してください。
[fieldsパラメータの使い方](http://qiita.com/howdy39/items/2eca13609901a22fcbb8#fields%E3%83%91%E3%83%A9%E3%83%A1%E3%83%BC%E3%82%BF%E3%82%92%E6%8C%87%E5%AE%9A)


## 値が3つあるのはなぜ？
以下のような関数式が埋め込まれている場合がわかりやすいです。
![image.png](https://qiita-image-store.s3.amazonaws.com/0/35595/96fee021-2302-d31b-1da4-07275ceffd81.png)

userEnteredValue、effectiveValue、formattedValueにはそれぞれ
入力された値、評価された値、整形された値（見た目上の値）となります。

```json
{
    "userEnteredValue": {
        "formulaValue": "=A1"
    },
    "effectiveValue": {
        "numberValue": 1000
    },
    "formattedValue": "1,000.00"
}
```


# 方法その2 spreadsheets.values.get
[Method: spreadsheets.values.get  |  Sheets API  |  Google Developers](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/get)

`{spreadsheetId}`と`{range}`をパスに指定するリクエストです。
`GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}`

## rangeを指定
`range`は方法その1と同じように指定すればOKです。

`GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/シート1!A1:C2`

```json
{
  "range": "'シート1'!A1:C2",
  "majorDimension": "ROWS",
  "values": [
    [
      "シート1のA1",
      "シート1のB1",
      "シート1のC1"
    ],
    [
      "シート1のA2",
      "シート1のB2",
      "シート1のC2"
    ]
  ]
}
```

## majorDimensionで行と列の主体を切り替える
先程のレスポンスに`majorDimension`というのがありましたが、これは2次元配列で行と列のどちらを主体としているかの情報です。
これを明示的にmajorDimensionパラメータで切り替えられます。
[Collection: spreadsheets.values#Dimension  |  Sheets API  |  Google Developers](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values#Dimension)

`majorDimension=COLUMNS`を指定した場合、以下のようになります。

```json
{
  "range": "'シート1'!A1:C2",
  "majorDimension": "COLUMNS",
  "values": [
    [
      "シート1のA1",
      "シート1のA2"
    ],
    [
      "シート1のB1",
      "シート1のB2"
    ],
    [
      "シート1のC1",
      "シート1のC2"
    ]
  ]
}
```


## valueRenderOptionで取得する値の種類を変える
デフォルトで取れるのは方法その1ででてきた`formattedValue`です。
その他の2つを取得するには`valueRenderOption`を設定します。
[ValueRenderOption  |  Sheets API  |  Google Developers](https://developers.google.com/sheets/api/reference/rest/v4/ValueRenderOption)


## dateTimeRenderOptionで日付データ値の種類を変える
`valueRenderOption`の日付版みたいなオプションです。
「基本的には入力値をそのまま取得したいけど、日付だけはシリアル値じゃなくて整形された値が欲しい。」
みたいなときに使います。
[DateTimeRenderOption  |  Sheets API  |  Google Developers](https://developers.google.com/sheets/api/reference/rest/v4/DateTimeRenderOption)


# 方法その３ spreadsheets.values.batchGet
[Method: spreadsheets.values.batchGet  |  Sheets API  |  Google Developers](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchGet)

`{spreadsheetId}`をパスに指定するリクエストです。
`GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchGet`

## 各種リクエストパラメータを使用する
`方法その2 spreadsheets.values.get`で紹介したパラメータに加えて`ranges`パラメータが使えます。

そのため以下のようなリクエストが可能です。

`GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchGet?ranges=シート1!A1:B2&majorDimension=COLUMNS&dateTimeRenderOption=FORMATTED_STRING&valueRenderOption=FORMULA`


## spreadsheets.values.getとの違いは？
`batchGet`の名前の通り`values.get`を複数まとめて実行することができます。
`ranges`となっているように`ranges`パラメータは複数送れます。[^1]
[^1]: 気づくのにすこし時間がかかった

`GET https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchGet?ranges=シート1!A1:B2&ranges=シート1!C1:C3`

上記のリクエストの場合は以下のようなレスポンスになります。

```json
{
  "spreadsheetId": "1428x4m9MSiMerQizvKXWbKhXh_a3Ye6lHhUl0HwjkEw",
  "valueRanges": [
    {
      "range": "'シート1'!A1:B2",
      "majorDimension": "ROWS",
      "values": [
        [
          "シート1のA1",
          "シート1のB1"
        ],
        [
          "シート1のA2",
          "シート1のB2"
        ]
      ]
    },
    {
      "range": "'シート1'!C1:C3",
      "majorDimension": "ROWS",
      "values": [
        [
          "シート1のC1"
        ],
        [
          "シート1のC2"
        ],
        [
          "シート1のC3"
        ]
      ]
    }
  ]
}
```


# どれを使うか
筆者の選択基準と一覧を記載しておきます。


## 筆者の選択基準
1. セルの値だけではなく書式やスプレッドシート情報も必要 → 方法その1（その１以外はそもそも取れないので）
2. 入力値の型とか気にしなくていいから手軽に取得したい → 方法その3
3. 大幅に改修する可能性がある → 方法その1 （どう転んでも対応できる）

その2は筆者は使いません。


## 一覧

|方法|セルの値|セルの書式|範囲指定|離れているセルの範囲指定|スプレッドシートの基本情報|実装の容易さ|
|---|---|---|---|---|---|---|
|方法その1 spreadsheets.get|◎ [※1](http://qiita.com/howdy39/items/5473160c93030c386c2d#1)|○|○|○|○|× [※2](http://qiita.com/howdy39/items/5473160c93030c386c2d#1)|
|方法その2 spreadsheets.values.get|○ ※[3](http://qiita.com/howdy39/items/5473160c93030c386c2d#3)|×|○|×|×|○|
|方法その3 spreadsheets.values.batchGet|○ [※3](http://qiita.com/howdy39/items/5473160c93030c386c2d#3)|×|○|○|×|○|


#### #1
入力された値、評価された値、整形された値を同時に取得可能

#### #2
`formattedValue`以外を使う場合は、JSONのプロパティ名が文字列・数値・関数でそれぞれ変わってくるのでハンドリングが大変です。
型がある言語だとなおさら面倒です。

```json:formulaValueだったりnumberValueだったり入力値で変動するのが大変
{
    "userEnteredValue": {
        "formulaValue": "=A1"
    },
    "effectiveValue": {
        "numberValue": 1000
    },
    "formattedValue": "1,000.00"
}
```

#### #3
問題になるケースがあります。
以下のような関数としての`=A4`と、文字列としての`=A4`があった場合、`valueRenderOption=FORMULA`を指定しているとどっちも`=A4`としてレスポンスが返ってきます。
これを区別するには`valueRenderOption=FORMULA`以外を送ってそれぞれを比較する必要があります。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/35595/3d77cd60-ba23-cb85-e587-03bb67c1c5c0.png)

```json:どっちもA4になる
{
  "range": "'シート1'!A4:A6",
  "majorDimension": "COLUMNS",
  "values": [
    [
      "2017/06/06 0:00:00",
      "=A4",
      "=A4"
    ]
  ]
}
```