GoogleスプレッドシートAPIの高速化

TL;DR

スプレッドシートAPIは愚直にセルに値を設定するリクエストだととても遅くなる。
人が画面で操作する要領でリクエストを記述する。それにより効率的なAPI実行となり高速化につながる。

前提条件

基本的なスプレッドシートAPIの使い方を理解していること。

理解していない or そもそもGoogleスプレッドシートAPIって何？という方は以下の記事をどうぞ。
Googleスプレッドシートをプログラムから操作

作りたいスプレッドシート

以下の値をスプレッドシートに書き込みたいとします。

1. A1に1が格納されており右にすすむにつれて1ずつ加算（100まで）
2. 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"} },
...こんなかんじで１万行になるので省略

全部のデータは以下ファイルを参照
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つずつセルに値を書いているのと同じことになります。

1. A1セルに1を書く
2. A2セルに2を書く
3. A3セルに3を書く
4. これが1万回つづく

※遅くなる原因として送信時のリクエストサイズが大きいというのも多少あると思います。

良い例

一方、良い例は人の操作同様に3アクションです。

1. A1セルに1を書く
2. A1セルをオートフィルで横方向に伸ばす
3. 1行目をコピーして縦方向にペーストする

1万回の操作が3回になったのですから早くなって当然といえるでしょう。

まとめ

スプレッドシートAPIを使うときは ブラウザ画面上を操作する要領で より少ないアクション（リクエスト）で済むようにすると高速化できる。