TL;DR
スプレッドシートAPIは愚直にセルに値を設定するリクエストだととても遅くなる。
人が画面で操作する要領でリクエストを記述する。それにより効率的なAPI実行となり高速化につながる。
前提条件
基本的なスプレッドシートAPIの使い方を理解していること。
理解していない or そもそもGoogleスプレッドシートAPIって何?という方は以下の記事をどうぞ。
Googleスプレッドシートをプログラムから操作
作りたいスプレッドシート
以下の値をスプレッドシートに書き込みたいとします。
- A1に1が格納されており右にすすむにつれて1ずつ加算(100まで)
- 2行目から100行目までは1行目と同じ
つまり100行✕100列になるので10,000セルへの書き込みになります。
悪い例
updateCells
リクエストだけで10,000セルに書き込むやりかたです。
大体ですが 3秒 程度で反映されます。
{
"requests": [
{
"updateCells": {
"start": {
"sheetId": 0,
"rowIndex": 0,
"columnIndex": 0
},
"rows": [
{
"values": [
{ "userEnteredValue": { "stringValue": "1"} },
{ "userEnteredValue": { "stringValue": "2"} },
{ "userEnteredValue": { "stringValue": "3"} },
{ "userEnteredValue": { "stringValue": "4"} },
{ "userEnteredValue": { "stringValue": "5"} },
{ "userEnteredValue": { "stringValue": "6"} },
...こんなかんじで1万行になるので省略
全部のデータは以下ファイルを参照
https://github.com/howdy39/verify-spreadsheets-api/blob/master/inputdata/updatecells.json
良い例
updateCells
, autoFill
, copyPaste
を組み合わせたリクエストです。
大体ですが 0.5〜1秒 程度で反映されます。
{
"requests": [
{
"updateCells": {
"start": {
"sheetId": 0,
"rowIndex": 0,
"columnIndex": 0
},
"rows": [
{
"values": [
{ "userEnteredValue": { "stringValue": "1"} },
],
},
],
"fields": "userEnteredValue"
}
},
{
"autoFill": {
"useAlternateSeries": false,
"range": {
"sheetId": 0,
"startRowIndex": 0,
"endRowIndex": 1,
"startColumnIndex": 0,
"endColumnIndex": 100,
},
}
},
{
"copyPaste": {
"source": {
"sheetId": 0,
"startRowIndex": 0,
"endRowIndex": 1,
"startColumnIndex": 0,
"endColumnIndex": 100,
},
"destination": {
"sheetId": 0,
"startRowIndex": 1,
"endRowIndex": 100,
"startColumnIndex": 0,
"endColumnIndex": 100,
},
}
}
]
}
このリクエストがそれぞれを分解して何をやっているのかは以下のようになります。
updateCells
セルA1に1を書き込む。
autoFill
セルA1から横方向に100列目のセルまでオートフィルで伸ばす。
※1行目に1〜100が作られる。
copyPaste
1行目のセルを選択して、2行目から100行目にコピーペーストする。
なぜそんなに早くなる?
これは筆者の想像ですが、スプレッドシートAPIはブラウザ画面操作をエミュレートしたものです。
ブラウザ上で人が操作したときの動きをそのままAPI化したものと見ていいと思います。
そのためSheetの操作系リクエストにオートフィルやコピーペーストがあるわけです。
悪い例
悪い例は人の操作で言うと以下のように1つずつセルに値を書いているのと同じことになります。
- A1セルに1を書く
- A2セルに2を書く
- A3セルに3を書く
- これが1万回つづく
※遅くなる原因として送信時のリクエストサイズが大きいというのも多少あると思います。
良い例
一方、良い例は人の操作同様に3アクションです。
- A1セルに1を書く
- A1セルをオートフィルで横方向に伸ばす
- 1行目をコピーして縦方向にペーストする
1万回の操作が3回になったのですから早くなって当然といえるでしょう。
まとめ
スプレッドシートAPIを使うときは ブラウザ画面上を操作する要領で より少ないアクション(リクエスト)で済むようにすると高速化できる。