GoogleスプレッドシートはSheets APIを使用してプログラムから操作することが可能です。
APIにはセル値を書き込む方法が 3種類 用意されています。
本記事はそれぞれの[使用方法] [違い] [どれを使えばいいか]などをまとめた記事です。
読み込み方法の記事もあるのでどうぞ。
Google Sheets APIでセルの値を読み込む方法 - Qiita
前提条件
Sheets APIの基本的な使い方を理解していること。
知らない方は以下の記事を参照。
Googleスプレッドシートをプログラムから操作 - Qiita
その１ UpdateCellsRequest
SpreadSheets.batchUpdateでUpdateCellsRequestを使う方法です。
POST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdate
Path parameters
spreadsheetIdをパスに含めてスプレッドシートを指定します。
Request body
書き込む内容はUpdateCellsRequest形式で送ります。
ここでは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型で指定します。
sheetId rowIndex columnIndexを指定して、書き込むシートとセルの開始地点を指定します。
一番左上のA1セルは(0, 0)となります。
例）A2セルに書き込むときは(columnIndex, rowIndex)が(0, 1)になります。
rangeプロパティ
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型の配列で指定します。
RowDataは1行のセルを表し、Celldataは1つのセルを表します。
rowsが複数行を表しvaluesが同一行内の複数セルを表します。
"rows": [
// object(RowData)
{
"values": [
{
// object(CellData)
}
],
}
],
rows.values.CellData.userEnteredValueプロパティ
セルに値を書き込むにはCellData内のuserEnteredValueプロパティを使います。
userEnteredValueはExtendedValue型で指定します。
ExtendedValuedには数値・文字列・真偽値・関数を表すnumberValue・stringValue・boolValue・formulaValueがそれぞれ用意されています。1
型情報をまとめると以下になります。
{
"values": [
{
"userEnteredValue": {
"numberValue": number,
"stringValue": string,
"boolValue": boolean,
"formulaValue": string,
},
}
],
}
例）数値で100を書き込みたいときは{"numberValue": 100}を指定します。
{
"values": [
{
"userEnteredValue": {
"numberValue": 100,
},
}
],
}
fieldsプロパティ
fieldsで書き込むプロパティを絞れます。
fieldsは必須プロパティで、対象のプロパティが指定されていない場合は、リクエストに含まれていても無視されます。
想定していないプロパティを間違って変更しないための安全用フィルターみたいなものです。
またfieldsは rows.valuesからのプロパティ名を指定します。
セルに値を書き込むだけの場合は{"fields": "userEnteredValue"}を指定しておくと良いでしょう。
※本記事では紹介しませんがセルの書式を書き込む場合は{"fields": "userEnteredFormat"}を指定します。
※書き込み範囲を絞りたくない場合は"*"を指定します。
実際のリクエスト例
ここまでのまとめとしてリクエスト例を記載しておきます。
リクエスト
POST https://sheets.googleapis.com/v4/spreadsheets/1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA:batchUpdate
{
"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"
}
}
]
}
レスポンス
{
"spreadsheetId": "1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA",
"replies": [
{}
]
}
画面
その２ spreadsheets.values.update
spreadsheets.values.updateを使う方法です。
PUT https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}/values/{range}
Path parameters
spreadsheetId rangeをパスに含めてスプレッドシートと書き込むセルの範囲を指定します。
rangeの指定は一般的な'シート1'!A2みたいな形式です。
Query parameters
ValueInputOptionパラメータ
値を書き込む際の形式を指定する必要があります。
それが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 |
{
"majorDimension": "COLUMNS",
"values": [
[
100,
"100",
"=A1",
42892,
true,
"true",
]
],
}
※日付はシリアル値なので数値でいれて書式で日付にしてあります。
RAWSとUSER_ENTEREDはどっちを使うべきか
直感的なUSER_ENTEREDを使うのが良いでしょう。
USER_ENTEREDで文字列を入れたかったら書き込みたい値の頭に'（シングルクォート）をつければOKです。
逆を言えばRAWSは文字列を指定すると自動でシングルクォートをつけるということです。
includeValuesInResponseパラメータ
デフォルトではレスポンスに更新したセルの値は含まれていません。
必要な場合はincludeValuesInResponseにtrueを指定します。
responseValueRenderOptionパラメータ
前述のincludeValuesInResponse=trueで取得する値の形式を設定します。
valueRenderOptionで取得する値の種類を変える
responseDateTimeRenderOptionパラメータ
前述のincludeValuesInResponse=trueで取得する日付値の形式を設定します。
dateTimeRenderOptionで日付データ値の種類を変える
Request body
書き込む内容はValueRange型で送ります。
rangeプロパティ
rangeはJSONのプロパティにも入れられますが、クエリパラメータと一致している必要があります。
クエリパラメータのrangeは必須のためJSONのrangeを指定するメリットはありません。
majorDimensionプロパティ
値の行と列の設定方法を指定します。
majorDimensionで行と列の主体を切り替える
valuesプロパティ
セルに書き込む値を指定します。
こちらも以下の記事を参照してください。
majorDimensionで行と列の主体を切り替える
実際のリクエスト例
ここまでのまとめとして値を書き込むリクエスト例を記載しておきます。
リクエスト
PUT https://sheets.googleapis.com/v4/spreadsheets/1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA/values/'シート2'!B2:B7?valueInputOption=USER_ENTERED&includeValuesInResponse=true&responseValueRenderOption=UNFORMATTED_VALUE&responseDateTimeRenderOption=FORMATTED_STRING
{
"majorDimension": "COLUMNS",
"values": [
[
100,
"'100",
"=A1",
42892,
true,
"'true",
]
],
}
レスポンス
{
"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"
]
]
}
}
画面
その３ spreadsheets.values.batchUpdate
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以外は全てその２で出てきたクエリパラメータと同じです。
{
"valueInputOption": enum(ValueInputOption),
"data": [
{
object(ValueRange)
}
],
"includeValuesInResponse": boolean,
"responseValueRenderOption": enum(ValueRenderOption),
"responseDateTimeRenderOption": enum(DateTimeRenderOption),
}
dataプロパティ
その2でも出てきたValueRange型を配列で指定します。
その2ではrangeがクエリパラメータなので1つしか指定できませんが、その３の方法ではここが配列のため複数指定ができるわけです。
実際のリクエスト例
リクエスト
POST https://sheets.googleapis.com/v4/spreadsheets/1abJYVvu9xjSW1_VTDI86BWnh0hCe6l3zllHCtZvYleA/values:batchUpdate
{
"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",
}
レスポンス
{
"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"
]
]
}
}
]
}
画面
どれを使うか
筆者の選択基準と一覧を記載しておきます。
筆者の選択基準
- セルの値だけではなく書式やスプレッドシート情報の変更も必要 → 方法その1（その１以外はそもそも値以外の変更が出来ないので）
- 値だけ更新したい → 方法その3
- 大幅に改修する可能性がある → 方法その1 （どう転んでも対応できる）
その2ですが、筆者は使いません。
一覧
| 方法 | セルの値 | セルの書式 | 範囲指定 | 離れているセルの範囲指定 | スプレッドシートの基本情報 | 実装の容易さ |
|---|---|---|---|---|---|---|
| 方法その1 UpdateCellsRequest | ○ | ○ | ○ | ○ | ○ | ×※1 |
| 方法その2 spreadsheets.values.update | ○ | × | ○ | × | × | ○ |
| 方法その3 spreadsheets.values.batchUpdate | ○ | × | ○ | ○ | × | ○ |
#1
範囲指定はGridRange型なのが使いづらいです。
セルの指定は数値(0,0)よりは文字列(A1)の方が直感的でしょう。
また、シート名ではなくシートIDを使う必要があります。
そのため、初期シート以外のシートがある場合、シートごとのIDを別途保持ないし、都度APIで取得してからでないと使えません。2
逆にその２・その３の方法はシート名を変えられると動かなくなる、とも言えます。