gspreadのリクエスト回数を削減したい #Python

PythonからGoogle Spread Sheetsを操作するライブラリとして提供されているgspread。しかしSheets APIには時間当たりのリクエスト回数制限があり、for文の中で繰り返し呼び出したりすると一瞬でエラーになります。
本稿ではどこでリクエストが発生するのかまとめます。

筆者環境

Python 3.7.1
gspread 3.1.0

リクエスト回数制限

デフォルトで100秒に100回。ここから確認できると思います。記事執筆時点で総利用数上限などはありません。

gspreadでリクエストが発生する操作

シートを開く

この部分のコードはこちらを参考にさせていただいております。

Python3

'''
あるスプレッドシートの１番目のシートを開くコード
'''
_SCOPE = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
credentials = ServiceAccountCredentials.from_json_keyfile_name(_JSON_DIRECTORY, _SCOPE)
# _JSON_DIRECTORYは認証キーの.jsonファイルの(相対)パス

gc = gspread.authorize(credentials)  # ここで１回リクエスト？
ws = gc.open_by_key(_FILE).sheet1  # ここで１回リクエスト
# _FILEは開くシートのリンクのうち https://docs.google.com/spreadsheets/d/【ここの文字列】//edit#gid=0

gcやwsオブジェクトは使いまわすようにしましょう。

シートの中身を取得する

以下の全ての操作で、リクエストがそれぞれ一回ずつ発生します。

# 特定のセルの値を取得する(２パターン)
val = ws.acell('B1').value
val = ws.cell(1, 2).value

# 特定の行に含まれるすべての値の配列を取得する。列も同様
lst = ws.row_values(2)

# ワークシートからすべての値をリストのリストとして取得する
lst2d = ws.get_all_values()

# シートの行数
row_count = ws.row_count

# 特定の座標のセルオブジェクトを取得する
cell = ws.acell('A1')

# 特定の範囲のセルオブジェクトの(一次元)配列を取得する
cells = ws.range('B2:D3')

※ちなみに「特定の列に含まれるすべてのセルオブジェクトの配列を取得する」操作を直接実現することはできない様子。以下のようにすればよい。

row_count = ws.row_count
cells=ws.range('B1:B' + str(row_count))

シートを操作する

以下の全ての操作で、リクエストがそれぞれ一回ずつ発生します。

# 特定のセルの値を更新する(２パターン)
ws.update_acell('B1', 'Hello')
ws.update_cell(1, 2, 'Hello')

# セルオブジェクトの一次元配列を引数として、各セルの値をまとめて更新する
ws.update_cells(cell_list)

リクエストが発生しない操作

一旦取得したCellオブジェクトやその配列をローカルで操作するとき、リクエストは発生しません。

# 条件分岐・値書き換え
if cells[i].value == "TRUE":
    cells[i].value = True
    # Cellオブジェクトを取得した時点ではvalueはすべてstr型だが、後から違う型の値を代入することが可能

# 特定のCellオブジェクトを別の配列(cells_new)に挿入
cells_new.append(cells[i])

ここで値の更新をした場合、そのままではスプレッドシートには反映されないので、最後にws.update_cells(cell_list)する必要があります。

リクエスト回数を定数回に抑える方法

今回は「A列にあるセルの値が特定の値だったら、隣のセルの値をTrueにする」というコードを書いてみます。在庫管理かなにかで使えそうですね。~~最初からCloud SQLを使うべきかもしれませんが~~

main.py

val = "hoge" #判定したい文字
# ワークシートを開くところまで省略
row_count = ws.row_count  # リクエスト発生
cells_a = ws.range('A1:A' + str(row_count))  # リクエスト発生
cells_b = ws.range('B2:B' + str(row_count))  # リクエスト発生
cells_update = []

for i in range(row_count):
    if not cells_a[i].value:  # i行A列のセルが空だった場合は飛ばす
        continue
    if cells_a[i].value == val:
        cells_update.append(cells_b[i])
        cells_update[-1].value = True  # いま追加したcellオブジェクトの値をTrueにする

ws.update_cells(cells_update)  # リクエスト発生

cells_a[i].value == valのたびにws.cell(i, 2).value = Trueとすると、値がvalに等しいセルの数に比例してリクエスト回数が多くなってしまいます。ローカルで値を変更した後まとめて変更したいセルだけupdate_cellsすることで、リクエストの数を定数回に抑えることができました。

おまけ

範囲指定でスプレッドシートから得たCellオブジェクトは、複数列に渡るものであっても一次元配列に入っています。配列の順序は以下の通りです。

行\列	A	B
1	cells[0]	cells[1]
2	cells[2]	cells[3]
3	cells[4]	cells[5]

以下のコードで二次元配列に整形して使いやすくできます。

def cellsto2darray(cells, col):  # colは列の数
    cells2d = []
    for i in range(len(cells) // col):
        cells2d.append(cells[i * col:(i + 1) * col])
    return cells2d

ただしupdate_cellsするときには一次元配列に戻さないといけません。戻すときは次のようにします。

def cellsto1darray(cells2d):
    cells1d = []
    for cells in cells2d:
        cells1d.extend(cells)
    return cells1d