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

本日もお疲れ様です。

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

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

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

準備

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

アクセス後、「プロジェクトを作成」をクリックします。
クリック後、以下のような画面になるので「プロジェクト名」を記入して
「作成」をクリックします。

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

移動後、先ほど作成したプロジェクト名が表示されているか確認します。
（されていなかったら▼ボタンで選択してください。）
確認後、ハンバーガーメニューから「APIとサービス」→「ライブラリ」をクリックします。

移動後、「Google Drive API」と「Google Sheets API」を探し出し、APIを有効化します。

クリックすると有効にするボタンがあるので、クリックしてAPIを有効化します。

次にキーを作成します。
ハンバーガーメニューから「APIとサービス」→「認証情報」をクリックします。

移動後、「認証情報を作成」→「サービスアカウント」をクリックします。
クリック後、サービスアカウントの作成画面に移動するので、「サービスアカウント名」を入力します。
（それ以外は省略しても大丈夫そうです。）

作成完了すると一覧に並ぶのでクリックします。
クリック後、「キー」→「新しい鍵を作成」をクリックします。

以下のような画面が表示されるので、JSONを選択し、作成をクリックします。

クリックすると、パソコンにJSONのキーが保存されます。
このJSONを開くと「client_email」という項目が記載されているので、
このアドレスを使用するスプレッドシートの「共有」に追加します。
（権限レベルは「編集者」にします。）

長かったですね。頑張りました

いざ実装

実装する前にスプレッドシートの構成を晒します。

今回はRPGのキャラクターのデータベースが存在するシチュエーションでいろいろやってみます。
色とか書式とか自由に設定できるのがアツいですね。

データを取得する

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']
のようにすると、最初のキャラ(ゾンビ)の名前が取れますね。

データを追加する

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になります。）
これを実行すると↓↓

gorillaが追加されました。

データを削除する

適当にゾンビさんを消してみましょう。

gsheet_test.py

    # 一部省略

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

実行後↓↓

一行書くだけで消せました。楽でいいですね。

データを変更する

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

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)

実行後↓↓

スライムくんが爆速になりました。めでたい

まとめ

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