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

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



```Python: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
公式ページ内にあるサンプルを参考に記述します。

```Python: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](https://gspread-asyncio.readthedocs.io/en/latest/)
[API リクエストの制限と割り当て](https://developers.google.com/analytics/devguides/config/mgmt/v3/limits-quotas?hl=ja)
[18.5.3. タスクとコルーチン](https://docs.python.org/ja/3.6/library/asyncio-task.html#coroutine)
[Python3での非同期処理まとめ](https://qiita.com/takechanman1228/items/4cc4728abc8ffbb506fe)
[Pythonの非同期I/O（asyncio）を試す](https://qiita.com/tanuk1647/items/052c68bd06b9efbec205)
[Fluent Python――Pythonicな思考とコーディング手法　18章　asyncioによる並行処理](https://www.oreilly.co.jp/books/9784873118178/)
[Fluent Python sample code――18-asyncio-py3.7](https://github.com/fluentpython/example-code/tree/master/18-asyncio-py3.7)