通常は何らか、有り物のツールを使ったほうが絶対いいのですが、どうしても特殊要件があって既存のツールが使えないことがあったりします。カスタムツールを作るのに、すごい工数かけるのもだるいので、さっくり作りたいときにGAEとSpreadsheetsを使ってみてはどうでしょうか？

下が今回作ったものの全体図ですが、このような仕組みのツールが200行も書かないアプリケーションで実現できます。半日かけた日曜大工的プログラミングでだいたい作れました。

サンプル・アプリケーションは https://github.com/yosukesuzuki/monitoring-tool で公開していますので、よろしければ参考にしてください。とあるbasic認証のかかっているサイトの情報とspreadsheetsの内容を比較して差分があれば問題があるので、SlackやSendgrid経由のメールで通知できるようにしているものです。

ツールを作る時に面倒くさいこと

ツールを作る時に工数がかかるのが下のような項目だと思います。

CRUD

管理ツールには何らか設定値を持つことになると思います。設定値をハードコートしてもいいのですが、それはそれで不便でいわゆるCRUD(create, read, update, delete)の機能を持たせる必要が出てくるときがありますが、これを作るのが本当面倒ですよね。

Railsとかフレームワークの機能でモデルから一発で出来ますよとかあるといえばありますが、結局誰でも編集できたらこまるので、認可の設計が必要になったります。そういうところも含めるとそんなに単純じゃないです。

利用する人が限られるようなもので、業務上Gsuiteを使っているなら特にSpreadsheetsをCRUD代わりに使うのがお手軽です。ブラウザーとかSpreadsheetsのスマホアプリあれば書換え可能なのでとても便利です。編集履歴も勝手にとっておいてくれます。

管理者権限

ウェブ上の管理者ツールを管理者しか実行できないようにする権限設定がこれまた面倒くさいことが多いですが、GAEであればapp.yamlに下に追記すれば管理者しか実行できないようにするのはすぐできます。

- url: /.*
  script: main.app
  login: admin
  secure: always

Cron/定期タスク実行

まあ、これはGAEだからできるという話ではなくてAWS Lamdaとかでもいいんですが、もしSaaSとかなしで確実に定期実行させようとしたらけっこう手間かかりますよね。cron.yamlにさらっと書いておけばいいので楽ですね。

cron:
- description: run check
  url: /
  schedule: every 1 hours

gspreadを使う

さて、ようやく本題ですがSpreadsheetsを読み込むためのPythonライブラリであるgspreadを今回使います。pythonからspreadsheetsを非常に簡単にあつかえるようにするライブラリです。

使ったライブラリ全体

Flask ベースのフレームワーク
pyquery ウェブサイトのスクレピング用
gspread 今回のメインディッシュ
oauth2client Spreadsheetsに接続するのに必要
requests gspreadの依存
requests-toolbelt gspreadがrequestsに依存しているがそのままだとGAEで動かないのでモンキーパッチするためのもの
validators メールアドレスのバリデーション
sendgrid メール送信用

ユーザーを作成してSpreadsheetsに登録する

利用するGAEプロジェクトのIAM管理画面に行って、Service accountユーザーを追加します。RoleはAppEngine Viewerをあたえておけば大丈夫でした。

ユーザー作成時にJSONのcredentialが生成・ダウンロードできるので、それをGAEに読ませます。

Service accountユーザーをSpreadsheetに登録

上の項で生成したService accountユーザーを普通のgoogleアカウントユーザーと共有するかのごとく共有しておきます。

chromebit_management_-_Google_Sheets_🔊.png

ユーザーのcredentialをGAEアプリで読み込む

ダウンロードしたservice accountのcredential fileをpythonアプリケーションで読み込みます。

{
  "type": "service_account",
  "project_id": "xxxxx",
  "private_key_id": "xxxxx",
  "private_key": "-----BEGIN PRIVATE KEY-----xxxxx\n-----END PRIVATE KEY-----\n",
  "client_email": "xxxxx@xxxxx.iam.gserviceaccount.com",
  "client_id": "xxxxx",
  "auth_uri": "https://accounts.google.com/o/oauth2/auth",
  "token_uri": "https://accounts.google.com/o/oauth2/token",
  "auth_provider_x509_cert_url": "https://www.googleapis.com/oauth2/v1/certs",
  "client_x509_cert_url": "https://www.googleapis.com/robot/v1/metadata/x509/xxxxx%40xxxxx.iam.gserviceaccount.com"
}

下の通りcredentialを使ってSpreadsheetsにアクセスできるようになります。


credential_file = os.path.join(os.path.dirname(__file__), 'credential.json')
with open(credential_file, 'r') as dataFile:
        credential_dict = json.loads(dataFile.read())
credentials = ServiceAccountCredentials.from_json_keyfile_dict(credential_dict,
                                                                   scopes=['https://spreadsheets.google.com/feeds'])
gs_client = gspread.authorize(credentials)

Spreadsheetsの中身を読み込む

このあとはとても簡単です。

ドキュメントのキーを指定して読み込むだけです。配列の中に配列がある形でspreadsheetのデータが入ってますので、あとは適当にループ処理すればいいだけです。


gfile = gs_client.open_by_key(config_dict['doc_key'])
worksheet = gfile.sheet1
records = worksheet.get_all_values()

とっても簡単ですね。

微妙なところ

今回、依存関係でrequestsのモンキーパッチを当てました。正直微妙なので、時間があったらurlfetchを使うように書換えたいところです。