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

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

前提条件

Sheets APIの基本的な使い方を理解していること。
知らない方は以下の記事を参照。
Googleスプレッドシートをプログラムから操作 - Qiita

方法その1 spreadsheets.get

Method: spreadsheets.get  |  Sheets API  |  Google Developers

{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セル情報だけでも、以下のような膨大な情報量になります。

{
    "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)

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

fieldsパラメータの使い方は以下の記事を参照してください。
fieldsパラメータの使い方

値が3つあるのはなぜ？

以下のような関数式が埋め込まれている場合がわかりやすいです。

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

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

方法その2 spreadsheets.values.get

Method: spreadsheets.values.get  |  Sheets API  |  Google Developers

{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

{
  "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

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

{
  "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

dateTimeRenderOptionで日付データ値の種類を変える

valueRenderOptionの日付版みたいなオプションです。
「基本的には入力値をそのまま取得したいけど、日付だけはシリアル値じゃなくて整形された値が欲しい。」
みたいなときに使います。
DateTimeRenderOption  |  Sheets API  |  Google Developers

方法その３ spreadsheets.values.batchGet

Method: spreadsheets.values.batchGet  |  Sheets API  |  Google Developers

{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パラメータは複数送れます。¹

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

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

{
  "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	○	○	○	○	× ※2
方法その2 spreadsheets.values.get	○ ※3	×	○	×	×	○
方法その3 spreadsheets.values.batchGet	○ ※3	×	○	○	×	○

#1

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

#2

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

formulaValueだったりnumberValueだったり入力値で変動するのが大変

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

#3

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

どっちもA4になる

{
  "range": "'シート1'!A4:A6",
  "majorDimension": "COLUMNS",
  "values": [
    [
      "2017/06/06 0:00:00",
      "=A4",
      "=A4"
    ]
  ]
}

1. 気づくのにすこし時間がかかった ↩