【Python】スプレッドシートをgspreadでDBっぽく使う世界線

本日もお疲れ様です。

皆さんはデータベースは好きですか？
僕は結構好きです。
なんかプログラマーっぽくて。（語彙力どうした）

でもいざ使おうとするとお金がかかったり、扱いに慣れるまでちょっと時間がかかったり…

そこで今回は
**「安い（無料）」「早い（扱いやすい）」「美味い（？）」**
でおなじみのスプレッドシートを巷でよく見るdbっぽく扱ってみたいと思います。

## 準備

まずはPythonからスプレッドシートにアクセスするために、権限周りの準備をします。
早速以下にアクセスしましょう。

https://console.cloud.google.com/cloud-resource-manager

アクセス後、「プロジェクトを作成」をクリックします。
クリック後、以下のような画面になるので「プロジェクト名」を記入して
「作成」をクリックします。
![step_2.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/69017b16-f2b0-b130-90b9-91a3812b403b.png)

作成後、作成したプロジェクト画面に移動したいので「Google Cloud Platform」をクリックして
ホーム画面っぽいとこに移動します。

移動後、先ほど作成したプロジェクト名が表示されているか確認します。
（されていなかったら▼ボタンで選択してください。）
確認後、ハンバーガーメニューから「APIとサービス」→「ライブラリ」をクリックします。
![step_5.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/89b19ef1-f6f5-9f85-2b84-421d6f88a41d.png)

移動後、「Google Drive API」と「Google Sheets API」を探し出し、APIを有効化します。
![step_6.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/2440d55d-9c54-595b-cb16-0a8eb9c16a87.png)
クリックすると有効にするボタンがあるので、クリックしてAPIを有効化します。

次にキーを作成します。
ハンバーガーメニューから「APIとサービス」→「認証情報」をクリックします。
![step_4.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/09acc981-d980-b39b-f906-255293869ebb.png)

移動後、「認証情報を作成」→「サービスアカウント」をクリックします。
クリック後、サービスアカウントの作成画面に移動するので、「サービスアカウント名」を入力します。
（それ以外は省略しても大丈夫そうです。）
![step_8.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/83b6a02b-f091-e3c9-7913-9011296afe8a.png)
作成完了すると一覧に並ぶのでクリックします。
クリック後、「キー」→「新しい鍵を作成」をクリックします。
![step_9.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/896302d5-affb-cca2-51c0-14bc7f8a5e26.png)
以下のような画面が表示されるので、JSONを選択し、作成をクリックします。
![step_10.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/44447943-bbbb-983c-19c6-f24c88f65452.png)

クリックすると、パソコンにJSONのキーが保存されます。
このJSONを開くと**「client_email」**という項目が記載されているので、
このアドレスを使用するスプレッドシートの「共有」に追加します。
（権限レベルは「編集者」にします。）
![step_14.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/ca128c19-5ca1-7244-46bb-3b39ad18a5e9.png)
長かったですね。頑張りました:clap:

## いざ実装

実装する前にスプレッドシートの構成を晒します。
![step_11.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/94ac8e52-83ed-32dd-9881-01fee3865df7.png)
今回はRPGのキャラクターのデータベースが存在するシチュエーションでいろいろやってみます。
色とか書式とか自由に設定できるのがアツいですね。

***

#### データを取得する
```python:gsheet_test.py
import gspread
import os
from oauth2client.service_account import ServiceAccountCredentials 

if __name__ == "__main__":

    # 認証処理
    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
    credentials = ServiceAccountCredentials.from_json_keyfile_name(os.path.join(os.getcwd(), "key.json"), scope)
    authorize = gspread.authorize(credentials)

    # 対象のシートにアクセスする
    target_data = authorize.open("db_character")
    sheet = target_data.worksheet("status")

    # シートの全情報を辞書型で取得
    character_datas = sheet.get_all_records()

    for data in character_datas:
        print("------------------------")
        for key, value in data.items():
            print(key, value)
```

ServiceAccountCredentials.from_json_keyfile_name の第一引数では、
準備段階で作成したJSONのキーを渡しています。
では、これを実行してみましょう↓↓

```
------------------------
name ゾンビ
lv 5
hp 10
atk 5
def 3
agi 1
luk 0
------------------------
name スケルトン
lv 7
hp 8
atk 7
def 4
agi 3
luk 5
～以下省略～
```

もうこの時点でキャラクターごとのステータスが取れてしまうのです。優秀。優勝。大喝采。
各キャラクターのデータが、辞書型で配列に格納されているようなイメージです。
**character_datas[0]['name']**
のようにすると、最初のキャラ(ゾンビ)の名前が取れますね。

***

#### データを追加する
```python:gsheet_test.py
    # 一部省略

    # シートの全情報を辞書型で取得
    character_datas = sheet.get_all_records()

    # ステータスの種類数を取得
    status_names = sheet.get_all_values()[0]

    # 追加したい内容
    add_data = {'name': 'gorilla', 'lv': 120, 'hp': 25000, 'atk': 50000, 'def': 30000, 'agi': 1, 'luk': 100}
    
    # 既存のキャラデータに追加
    character_datas.append(add_data)

    # 既存キャラ+追加するキャラをこの範囲のセルに記載する
    range = sheet.range(2, 1, len(character_datas)+1, len(status_names))

    # データを書いていく
    index = 0
    for data in character_datas:
        for key, value in data.items():
            range[index].value = value
            index += 1
    
    # セルを更新して書き込んだ内容を反映する
    sheet.update_cells(range)
```

スプレッドシートの範囲取得の値に注意しましょう。
（配列の先頭は0ですが、シートの始まりは1になります。）
これを実行すると↓↓
![step_12.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/0a73e7db-72e2-7990-4a2b-c045ce62a179.png)
gorillaが追加されました。

***
#### データを削除する
適当にゾンビさんを消してみましょう。

```python:gsheet_test.py
    # 一部省略

    # ゾンビを消してみる
    sheet.delete_row(2)
```

実行後↓↓
![step_13.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/f20bf7ba-e1f6-c350-2fea-897565877c90.png)
一行書くだけで消せました。楽でいいですね。

***
#### データを変更する

スライムくんの速さ（agi）があまりにも遅いので早くしてみましょう。
データの追加とほぼソースは同じです。

```python:gsheet_test.py
    # 一部省略

    # シートの全情報を辞書型で取得
    character_datas = sheet.get_all_records()

    # ステータスの種類数を取得
    status_names = sheet.get_all_values()[0]

    # スライムの速さを変更
    character_datas[2]['agi'] = 7000

    # 既存キャラ+追加するキャラをこの範囲のセルに記載する
    range = sheet.range(2, 1, len(character_datas)+1, len(status_names))

    # データを書いていく
    index = 0
    for data in character_datas:
        for key, value in data.items():
            range[index].value = value
            index += 1
    
    # セルを更新して書き込んだ内容を反映する
    sheet.update_cells(range)
```

実行後↓↓
![step_15.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/1780415/e1ab0bba-403f-7d03-24ff-33b9ddb9fccc.png)
スライムくんが爆速になりました。めでたい:runner_tone2:

## まとめ
準備に少し時間がかかりますが、割と扱いやすい印象でした。
スプレッドシートとPython（gspread）の扱いに慣れている方は是非一度お試しください。