gspreadでオートフィル

　gspreadには色々な関数があり、スプレッドシート上の多くの作業を簡単に実装することができます。しかし残念ながら、専用の関数が用意されていない処理もいくつかあります。今回ターゲットにするオートフィル処理もその一つです。
　そこでこの記事では、オートフィル処理をはじめとして、gspreadに関数化されていない処理を実装する方法を説明します。

Sheets API上でのオートフィルの扱い

　オートフィルを自動化するための方法は、REST APIとしては提供されています。それがAutoFillRequestというものです。

AutoFillRequest
Fills in more data based on existing data.¹

　このリクエストをGoogle Sheets APIのspreadsheets.batchUpdateメソッドで送出することで、プログラムからオートフィル処理を実現できます。²

batchUpdateメソッドの詳細

　batchUpdateメソッドの機能は、単にリクエストを送るとそれが実行されるという単純なものです。
（厳密には、リクエストを実行した結果のセル値を取得する等の機能もあるようですが、今回の主題であるオートフィルには直接関連しないため説明は割愛します。詳細は公式のリファレンス²をご覧ください）
　このメソッドのリクエストボディは次のような形をしています。

Request-Body

{
  "requests": [
    {
      object (Request)
    }
  ],
  "includeSpreadsheetInResponse": boolean,
  "responseRanges": [
    string
  ],
  "responseIncludeGridData": boolean
}

　ここで4つのフィールドが登場しましたが、ここで使うのは"requests"だけです。他の3つのフィールドは実行結果の取得に関連するもののようですから、操作が目的である今回は "includeSpreadsheetInResponse":false だけ指定しておけば差し支えありません。
　この"request"フィールドに、オートフィルに関する設定を記述したAutoFillRequestを追加すれば、オートフィル処理が完了します。
　なお、requestsの形式はRequests³として定義されています。この形式は、単に {リクエストの種類: リクエストの実体} というものです。

AutoFillRequestの詳細

　AutoFillRequestは以下のように定義されています。

AutoFillRequest

{
  "useAlternateSeries": boolean,

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

• useAlternateSeries: オートフィルの方式を指定します。Falseではデフォルトの動作をしますが、Trueにすると"alternate"モードでフィルされます。これは、WindowsではCtrl、Macではoptionを押しながらオートフィルした場合の挙動と同じで、連続した数値などを連番ではなくコピーさせるもののようです。⁴
• range: オートフィルする範囲です。指定した範囲の中で値のあるセルを探し出して、空白セルを自動的に埋めてくれます。GridRange⁵という形式で指定します。
• sourceAndDestination: オートフィルするためのデータと埋める先の範囲を別々に指定する方式です。形式はSourceAndDestination⁶として指定されています。

※rangeとsourceAndDestinationは一方しか指定してはいけません。

　今回は、より直感的なrangeの方を使用して実装を進めます。あまりないと思いますが、sourceAndDestinationが必要な場合は、リファレンス⁵を参考にしてください。

GridRangeの形式⁵

GridRange

{
  "sheetId": integer,
  "startRowIndex": integer,
  "endRowIndex": integer,
  "startColumnIndex": integer,
  "endColumnIndex": integer
}

• sheetId: 対象のシートのIDです。通常スプレッドシートのURLはhttps://docs.google.com/spreadsheets/d/xxx/edit#gid=1234 のような形をしていますが、このgidを使います。
• startRowIndex: 範囲の左上の行番号です。1始まりで指定します。ちなみに、省略するとシートの端までと解釈されます。（その他3つも同様）

※gridRangeでは、行・列番号は 0始まり の__半開区間 [start, end)__ で指定します。つまり、'A:C'（1,2,3列目を選択）のときはstartColumnIndex=0, endColumnIndex=3になります。手動で設定する場合はご注意ください。

APIリクエストの例

　以上の内容を使えば、REST API呼び出しによってオートフィルを実現できます。たとえば、https://docs.google.com/spreadsheets/d/xxx/edit#gid=1234のシート上でA2:C5にオートフィルをするときのリクエストボディは以下の形になります。

Request-Body

{
  "requests": [
    {
      "autoFill": {
        "useAlternateSeries": false,
        "range": {
          "sheetId": 1234,
          "startRowIndex": 2,
          "endRowIndex": 5,
          "startColumnIndex": 1,
          "endColumnIndex": 3
        }
      }
    }
  ],
  "includeSpreadsheetInResponse": false
}

　これをPOST https://sheets.googleapis.com/v4/spreadsheets/{spreadsheetId}:batchUpdateに送信すれば、晴れてオートフィルに成功です。（もちろん認証などの前処理は必要ですが）

gspreadとの連携

gspreadでREST API呼び出しを代替

　実は、前に書いたbatchUpdateのREST APIメソッドは、gspread.spreadsheet.Spreadsheet.batch_updateメソッドを使えばgspreadから呼び出せます。これを使えば認証・認可の手間が省けます。

sample

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# gspreadの機能で普通に認可
credentials = ServiceAccountCredentials.from_json_keyfile_name(
  '鍵ファイルのパス', ['https://www.googleapis.com/auth/drive']
)
gc = gspread.authorize(credentials)
workbook = gc.open_by_key('Spreadsheet ID')
# ここでAPI呼び出し
workbook.batch_update( # 先ほど書いたAPIのリクエストボディそのまま
  {
    'requests': [
      # ...
    ],
    # ...
  }
)

gspreadの範囲からgridRangeを求める

　range関数の戻り値をgridRangeの形式に整えるプログラムは以下の通りです。（範囲が分かっているなら本当はrange関数を通す必要もないんですが、挙動理解のためと思ってください）

sample

workbook = gc.open_by_key('Spreadsheet ID')
worksheet = workbook.worksheet('シート名') # 対象シート
range = worksheet.range('A2:C5') # 対象範囲
grid_range = {
  'sheetId': worksheet.id,
  'startRowIndex': range[0].row - 1, # range[0]は左上, gspreadは1始まりだから-1
  'endRowIndex': range[-1].row + 1, # range[-1]は右下, 0始まりの半開区間
  'startColumnIndex': range[0].col - 1, # gspreadは1始まりだから-1
  'endColumnIndex': range[-1].col # 0始まりの半開区間
}

　ちなみに、gspreadにはgspread.utils.a1_range_to_grid_rangeというズバリな関数があるので、現実的にはこれを使えばいいと思います。（少し前は使えなかった⁷という情報がありましたが、2022/01時点の最新版v5.1.1では実行できました。使えなかった方はライブラリのアップデートを試してみてください）

最終的なコード

gspreadでオートフィル

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# gspreadの機能で普通に認可
credentials = ServiceAccountCredentials.from_json_keyfile_name(
  '鍵ファイルのパス', ['https://www.googleapis.com/auth/drive']
)
gc = gspread.authorize(credentials)
workbook = gc.open_by_key('Spreadsheet ID')
worksheet = workbook.worksheet('シート名') # 対象シート
grid_range = gspread.utils.a1_range_to_grid_range('A2:C5', worksheet.id)
# ここでAPI呼び出し
workbook.batch_update(
  {
    "requests": [
      {
        "autoFill": {
          "useAlternateSeries": false,
          "range": grid_range
        }
      }
    ]
  }
)

その他のリクエスト

　今回使用したbatchUpdateでは、他にもいろいろなことが可能です。もちろん、どれでもgspread.spreadsheet.Spreadsheet.batch_updateで実行できます。
　たとえば、ファイル名、データ内容、書式などの設定や、検索と置換、CSV/HTMLのペーストなど、本当にいろいろとあります。
　これについて知りたい方は、こちらのページで例を出して説明されています。
　また、使用可能なリクエストの一覧はリファレンス³から確認できます。gspreadにない機能を使いたい場合は、一度目を通すようにするとよいかもしれません。

1. https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#autofillrequest ↩

2. https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/batchUpdate ↩ ↩²

3. https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request ↩ ↩²

4. https://qiita.com/howdy39/items/ca719537bba676dce1cf#usealternateseries ↩

5. https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/other#GridRange ↩ ↩² ↩³

6. https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#sourceanddestination ↩

7. https://qiita.com/plumfield56/items/7ae3323bc57750849ea0 ↩