LoginSignup
2
5

More than 3 years have passed since last update.

@satsukiya(Kazuo Sato)

Pythonで多めのデータをGoogleスプレッドシートに書く時の注意点

Last updated at Posted at 2019-07-16

Background

結構多めのデータをスプレッドシートに書き込む場合、下記のコードを書くとエラーが出てしまう。 データ元は埼玉県の郵便番号リスト(https://www.post.japanpost.jp/zipcode/dl/kogaki/zip/11saitam.zip) です。

serial
import configparser
import json
import os
import time
from oauth2client.service_account import ServiceAccountCredentials
import gspread

def serial():
    config = configparser.ConfigParser()
    ini_file = os.path.join("./", 'setting.ini')
    config.read(ini_file)

    scope = ["https://spreadsheets.google.com/feeds",
            "https://www.googleapis.com/auth/drive"]
    book_id = config.get("googleSpreadSheet", "book_id")
    path = os.path.join("./", config.get("googleSpreadSheet", "keyfile_name"))
    credentials = ServiceAccountCredentials.from_json_keyfile_name(path, scope)        
    client = gspread.authorize(credentials)
    gfile = client.open_by_key(book_id)

    sheet = gfile.worksheets()[0]

    adress = []
    with open("11SAITAM.csv", "r") as fin:
        for item in fin.readlines():
            one_row = item.split(",")
            if one_row[8] != "以下に掲載がない場合":
                adress.append(one_row[0:2] + one_row[6:9])

    start_time = time.time()
    for i,a in enumerate(adress[:50]):
        sheet.update_acell('A{0}'.format(i+1), a[0])
        sheet.update_acell('B{0}'.format(i+1), a[1])
        sheet.update_acell('C{0}'.format(i+1), a[2])
        sheet.update_acell('D{0}'.format(i+1), a[3])
        sheet.update_acell('E{0}'.format(i+1), a[4])
        time.sleep(1)

    elapsed_time = time.time() - start_time
    print ("elapsed_time:{0}".format(elapsed_time) + "[sec]")

if __name__ == "__main__":
    serial()
error_message
gspread.exceptions.APIError: {
  "error": {
    "code": 429,
    "message": "Quota exceeded for quota group 'WriteGroup' and limit 'USER-100s' of service 'sheets.googleapis.com' for consumer 'project_number:XXXXXX'.",
    "status": "RESOURCE_EXHAUSTED",
    "details": [
      {
        "@type": "type.googleapis.com/google.rpc.Help",
        "links": [
          {
            "description": "Google developer console API key",
            "url": "https://console.developers.google.com/project/11736174289/apiui/credential"
          }
        ]
      }
    ]
  }
}

原因はユーザーごとの 100 秒あたりのリクエスト数の上限を達してしまった可能性があって、短期間でsheet.update_acell() を送り続けるとエラーになるとのことです。

それで、シンプルに import time time.sleep(1) と遅延を掛けてもエラーにならないこともなく不安定です。

Solution

非同期I/O asyncio を内包している gspread-asyncio を使います。

Install

pip3 install gspread-asyncio

Development

公式ページ内にあるサンプルを参考に記述します。

gspread_asyncio
import asyncio
import gspread_asyncio
import configparser
import json
import os
import time
from oauth2client.service_account import ServiceAccountCredentials
import gspread

async def simple_gspread_asyncio(agcm):
    agc = await agcm.authorize()
    book = await agc.open_by_url("https://docs.google.com/spreadsheets/d/{sheet_id}/")
    sheet = await book.get_worksheet(0) 

    adress = []
    with open("11SAITAM.csv", "r") as fin:
        for item in fin.readlines():
            one_row = item.split(",")
            if one_row[8] != "以下に掲載がない場合":
                adress.append(one_row[0:2] + one_row[6:9])

    start_time = time.time()

    #50件のみ書き込み
    for i,a in enumerate(adress[:50]):
        await sheet.update_acell('A{0}'.format(i+1), a[0])
        await sheet.update_acell('B{0}'.format(i+1), a[1])
        await sheet.update_acell('C{0}'.format(i+1), a[2])
        await sheet.update_acell('D{0}'.format(i+1), a[3])
        await sheet.update_acell('E{0}'.format(i+1), a[4])

    elapsed_time = time.time() - start_time
    print ("elapsed_time:{0}".format(elapsed_time) + "[sec]")

def get_creds():
    return ServiceAccountCredentials.from_json_keyfile_name(
        "{authority_json_file_name}.json",
        ['https://spreadsheets.google.com/feeds', 
        'https://www.googleapis.com/auth/drive',
        'https://www.googleapis.com/auth/spreadsheets'])

if __name__ == "__main__":
    #設定クラス作成
    agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
    #非同期処理開始
    loop = asyncio.get_event_loop()
    #終了するまで実行
    loop.run_until_complete(simple_gspread_asyncio(agcm))
    #終了処理
    loop.close()

Result

処理結果は elapsed_time:275.9753634929657[sec] と4,5分かかります。ですが、ERROR:root:Error while calling update_cell (44, 5, '見沼') {}. といったエラーメッセージが出るものの途中終了せずに書き込みは完了します。

Suplement

AsyncioGspreadClientManagerクラスにAsyncioGspreadClientManager(get_creds,gspread_delay=0.5)と遅延時間をチューニングすることができます。 defalutは1.1秒です。

一度0.4秒で設定してtryしたところ、214.48740601539612[sec]と1分くらい短縮は出来ますが大幅には改善されません。ERROR:root:Error while calling update_cell のエラーが頻発するのでAPIリクエストの上限に引っかかるみたいです。

また、python3.7ではコルーチンを起動と終了を自動で実行するシンプルなコード asyncio.run(coroutine_func) が追加されましたが、 asyncio.run(simple_gspread_asyncio(agcm)) とすると下記のエラーが表示されます。 なぜかは不明です:sweat_smile:。

Traceback (most recent call last):
  File "main.py", line 154, in <module>
    asyncio.run(simple_gspread_asyncio(agcm))
  File "/usr/local/lib/python3.7/asyncio/runners.py", line 43, in run
    return loop.run_until_complete(main)
  File "/usr/local/lib/python3.7/asyncio/base_events.py", line 579, in run_until_complete
    return future.result()
  File "main.py", line 117, in simple_gspread_asyncio
    agc = await agcm.authorize()
  File "/usr/local/lib/python3.7/site-packages/gspread_asyncio/__init__.py", line 164, in authorize
    return await self._authorize()
  File "/usr/local/lib/python3.7/site-packages/gspread_asyncio/__init__.py", line 171, in _authorize
    creds = await self._loop.run_in_executor(None, self.credentials_fn)
RuntimeError: Task <Task pending coro=<simple_gspread_asyncio() running at main.py:117> cb=[_run_until_complete_cb() at /usr/local/lib/python3.7/asyncio/base_events.py:153]> got Future <Future pending cb=[_chain_future.<locals>._call_check_cancel() at /usr/local/lib/python3.7/asyncio/futures.py:348]> attached to a different loop

Future

途中終了せずに多めのデータをスプレッドーシートに書き込む方法をまとめてみたのですが、もっと多めのデータでは完了するまでに時間がかかります。埼玉県内の郵便番号データは約3000件あるので、上記のコードでは全データを書き込むのに3時間くらいかかります。

concurrent.futures.ThreadPoolExecutor でも使ってworkerを振り分けて内部で並列処理を掛けないと速度を出すのは厳しいです。速度を出すとリクエストの制限に引っかかりそうだ。調節が必要です:expressionless:。

Reference

gspread_asyncio
API リクエストの制限と割り当て
18.5.3. タスクとコルーチン
Python3での非同期処理まとめ
Pythonの非同期I/O（asyncio）を試す
Fluent Python――Pythonicな思考とコーディング手法　18章　asyncioによる並行処理
Fluent Python sample code――18-asyncio-py3.7

2
5
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
5