Google Sheets APIでセルの値を書き込む方法

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

読み込み方法の記事もあるのでどうぞ。
[Google Sheets APIでセルの値を読み込む方法 - Qiita](http://qiita.com/howdy39/items/5473160c93030c386c2d)

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


# その１ UpdateCellsRequest

[SpreadSheets.batchUpdate](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate)でUpdateCellsRequestを使う方法です。

```
POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate
```

## Path parameters
`spreadsheetId`をパスに含めてスプレッドシートを指定します。

## Request body

書き込む内容は[UpdateCellsRequest](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#updatecellsrequest)形式で送ります。
ここではUpdateCellRequestの各々のプロパティについて解説していきます。

```text:UpdateCellRequest
{
  "rows": [
    {
      object(RowData)
    }
  ],
  "fields": string,

  // Union field area can be only one of the following:
  "start": {
    object(GridCoordinate)
  },
  "range": {
    object(GridRange)
  },
  // End of list of possible types for union field area.
}
```

### startプロパティ
[GridCoordinate型](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#GridCoordinate)で指定します。

`sheetId` `rowIndex` `columnIndex`を指定して、書き込むシートとセルの開始地点を指定します。
一番左上のA1セルは(0, 0)となります。
例）A2セルに書き込むときは(columnIndex, rowIndex)が(0, 1)になります。

### rangeプロパティ
[GridRange型](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#GridRange)で指定します。

`sheetId` `startRowIndex` `endRowIndex` `startColumnIndex` `endColumnIndex`を指定して書き込むセルの開始地点と終了地点を指定できます。
A2セルに書き込むときは以下のようになります。

- (startColumnIndex, startRowIndex) ->(0, 1)
- (endColumnIndex, endRowIndex) -> (1, 2)

`start`プロパティと違うのは、指定したセルの範囲外に書き込むようなデータが指定された場合、以下のようなエラーが発生する点です。
`Attempting to write column: 3, beyond the last requested column of: 2`
そのため`range`で指定したほうが書き込むセルを明示的に指定できるため安全です。

また`endRowIndex`と`endColumnIndex`は省略可能です。
省略した場合は無限が指定されているような扱いになるため`start`パラメータ使用時と同等の動きになります。


### startとrangeはどっちを使うべきか
`range`が上位互換なので`range`でいいでしょう。
また`start`と`range`を両方指定することはできません。


### rowsプロパティ
書き込むデータの本体です。

[RowData型](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#RowData)の配列で指定します。
`RowData`は1行のセルを表し、[Celldata](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#CellData)は1つのセルを表します。
`rows`が複数行を表し`values`が同一行内の複数セルを表します。

```
"rows": [
  // object(RowData)        
  {
    "values": [
      {
        // object(CellData)
      }
    ],
  }
],
```

### rows.values.CellData.userEnteredValueプロパティ
セルに値を書き込むにはCellData内の`userEnteredValue`プロパティを使います。
`userEnteredValue`は[ExtendedValue型](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#ExtendedValue)で指定します。

`ExtendedValued`には`数値`・`文字列`・`真偽値`・`関数`を表す`numberValue`・`stringValue`・`boolValue`・`formulaValue`がそれぞれ用意されています。[^1]
[^1]: errorValueは読み取り専用で書き込むことはできません。

型情報をまとめると以下になります。

```json
{
  "values": [
    {
      "userEnteredValue": {
        "numberValue": number,
        "stringValue": string,
        "boolValue": boolean,
        "formulaValue": string,
      },
    }
  ],
}
```

例）数値で100を書き込みたいときは`{"numberValue": 100}`を指定します。

```json
{
  "values": [
    {
      "userEnteredValue": {
        "numberValue": 100,
      },
    }
  ],
}
```


### fieldsプロパティ
`fields`で書き込むプロパティを絞れます。
`fields`は必須プロパティで、対象のプロパティが指定されていない場合は、リクエストに含まれていても無視されます。
想定していないプロパティを間違って変更しないための安全用フィルターみたいなものです。

また`fields`は **`rows.values`からのプロパティ名を指定**します。
セルに値を書き込むだけの場合は`{"fields": "userEnteredValue"}`を指定しておくと良いでしょう。
※本記事では紹介しませんがセルの書式を書き込む場合は`{"fields": "userEnteredFormat"}`を指定します。
※書き込み範囲を絞りたくない場合は`"*"`を指定します。


## 実際のリクエスト例
ここまでのまとめとしてリクエスト例を記載しておきます。

### リクエスト
```
POST https://sheets.googleapis.com/v4/spreadsheets/1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA:batchUpdate
```

```json
{
  "requests": [
    {
      "updateCells": {
        "range": {
            "sheetId": 0,
            "startRowIndex": 0,
            "endRowIndex": 1,
            "startColumnIndex": 0,
            "endColumnIndex": 4
        },
        "rows": [
            {
              "values": [
                { "userEnteredValue": { "stringValue": "文字列"} },
                { "userEnteredValue": { "numberValue": 12345} },
                { "userEnteredValue": { "boolValue": false} },
                { "userEnteredValue": { "formulaValue": "=B1"} },
            ],
          },
        ],
        "fields": "userEnteredValue"
      }
    }
  ]
}
```

### レスポンス
```json
{
  "spreadsheetId": "1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA",
  "replies": [
    {}
  ]
}
```

### 画面
![image.png](https://qiita-image-store.s3.amazonaws.com/0/35595/fb4aa412-e38a-2cef-ab39-3531fc6fbe77.png)



# その２ spreadsheets.values.update
[spreadsheets.values.update](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/update)を使う方法です。

```
PUT https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}
```

## Path parameters
`spreadsheetId` `range`をパスに含めてスプレッドシートと書き込むセルの範囲を指定します。
`range`の指定は一般的な`'シート1'!A2`みたいな形式です。

## Query parameters

### ValueInputOptionパラメータ
値を書き込む際の形式を指定する必要があります。
それが[ValueInputOption](https://developers.google.com/sheets/api/reference/rest/v4/ValueInputOption)です。

```
PUT https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}?valueInputOption=USER_ENTERED
```

指定できる値には`RAWS`と`USER_ENTERED`があります。
それぞれの設定と各形式でセルに書かれる値を表にしておきます。

|形式|リクエストの設定例|RAW|USER_ENTERED|
|:---|:---|:---|:---|
|数値の100|100|100|100|
|文字列の100|"100"|'100|100|
|関数の=A1|"=A1"|'=A1|=A1|
|日付（シリアル値）|42892|42892|42892|
|真偽値のtrue|true|TRUE|TRUE|
|文字列のtrue|"true"|'true|TRUE|

```text:指定したリクエストボディ（詳細は後で解説します）
{
  "majorDimension": "COLUMNS",
  "values": [
    [
      100,
      "100",
      "=A1",
      42892,
      true,
      "true",
    ]
  ],
}
```


※日付はシリアル値なので数値でいれて書式で日付にしてあります。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/35595/a86369fc-fe77-533a-2deb-e6e25997bc7a.png)


#### RAWSとUSER_ENTEREDはどっちを使うべきか
直感的な`USER_ENTERED`を使うのが良いでしょう。
`USER_ENTERED`で文字列を入れたかったら書き込みたい値の頭に`'`（シングルクォート）をつければOKです。
逆を言えば`RAWS`は文字列を指定すると自動でシングルクォートをつけるということです。


### includeValuesInResponseパラメータ
デフォルトではレスポンスに更新したセルの値は含まれていません。
必要な場合は`includeValuesInResponse`にtrueを指定します。


### responseValueRenderOptionパラメータ
前述の`includeValuesInResponse=true`で取得する値の形式を設定します。

[valueRenderOptionで取得する値の種類を変える](http://qiita.com/howdy39/items/5473160c93030c386c2d#valuerenderoption%E3%81%A7%E5%8F%96%E5%BE%97%E3%81%99%E3%82%8B%E5%80%A4%E3%81%AE%E7%A8%AE%E9%A1%9E%E3%82%92%E5%A4%89%E3%81%88%E3%82%8B)


### responseDateTimeRenderOptionパラメータ
前述の`includeValuesInResponse=true`で取得する日付値の形式を設定します。

[dateTimeRenderOptionで日付データ値の種類を変える]
(http://qiita.com/howdy39/items/5473160c93030c386c2d#datetimerenderoption%E3%81%A7%E6%97%A5%E4%BB%98%E3%83%87%E3%83%BC%E3%82%BF%E5%80%A4%E3%81%AE%E7%A8%AE%E9%A1%9E%E3%82%92%E5%A4%89%E3%81%88%E3%82%8B)


## Request body
書き込む内容は[ValueRange型](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values#ValueRange)で送ります。


### rangeプロパティ
`range`はJSONのプロパティにも入れられますが、クエリパラメータと一致している必要があります。
クエリパラメータの`range`は必須のためJSONの`range`を指定するメリットはありません。


### majorDimensionプロパティ
値の行と列の設定方法を指定します。
[majorDimensionで行と列の主体を切り替える](http://qiita.com/howdy39/items/5473160c93030c386c2d#majordimension%E3%81%A7%E8%A1%8C%E3%81%A8%E5%88%97%E3%81%AE%E4%B8%BB%E4%BD%93%E3%82%92%E5%88%87%E3%82%8A%E6%9B%BF%E3%81%88%E3%82%8B)

### valuesプロパティ
セルに書き込む値を指定します。
こちらも以下の記事を参照してください。
[majorDimensionで行と列の主体を切り替える](http://qiita.com/howdy39/items/5473160c93030c386c2d#majordimension%E3%81%A7%E8%A1%8C%E3%81%A8%E5%88%97%E3%81%AE%E4%B8%BB%E4%BD%93%E3%82%92%E5%88%87%E3%82%8A%E6%9B%BF%E3%81%88%E3%82%8B)


## 実際のリクエスト例
ここまでのまとめとして値を書き込むリクエスト例を記載しておきます。

### リクエスト
```
PUT https://sheets.googleapis.com/v4/spreadsheets/1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA/values/'シート2'!B2:B7?valueInputOption=USER_ENTERED&includeValuesInResponse=true&responseValueRenderOption=UNFORMATTED_VALUE&responseDateTimeRenderOption=FORMATTED_STRING
```

```json
{
  "majorDimension": "COLUMNS",
  "values": [
    [
      100,
      "'100",
      "=A1",
      42892,
      true,
      "'true",
    ]
  ],
}
```

### レスポンス
```json
{
  "spreadsheetId": "1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA",
  "updatedRange": "'シート2'!B2:B7",
  "updatedRows": 6,
  "updatedColumns": 1,
  "updatedCells": 6,
  "updatedData": {
    "range": "'シート2'!B2:B7",
    "majorDimension": "COLUMNS",
    "values": [
      [
        100,
        "100",
        "A1セルです！",
        "2017/06/06",
        true,
        "true"
      ]
    ]
  }
}
```

### 画面
![image.png](https://qiita-image-store.s3.amazonaws.com/0/35595/31df5443-6b0c-a473-6b5c-8fec8f696250.png)



# その３ spreadsheets.values.batchUpdate
[spreadsheets.values.batchUpdate](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values/batchUpdate)を使う方法です。
この方法は`その２ spreadsheets.values.update`を一括で実行するためのAPIです。
そのためとても酷似しています。

```
POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values:batchUpdate
```
※PUTではなくPOSTです、注意してください。


## Path parameters
`spreadsheetId`をパスに含めてスプレッドシートを指定します。
※その2の方法では`range`がありましたが、その3の方法ではリクエストボディの中に`range`を記述します。


## Query parameters
固有のクエリパラメータはありません。
その2で出てきたクエリパラメータは全てリクエストボディの中に記述します。


## Request body
リクエストボディのJSON例が以下のようになります。
data以外は全てその２で出てきたクエリパラメータと同じです。


```JSON
{
  "valueInputOption": enum(ValueInputOption),
  "data": [
    {
      object(ValueRange)
    }
  ],
  "includeValuesInResponse": boolean,
  "responseValueRenderOption": enum(ValueRenderOption),
  "responseDateTimeRenderOption": enum(DateTimeRenderOption),
}
```

### dataプロパティ
その2でも出てきた[ValueRange型](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets.values#ValueRange)を**配列**で指定します。
その2では`range`がクエリパラメータなので1つしか指定できませんが、その３の方法ではここが配列のため複数指定ができるわけです。


## 実際のリクエスト例

### リクエスト
```
POST https://sheets.googleapis.com/v4/spreadsheets/1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA/values:batchUpdate
```

```JSON
{
  "valueInputOption": "USER_ENTERED",
  "data": [
    {
      "range": "'シート2'!B2:B7",
      "majorDimension": "COLUMNS",
      "values": [
        [
          100,
          "'100",
          "=A1",
          42892,
          true,
          "'true",
        ]
      ],
    },
    {
      "range": "'シート2'!C2:C7",
      "majorDimension": "COLUMNS",
      "values": [
        [
          "C2",
          "C3",
          "C4",
          "C5",
          "C6",
          "C7",
        ]
      ],
    }
  ],
  "includeValuesInResponse": true,
  "responseValueRenderOption": "UNFORMATTED_VALUE",
  "responseDateTimeRenderOption": "FORMATTED_STRING",
}
```

### レスポンス
```JSON
{
  "spreadsheetId": "1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA",
  "totalUpdatedRows": 6,
  "totalUpdatedColumns": 2,
  "totalUpdatedCells": 12,
  "totalUpdatedSheets": 1,
  "responses": [
    {
      "spreadsheetId": "1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA",
      "updatedRange": "'シート2'!B2:B7",
      "updatedRows": 6,
      "updatedColumns": 1,
      "updatedCells": 6,
      "updatedData": {
        "range": "'シート2'!B2:B7",
        "majorDimension": "COLUMNS",
        "values": [
          [
            100,
            "100",
            "",
            "2017/06/06",
            true,
            "true"
          ]
        ]
      }
    },
    {
      "spreadsheetId": "1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA",
      "updatedRange": "'シート2'!C2:C7",
      "updatedRows": 6,
      "updatedColumns": 1,
      "updatedCells": 6,
      "updatedData": {
        "range": "'シート2'!C2:C7",
        "majorDimension": "COLUMNS",
        "values": [
          [
            "C2",
            "C3",
            "C4",
            "C5",
            "C6",
            "C7"
          ]
        ]
      }
    }
  ]
}
```

### 画面
![image.png](https://qiita-image-store.s3.amazonaws.com/0/35595/e646f9e4-cc07-5078-50f6-5a89cc8666e7.png)


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


## 筆者の選択基準
1. セルの値だけではなく書式やスプレッドシート情報の変更も必要 → 方法その1（その１以外はそもそも値以外の変更が出来ないので）
2. 値だけ更新したい → 方法その3
3. 大幅に改修する可能性がある → 方法その1 （どう転んでも対応できる）

その2ですが、筆者は使いません。


## 一覧

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

### #1
範囲指定は[GridRange型](https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#GridRange)なのが使いづらいです。
セルの指定は数値(0,0)よりは文字列(A1)の方が直感的でしょう。

また、シート名ではなくシートIDを使う必要があります。
そのため、初期シート以外のシートがある場合、シートごとのIDを別途保持ないし、都度APIで取得してからでないと使えません。[^2]
[^2]: 初期で作成されるシートIDは0ですが新しく増やしたシートIDはランダムな数字が振られます。
**逆にその２・その３の方法はシート名を変えられると動かなくなる、とも言えます。**