記事を書くきっかけ
自身で物販システムを構築したときに解説記事が皆無だったため本記事を執筆することにしました。
想定の読者像
・Googleスプレッドシートをデータベースとして使用したシステムをpythonで構築したい方
データベース化する際の問題点と解決方法
gspread.exceptions.APIError
◎問題点
例えば、下記のように1セルごとにスプレッドシート(本番環境)へ反映させると【gspread.exceptions.APIError】が発生します。
for i in range(10):
for n in range(10):
worksheet.update_cell(i +1 , n + 1, 'TEST')
#上記のように実行すると下記のエラーが発生します
gspread.exceptions.APIError:
{'code': 429,
'message': "Quota exceeded for quota metric
'Write requests' and limit 'Write requests per minute per user'
of service 'sheets.googleapis.com' for consumer
'project_number:549701863385'.",
'status': 'RESOURCE_EXHAUSTED',
'details': [{'@type': 'type.googleapis.com/google.rpc.ErrorInfo',
'reason': 'RATE_LIMIT_EXCEEDED',
'domain': 'googleapis.com',
'metadata': {'service': 'sheets.googleapis.com',
'consumer': 'projects/549701863385',
'quota_location': 'global',
'quota_limit_value': '60',
'quota_metric': 'sheets.googleapis.com/write_requests',
'quota_limit': 'WriteRequestsPerMinutePerUser'}}]}
エラー原因は、【短時間高頻度でリクエストしてる】ことです。
update_acell
update_cell上記でデータベース更新していくと高い確率でエラーが発生します。
◎解決方法
結論から言うと
update_cellsを使用することです！
こちらの関数はバッチ処理のため、1度のリクエストで1シート内の複数セルを更新可能です！
前出した、誤った更新方法に対して、正しい更新方法は下記の通りです。
cell_list = worksheet.range('A1:J10')
for cell in cell_list:
cell.value = "TEST"
worksheet.update_cells(cell_list)
update_cellsを使用した更新が癖強
◎問題点
【行番号、列番号でセル位置を指定できない】
#update_cellの場合は行番号・列番号で指定可能
update_cell(行番号 , 列番号, 更新したい情報)
#しかし、update_cellsの場合は
①
cell_list = worksheet.range(対象セル範囲)
と更新対象のセル情報をリスト型(一次元配列)で読み込んでから
②
for cell in cell_list:
cell.value = "更新したい情報"
と読み込んだローカル情報を変更し
③
worksheet.update_cells(cell_list)
で一気に本番環境へ反映する
上記のようにupdate_cellsを使用する場合は【行番号、列番号】を指定してセル情報に変更を加えられず、非常に癖が強くなっております。
◎問題の解決方法
結論から言うと、次の性質を利用します。
・worksheet.range(対象セル範囲)で読み込んだときのリスト順(性質)
行方向に対象開始列から対象終了列順に読込し、次の行も対象であれば列方向に次の行方向へ1つ移動し繰り返す
具体例：
cell_list = worksheet.range('A1:J10')と読み込んだ場合のcell_list内の読込順
次に実装する方法について解説します。
対象列の最右行をMaxCol、リスト順をxとしたとき行番号【Row】と列番号【Col】は次のように導けます。
Row = ⌊x / MaxCol⌋ + 1
Col = x - ((Row - 1) * MaxCol)
※この方法で意味する行番号・列番号はシート内の相対位置(対象範囲内1番最初の行列を(1 , 1)としたときの番号になります。
行番号・列番号とリスト順の関係
この式を元に具体的な実装例を解説します。
◎具体的な実装例
テーブルは下記を仮定します。
Range("A2:C4")の範囲に次の3人分のデータを入れたいとします。
data1 = ["佐藤","080-0000-0000","男性"]
data2 = ["鈴木","080-0000-0001","女性"]
data3 = ["村上","080-0000-0002","男性"]すると下記のように実装すれば目的の処理が可能です。
data1 = ["佐藤","080-0000-0000","男性"]
data2 = ["鈴木","080-0000-0001","女性"]
data3 = ["村上","080-0000-0002","男性"]
dataes = [data1,data2,data3]
cell_list = worksheet.range('A2:C4')
for i,dataes2 in enumerate(dataes):
for n,data in enumerate(dataes2):
updateNumber = i*3 + n
cell_list[updateNumber].value = data
worksheet.update_cells(cell_list)
目的の処理ができました♪