Python3でGoogle SpreadsheetをDBのように利用する

SpreadsheetからAPIでデータ呼び出してRDBのように使えば便利だなぁ、と思ったのでやってみました。

社内のデータのやり取りにGoogle Spreadsheetを使うこと多く、 「もうこれRDB的使えばいいよ」って話になりました。

環境

  • Python3
  • Windows 8.1

利用するライブラリ

通常、SpreadsheetのAPIをPythonから使うにはgdataが一般的ですがPython3をサポートしていません。 そこで今回はPython3をサポートしているgspreadを利用します。

またAPI認証処理にはAPI Client Library for Pythonを利用します。

なお補足注意すると、スプレッドシートのデータを直接読む部分についてはAPI Client Library for Pythonでは対応できなさそうでした。。 API Client Library for Pythonで出来るのはGoogle Driveへのファイルアップロードや削除などで個別のファイルからデータ読み出すのは先に紹介したgspreadで可能なようです。

Google側の設定を行う

Google側での必要な設定を行います。
ネットでこの手の情報を探すと以下の記事がでますが、 私がやった時と若干異なっていたので、改めて説明します。

PythonからOAuth2.0を利用してスプレッドシートにアクセスする

Google Developers Consoleにてプロジェクト作成

まずは何はともあれGoogle Developers Consoleにログイン。

GoogleDevelopersConsoleログイン
ログイン後はこんな感じ。

GoogleDevelopersConsoleで新規プロジェクト作成
次にプロジェクトを作成します。
すでに利用しているプロジェクトがあればそちらを利用しても構いません。

新規プロジェクト作成後
プロジェクト作成直後はこんな感じ。

Google DriveのAPIを有効化

次にAPIを有効化します。

APIメニュー選択
左メニューの「APIと認証」をクリックし、開いたサブメニューからAPIをクリック。

DriveのAPIを選択
遷移先の画面ではDrive APIを選択します。

APIを有効化する
次の遷移先の画面で、「APIを有効にする」ボタンとクリック。

これでGoogle DriveのAPIが有効化されました。

認証関連の設定

次に有効化したAPIへのアクセス情報を設定します。

認証情報画面へ遷移
左メニューの「認証情報」を選択。

認証情報初期画面
こんな画面が表示されると思います。

サービスアカウント作成を選択
真ん中の「認証情報を追加」をクリックして、サービスアカウントを選択してください。
OAuthではありませんのでご注意ください。

JSONを選択
すると、キータイプを選択する画面になります。
よくわからないと思いますが、ここではJSONを選択し「作成」ボタンを押します。

JSONファイルの入手
作成ボタンを押すとJSONファイルのダウンロードが始まります。
このJSONの中にPrivate_keyなどの情報が保存されています。
このJSONファイルは一旦どこかに保管しておいてください。

メールアドレスの控え
JSONをダウンロードすると、上図の画面に遷移します。
見ると、メールアドレスとフィンガープリントが発行されていますが、このうちメールアドレスはすぐ後で使うのでメモ帳に控えておいてください。

スプレッドシート側の共有設定

では、最後に読みだされる側のスプレッドシートの設定を行います。

スプレッドシートを開く
まずは読み出したいスプレッドシートを開きます。

共有ボタンをクリック
そうしたら、画面右上にある青い「共有」ボタンをクリック。

共有メールアドレスを入力
共有する相手のメールアドレスを求められるので、先ほどの控えたメールアドレスを入力します。
メールアドレスは「0000000000-xxxxxxxxxxxxxxxxxxxxx0@developer.gserviceaccount.com」のように数字が複数桁にハイフン、そして英数字が複数桁というフォーマットになっていると思います。

メールアドレスを入力したら送信ボタンを押すと共有完了です。

IDを控える
最後に、スプレッドシートのURLから上記の部分をメモ帳に控えておいてください。
これがこのスプレッドシートのIDです。

スクリプトkからデータにアクセスする

やっとコードが書けます。
まずは必要なライブラリのインストールです。

pip install gspread
pip install --upgrade google-api-python-client

そしてやっと、以下がデータ抽出のサンプルスクリプトです。

# -*- coding:utf-8 -*-
import json
import gspread
from oauth2client.client import SignedJwtAssertionCredentials



def main():
    doc_id = '[スプレッドシートのURLからコピーした文字列]'

    # 先ほどDLしたJSONをロード
    json_key    = json.load(open('[ダウンロードしたJSONへのパス]'))
    scope       = ['https://spreadsheets.google.com/feeds']

    # credentialsを取得
    credentials = SignedJwtAssertionCredentials(
                    json_key['client_email'], 
                    json_key['private_key'].encode(), 
                    scope)

    gclient = gspread.authorize(credentials)
    gfile   = gclient.open_by_key(doc_id)
    wsheet  = gfile.get_worksheet(0) # シートのindexを任意で入力
    records = wsheet.get_all_records() # head=1を指定すると便利

    for record in records:
        print(record)


if __name__ == '__main__':
    main()

上記のスクリプトでスプレッドシートのデータが抽出できていればOKです。 以上です。お疲れ様でした。

参考