LoginSignup
5
4

More than 3 years have passed since last update.

技育祭serverの人たちAdvent Calendar 2020Day 12
@y_a_m_a

スプレットシートをデータベースぽく使ってみた

Last updated at Posted at 2020-12-12

みなさん、こんにちは！
この記事は技術祭で繋がった全国の学生のコミュニティ、技育祭serverの人たち Advent Calendar 2020の12日目の記事です。
みんないい記事書くので頑張らないとですね。。今回は、クラウドサービスにデータベースデプロイするのめんどいな〜と思った時に試したTipsです。

無料で使えるデータベースの候補

サービスやチャットシステムを公開する時に、データベースって必要だと思います。でもきちんとサービスとしてデプロイしないけど、プロトタイプでチャチャっと作りたいときや、数人で使うための小規模のサービスのとき、わざわざデプロイするのめんどくさいな〜ってなってました。後、無料だと思って使っていたら諭吉一人分ぐらい使っていた経験もあるので、お金に関してはシビアになってます。。

参考Qiita(別記事)
候補としては
Heroku
AWS
Azure
GCP, Firebase
などなど。。。

今回はこれにスプレットシートという選択肢を付け加えたいと思います！！
Google Sheets APIを使って、スプレットシート上にデータを配置することでリレーショナル型データベースっぽく使えるという仕様です！ただ、スプレットシート上に値を保存しているだけですね。

Google Sheets APIを使う流れ

  1. GCPに登録する
  2. APIキーを取得 3.認証情報の作成
  3. スプレットシートの設定
  4. コードを書いて値の取得と追加をする

環境

Python 3.8
google-auth-httplib2
google-auth-oauthlib

1. GCPに登録する

GCPの登録は、他の人がたくさん書いてくれているのでリンクのみです。
12ヵ月300ドルもらえるらしいです。学生枠は日本ではないみたいです（あったら教えてください）学生が使う中ではAzureが最強なのかなと思います。

これから始めるGCP（GCE） 安全に無料枠を使い倒せ - Qiita

2. APIキーを取得

Google Cloud Platform からプロジェクトを作成して、APIを使えるようにします。
プロジェクトを作成します。プロジェクトの選択で「新しいプロジェクト」→「作成」プロジェクト名はお好きなものをどうぞ。試しに「SheetsDatabaseGeek」とかにしておきます。作成できたら作成したプロジェクトに移動します。

image.png

左のタブにある「APIライブラリ」を選択します。
image.png

image.png

APIライブラリから、「Google Sheets API」を選択し、「有効にする」をクリックします。

image.png

有効にするを押したら、APIを使えるようになりました。
次に認証情報の作成をしましょう！

3.認証情報の作成

今回はアプリケーションがAPIを利用してGoogleのデータにアクセスするので、認証が必要です。OAuth2.0を使う場合とサービスアカウントの２パターンがあるのですが、認証画面を出したくない（データベースなので）ので「サービスアカウント」で認証情報を作成します。

「Google Sheets API」の画面から「認証情報」を選択します。
image.png
「認証情報を作成」から「サービスアカウント」を選択します。
image.png

作成を押して完了です。
image.png

次に、作成したサービスアカウントから鍵の情報をダウンロードします。
「サービスアカウント」から先ほど作成したアカウントを選択し、「鍵を追加」を押します。
image.png
追加する鍵は「json」を選択します。
image.png

鍵がローカルにダウンロードされたら完了です。この鍵は、認証を行う上で重要となる鍵なので、Gitなどに公開しないように気をつけましょう。
image.png

4. スプレットシートの設定

これで認証関係ができました。では次は実際にスプレットシートを作成してAPIでアクセスできるようにしましょう！

いつも通りにスプレットシートを作成して、右上の「共有」からリンクを取得を選択します。リンクを知っている全員を編集ができるようにして共有設定は終わりです。
リンクをコピーして、SPREADSHEET_KEYを取得します。
SPREADSHEET_KEYは
spreadsheets/d/（この部分）
の部分です。
image.png

このKEYを使ってスプレットシートを選択し、アクセスします。

5. コードを書いて値の取得と追加をする

これで書き込みを行う準備はほぼ終わりました！次にコードを書いていきましょう！
ドキュメントも公開されているので、そちらも参照してください。
ドキュメント
まずGCPのライブラリが必要です。インストールしていない場合は、pipでインストールします。

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
pip install gspread

また、プログラムファイルで先ほど作成したjsonファイルの秘密鍵を読み込むので、読み込みやすい場所に配置しましょう。

コードを記載します。はじめに、スプレットシートへの書き込みです。

スプレットシートへの書き込み

sheetsAPI.py
#Googleのサービスにアクセスするためにインポート
from oauth2client.service_account import ServiceAccountCredentials
import gspread
import json

def new_post(name,comment):
    # スコープの作成
    scope = ['https://spreadsheets.google.com/feeds','https://www.googleapis.com/auth/drive']
    #　認証情報設定
    # 先ほどのjsonファイルを読み込む
    credentials = ServiceAccountCredentials.from_json_keyfile_name('***YOUR_KEY***.json', scope)
    #　サービスアカウントを使ってログインする
    gc = gspread.authorize(credentials)
    #　共有設定したスプレッドシートキーを変数[SPREADSHEET_KEY]に格納する
    SPREADSHEET_KEY = '***YOUR_KEY***'
    #　シート1で作業行う
    worksheet = gc.open_by_key(SPREADSHEET_KEY).sheet1
    worksheet.update_cell(1,1,name) # (1,1)の値をアップデートする
    worksheet.update_cell(1,2,comment) # (1,2)の値をアップデートする

 new_post("yama","Hello")

ファイルを実行し、スプレットシートにアクセスすると無事値が挿入されていることがわかります。
image.png

スプレットシートの読み込み

次に、読み込みを行います。今回っはスプレットシート全体を読み込みます。
認証部分は書き込みと同じなので省略します。

sheetsAPI2.py
def get_all():
    # 認証などはpostと同じなので17行目まで省略
    # ...
    value = worksheet.get_all_values() # 全ての値を取得する
    return value 
 print(get_all())
  • 返答結果
[['yama', 'Hello']]

2次元配列のリストとして値が入ってきていると思います。認証系を2回書くのはめんどくさいので、クラスとかを使って書くと綺麗になるかなと思います。（今回はシンプルさを考えて一回一回書きました。）

辞書型での読み込み　(2021年3月31日追記)

gspreadには辞書で読み込むことができたので追記します（詳しくは公式ドキュメント）
認証部分は書き込みと同じなので省略します。

例えば以下のようなシートがあったとします。
image.png

これを取り出すためには、、

sheetsAPI3.py
def get_all_dict():
    # 認証などはpostと同じなので17行目まで省略
    # ...
    value = worksheet.get_all_records() # 全ての値を取得する
    return value 
 print(get_all_dict())
  • 返答結果
[{'name':'yama', 'message':'Hello'}]

のように返答されます。

SQLっぽいものを投げて探索とかできるようになってくるとより使えるものになってきそうですね。今回、スプレットシートの共有の機能を使っているので、セキュリティ的にはちゃんとしたデータベースと比べてどうなのかなという感じなので、そこらへんは気をつけてください。

最後に、スプレットシートを使って作ったものを簡単に紹介して終わりたいと思います。

応用例

スプレットシート掲示板

公開しているので暇だったら是非（サーバーはヨワヨワなので読み込みが遅いです。。）
https://yama-code.azurewebsites.net/board
image.png
こんな感じです。掲示板を作って見ました。ただやっていることは、スプレットシート内にある情報を表示しているだけです笑笑
image.png

ポートフォリオのデータベースとして (追記部分)

更新とかめんどくさかったのでスプレットシートから辞書型の配列を返すものを利用して作っています

https://yama-code.web.app/
image.png
スプレットシート上では、、、
image.png

Google Formの結果表示として

単にデータベースとして使うだけではなく、Googleの機能との合わせ技でも、スプレットシートAPIは能力を発揮します。

作ったのは、Googleフォームで値を送信してもらって、スプレットシートに値を保存、ソートして結果だけ表示するというシステムです。
これを行うと、Googleフォームの結果表示にだけではできなかったことができるようになります。またGoogleフォームは新規情報の追加だけでなく、送信済みの情報を編集することもGoogleフォームにお任せすることができます！

image.png
簡単にプロトタイプを作ることができるので、サクッと実装したいときにはこれで十分です！

公開はしていないのですが、詳しくは↓
TLで見かけたアイデアすぐ実装

最後に

はじめてのアドベントカレンダーの参加でした。
コロナで部活動やサークル活動が全くできなくなってその分、別のことをやってみようかな〜と思って始めた開発や技術を使ったものづくりですが、意外とはまってしまって楽しいものですね。おそらく、それがなかったらこのコミュニティに参加して色々繋がったり、技術についてもっと知りたいとか思ってなかったので、すごいですね。
技育展で知り合った人たちがわいわいするだけのコミュニティですが、色々な人と仲良くなりたい、わいわいしたいだけなので、このアドベントカレンダーに参加している人にDMとか声をかけてもらえれば参加できるので、興味のある方はぜひ！！（1日1問AtCoderやってる人がいたり、今度Zoom忘年会するらしいですよ！）

5
4
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
5
4