Python gspread でGoogle スプレッド シートをデータベース利用する際の注意点

<!-- wp:heading {"level":1} -->
<h1>記事を書くきっかけ</h1>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p id="block-51aad076-4e66-4e72-ab0a-9cf40a477094">自身で物販システムを構築したときに解説記事が皆無だったため本記事を執筆することにしました。</p>
<!-- /wp:paragraph -->

<iframe width="560" height="315" src="https://www.youtube.com/embed/4vIj4XW7RQs" title="YouTube video player" frameborder="0" allow="accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share" allowfullscreen></iframe>

<!-- wp:embed {"url":"https://youtu.be/4vIj4XW7RQs","type":"video","providerNameSlug":"youtube","responsive":true,"className":"wp-embed-aspect-16-9 wp-has-aspect-ratio"} -->
<figure class="wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio"><div class="wp-block-embed__wrapper">
https://youtu.be/4vIj4XW7RQs
</div></figure>
<!-- /wp:embed -->

<!-- wp:heading {"level":1} -->
<h1>想定の読者像</h1>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p>・Googleスプレッドシートをデータベースとして使用したシステムをpythonで構築したい方</p>
<!-- /wp:paragraph -->

<!-- wp:heading {"level":1} -->
<h1>データベース化する際の問題点と解決方法</h1>
<!-- /wp:heading -->

<!-- wp:heading -->
<h2>gspread.exceptions.APIError</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p><strong>◎問題点</strong></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>例えば、下記のように1セルごとにスプレッドシート(本番環境)へ反映させると【gspread.exceptions.APIError】が発生します。</p>
<!-- /wp:paragraph -->

```python
for i in range(10):
	for n in range(10):
		worksheet.update_cell(i +1 , n + 1, &#39;TEST&#39;)

#上記のように実行すると下記のエラーが発生します
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'}}]}
```

<!-- wp:paragraph -->
<p>エラー原因は、【短時間高頻度でリクエストしてる】ことです。</p>
<!-- /wp:paragraph -->

<!-- wp:loos-hcb/code-block {"fileName":"データベース更新に使用してはいけない関数"} -->
<div class="hcb_wrap"><pre class="prism undefined-numbers lang-plain" data-file="データベース更新に使用してはいけない関数"><code>update_acell
update_cell</code></pre></div>
<!-- /wp:loos-hcb/code-block -->

<!-- wp:paragraph -->
<p>上記でデータベース更新していくと高い確率でエラーが発生します。</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><strong>◎解決方法</strong></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>結論から言うと</p>
<!-- /wp:paragraph -->

<!-- wp:loos-hcb/code-block {"fileName":"データベース更新に推奨する関数"} -->
<div class="hcb_wrap"><pre class="prism undefined-numbers lang-plain" data-file="データベース更新に推奨する関数"><code>update_cells</code></pre></div>
<!-- /wp:loos-hcb/code-block -->

<!-- wp:paragraph -->
<p>を使用することです！</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>こちらの関数は<strong>バッチ処理のため、1度のリクエストで1シート内の複数セルを更新可能</strong>です！</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>前出した、誤った更新方法に対して、正しい更新方法は下記の通りです。</p>
<!-- /wp:paragraph -->

<!-- wp:loos-hcb/code-block {"fileName":"正しい更新方法"} -->
<div class="hcb_wrap"><pre class="prism undefined-numbers lang-plain" data-file="正しい更新方法"><code>cell_list = worksheet.range(&#39;A1:J10&#39;)
for cell in cell_list:
	cell.value = &quot;TEST&quot;

worksheet.update_cells(cell_list)
</code></pre></div>
<!-- /wp:loos-hcb/code-block -->

<!-- wp:heading -->
<h2>update_cellsを使用した更新が癖強</h2>
<!-- /wp:heading -->

<!-- wp:paragraph -->
<p><strong>◎問題点</strong></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><strong>【行番号、列番号でセル位置を指定できない】</strong></p>
<!-- /wp:paragraph -->

<!-- wp:loos-hcb/code-block {"fileName":"update_cellsの問題点"} -->
<div class="hcb_wrap"><pre class="prism undefined-numbers lang-plain" data-file="update_cellsの問題点"><code>#update_cellの場合は行番号・列番号で指定可能

update_cell(行番号 , 列番号, 更新したい情報)

#しかし、update_cellsの場合は

①
cell_list = worksheet.range(対象セル範囲)
と更新対象のセル情報をリスト型(一次元配列)で読み込んでから

②
for cell in cell_list:
	cell.value = &quot;更新したい情報&quot;
と読み込んだローカル情報を変更し

③
worksheet.update_cells(cell_list)
で一気に本番環境へ反映する

</code></pre></div>
<!-- /wp:loos-hcb/code-block -->

<!-- wp:paragraph -->
<p>上記のようにupdate_cellsを使用する場合は【行番号、列番号】を指定してセル情報に変更を加えられず、非常に癖が強くなっております。</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><strong>◎問題の解決方法</strong></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>結論から言うと、次の性質を利用します。</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><strong>・worksheet.range(対象セル範囲)で読み込んだときのリスト順(性質)</strong></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><strong>行方向に対象開始列から対象終了列順に読込し、次の行も対象であれば列方向に次の行方向へ1つ移動し繰り返す</strong></p>
<!-- /wp:paragraph -->

![キャプチャ-1.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2646082/1814204a-a5f0-a5a4-162a-5e959c8b3bdd.jpeg)


具体例：<br>cell_list = worksheet.range('A1:J10')と読み込んだ場合のcell_list内の読込順</figcaption></figure>
<!-- /wp:image -->

<!-- wp:paragraph -->
<p></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>次に実装する方法について解説します。</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>対象列の最右行をMaxCol、リスト順をxとしたとき行番号【Row】と列番号【Col】は次のように導けます。</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><strong>Row = ⌊x / MaxCol⌋ + 1</strong></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p><strong>Col = x - ((Row - 1) * MaxCol)</strong></p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>※この方法で意味する行番号・列番号はシート内の相対位置(対象範囲内1番最初の行列を(1 , 1)としたときの番号になります。</p>
<!-- /wp:paragraph -->

**行番号・列番号とリスト順の関係**
![キャプチャ-2.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2646082/80a06bbe-fcc0-4d09-e427-943eb2263083.jpeg)



<!-- wp:paragraph -->
<p>この式を元に具体的な実装例を解説します。</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>◎具体的な実装例</p>
<!-- /wp:paragraph -->

<!-- wp:paragraph -->
<p>テーブルは下記を仮定します。</p>
<!-- /wp:paragraph -->

![キャプチャ-3.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2646082/adfa0ee6-1b34-a19e-5341-1ffa0105a59a.jpeg)

<!-- wp:paragraph -->
<p>Range("A2:C4")の範囲に次の3人分のデータを入れたいとします。</p>
<!-- /wp:paragraph -->

<!-- wp:loos-hcb/code-block {"fileName":"入力したいデータ"} -->
<div class="hcb_wrap"><pre class="prism undefined-numbers lang-plain" data-file="入力したいデータ"><code>data1 = [&quot;佐藤&quot;,&quot;080-0000-0000&quot;,&quot;男性&quot;]
data2 = [&quot;鈴木&quot;,&quot;080-0000-0001&quot;,&quot;女性&quot;]
data3 = [&quot;村上&quot;,&quot;080-0000-0002&quot;,&quot;男性&quot;]</code></pre></div>
<!-- /wp:loos-hcb/code-block -->

<!-- wp:paragraph -->
<p>すると下記のように実装すれば目的の処理が可能です。</p>
<!-- /wp:paragraph -->

```python
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)
```

![キャプチャ-4.jpg](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/2646082/0a033a1e-6ad1-8ebb-c230-50be0fda34c1.jpeg)

目的の処理ができました♪