こんな事ありませんか？
例えば「Playerの登録数を毎日確認したいからエクセルか何かに書き出してほしい」と頼まれたりとか。
私は良く有ります。
今までは、律儀に1日一回集計コマンドを実行して、集計結果をエクセルにコピペみたいな事をしていた事もありました。
そんなの面倒くさいですよね？
なので、そんな面倒な事はやめにして、自動化しちゃいましたというお話。

そこで、表計算出来た方が何かと便利ですが、エクセルだと面倒なのでgoogleスプレッドシートに集計結果を毎日自動で書き込む「集計君」を作る事にしました。

googleサービスアカウントの作成

まずはすプレッドシートをAPI操作する為にgoogleサービスアカウントを作成します。

新規プロジェクトの作成

まず、google Developers Consoleにアクセスし、新規プロジェクトを作成します。

Drive APIを有効化

プロジェクトが作成できたら、作成したプロジェクトを選択し、googleAPIsのアイコンで下記ページに遷移します。
次にDrive APIを選択します。

APIを有効化します。

サービスアカウントの作成

API Managerから認証情報ページに遷移しサービスアカウント作成を選択します。

P12ファイルを作成する

対象のEngineを選択し、認証用のP12ファイルを作成します

Google App Engine : googleDriveなどのクラウド上のサービスを使用する場合に選択
Google Compute Engine : 「Google Compute Engine 」というgoogleが提供する仮想サーバーを使用する場合に選択

今回はGoogle App Engineを使用します。

作成を押すとP12ファイルがダウンロードされて、パスワードが表示される。

詳細情報ではApp EngineにキーIDが割り振られている事が確認できます。
これでサービスアカウントの作成が完了です。

スプレッドシートの操作

次に作成したサービスアカウントからスプレッドシートを編集していきます。

サービスアカウントを共有

操作したい、スプレッドシートを開き、サービスアカウントを共有します。

共有方法は通常のユーザー登録と同じです。
先ほど作成したサービスアカウントのメールアドレスを編集者として登録します。

スクリプトの作成

下記は例ですが、実際は必要な処理を行い、指定したcellに必要な値を書き込みます。
下記モジュールをpip install してください。

oauth2client : oauth2認証を行う為のモジュール
gspread : スプレッドシートを操作する為のモジュール
- gspreadの使い方はこちらの記事が参考になります


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


# ======auth======
EMAIL = 'xxxxxxx@xxxxxxxxx.serviceaccount.com' #サービスアカウントのアドレス
SECRET_KEY_PATH = '/home/xxxxxxxxx.pem' #サービスアカウント作成時に発行したp12ファイルを変換したもの(後述)

# ======sheet======
WORK_BOOK_KEY = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'  # URLに書いてあるKEY
WORK_SHEET_NAME = 'xxxxxxxxxxxxxxxxxx' #シートの名前

# ======database======
DB_HOST = 'localhost'
DB_USER = 'root'
DB_PASS = ''
DB_NAME = ''


def get_credentials():
    client_email = EMAIL
    f = open(SECRET_KEY_PATH) 
    private_key = f.read() 
    scope = ["https://spreadsheets.google.com/feeds"]
    return SignedJwtAssertionCredentials(client_email, private_key, scope=scope)


def get_value():
    summary_datetime = datetime.datetime.now()
    conn = MySQLdb.connect(host=DB_HOST, user=DB_USER, passwd=DB_PASS, db=DB_NAME)
    cursor = conn.cursor()
    try:
        query = 'select count(id) from table where created_at < \"{datetime}\"'.format(datetime=summary_datetime)
        cursor.execute(query)
        value = cursor.fetchone()
    finally:
        cursor.close()
        conn.close()
    return value


if __name__ == "__main__":
    #認証
    credentials = get_credentials()

    row_index = 1 
    col_index = 1

    #DBから値取得
    value = get_value()

    # シートの取得
    gc = gspread.authorize(credentials)
    wb = gc.open_by_key(WORK_BOOK_KEY)
    sheet =  wb.worksheet(WORK_SHEET_NAME)
    sheet.update_cell(row_index, col_index, value)

oauth2clientのバージョンに注意

上記のスクリプトを参考にした場合oauth2client2系だとエラーになるので、1.5.2して解決。

p12ファイルの変換

p12ファイルの指定でエラーになる。
単純にファインダーで拡張子を書き換えても有効にならないので、下記コマンドでpemファイルを作成。

openssl pkcs12 -in xxxxxxxxxxxxx.p12 -nodes -nocerts > xxxxxxxxxx.pem

変換時に要求されるパスワードはP12ファイル作成時に表示されていたパスワードを指定します。

まとめ

これで、準備が整ったので、プログラムを使ってスプレッドシートに書き込む事ができます。
crontabなどで定期的に実行してあげる事で、自動で集計できるので、非常に便利です。

今回API操作を試みるまでは、スプレッドシートを外部から操作するのは結構難しそうなイメージがありましたが、やってみたら意外に簡単にできたなーって印象です。

ちなみに、今回は説明を省きましたが、自分は上記プログラムが実行された際に、hipchatにメッセージを投稿する機能も付けています。
そうする事で更新された際にチャットに連絡が来るので、分かりやすくなります。

今も毎日手動で心暖まる集計を行っている人がいたら、今回の様に是非自動化を試みては如何でしょう。^^

面倒な定期データ集計をGoogleスプレッドシートに自動で書き出して共有を楽にする