Google SpreadSheetにコマンドラインからCSVをアペンドする

#Python

#Google Spreadsheet

2017.07.07

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

はじめに

Google SpreadSheetはWebベースの表計算ツールとしてとても便利ですね。単なる表計算ツールとしてだけではなく、データを共有したり、グラフを作成するなどにも使えます。もっと便利にしてみようと、コマンドラインからCSVファイルをアペンド(追記)していくためのスクリプトを作ってみたので紹介します。

使用環境

今回使用した動作環境は以下のとおりです。

OS: macOS 10.12
Python: 2.7
Google Sheets API v4

ライブラリは、Python向けのgoogle-api-python-clientを使用します。google-api-python-clientは、pipでインストールしておきます。

$ pip install google-api-python-client

OAuthを使用する

GoogleのAPIを使用するためには、アプリケーションキーが必要ですので、作成します。Google APIキーコンソールに、アプリケーションを登録し、キーをダウンロードするという流れです。

ブラウザから下記のURLを開きます。

https://console.developers.google.com/start/api?id=sheets.googleapis.com

するとAPIが有効化されます。適当にプロジェクト名を決めて入力します。

gss-01-make-project

進んでいくと次のようなプロジェクトへ認証情報追加画面となりますが、ここはいったんキャンセルして、先にOAuth関連情報を設定します。

gss-03-credential1

OAuth表示設定タブをクリックして、メールアドレスと、ユーザに表示するサービス名を入力します。

gss-04-oauth-settings1

認証情報を作成するプルダウンメニューが出てきますので、「OAuthクライアントID」を選択します。

gss-05-create-credential1

アプリケーションの種類として「その他」を選び、適当な名前を付けます。

gss-06-client-id1

クライアントIDとシークレットが生成され、一覧表示の状態になります。ダウンロードボタンをクリックすると、JSON形式でダウンロードできますので、client_secret.json というファイル名で保存します。

gss-07-download-client-secret1

以上で、クライアントキーが作成できました。名前を決めるところが何箇所かありますが、いずれも適当でかまいません。

シートを用意する

スプレッドシートを開き、IDを調べておきます。ブラウザのアドレスバーに表示されているURLの一部がIDです。以下では空のシートを作成しましたが、データの入っている既存のシートでも大丈夫です。

gss-21-spreadsheet-id

スクリプト

gssappendcsv.py

#!/usr/bin/env python
# coding:utf-8
from apiclient import discovery
import oauth2client
import httplib2
import argparse
import csv
import sys

SPREADSHEET_ID = 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'
RANGE_NAME = 'A1'
MAJOR_DIMENSION = 'ROWS'

CLIENT_SECRET_FILE = 'client_secret.json'
CREDENTIAL_FILE = "./credential.json"
APPLICATION_NAME = 'CSV Appender'

store = oauth2client.file.Storage(CREDENTIAL_FILE)
credentials = store.get()
if not credentials or credentials.invalid:
    SCOPES = 'https://www.googleapis.com/auth/spreadsheets'
    flow = oauth2client.client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
    flow.user_agent = APPLICATION_NAME
    args = '--auth_host_name localhost --logging_level INFO --noauth_local_webserver'
    flags = argparse.ArgumentParser(parents=[oauth2client.tools.argparser]).parse_args(args.split())
    credentials = oauth2client.tools.run_flow(flow, store, flags)

http = credentials.authorize(httplib2.Http())
discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?' 'version=v4')
service = discovery.build('sheets', 'v4', http=http, discoveryServiceUrl=discoveryUrl)
resource = service.spreadsheets().values()

parser = argparse.ArgumentParser()
parser.add_argument('infile', nargs='?', type=argparse.FileType('r'),
                    default=sys.stdin)
args = parser.parse_args(sys.argv[1:])

r = csv.reader(args.infile)
# read whole csv data
data = list(r)

body = {
    "range": RANGE_NAME,
    "majorDimension": MAJOR_DIMENSION,
    "values": data
}
resource.append(spreadsheetId=SPREADSHEET_ID, range=RANGE_NAME,
                valueInputOption='USER_ENTERED', body=body).execute()

短いPythonスクリプトですが、少し補足します。

(1) スプレッドシートはIDで指定します。簡単のため今回は直接スクリプトに書いています。 RANGE_NAMEは、挿入位置を指示しますが、すでにデータがある場合は指定した場所ではなく、空いている下の位置に入ります。 'ROW'は縦方向に追記していくことを指示しています。

(2) 鍵とトークン関係の設定をしています。client_secret.jsonはさきほどダウンロードしたファイルです。 credential.jsonは、OAuthで受け取ったトークンを格納しておくファイルで、ログインが成功すると自動的に作成されます。ファイルcredential.jsonがあればそれをトークンとして使い、無ければclient_secret.jsonを使って認証を行います。トークンが更新された場合は、credential.jsonの内容がアップデートされます。

上記のコードでは認証の際にURIをターミナルに表示、そして受け取った確認コードを入力することを想定しています。デスクトップ環境の場合には、argsを空にしておくことで認証の際に自動的にブラウザを開くことが可能です。

(3) APIにアクセスして、スプレッドシート操作用のエンドポイントを得ます。あとはresourceを操作することで、データの入出力などなんでも可能なはずです。

(4) 引数に指定されたファイル(無指定の場合は標準入力)からcsvモジュールを使って読み込みます。ほんらいcsvモジュールは行ごとに読むのですが、list()に渡してしまうことで全ての行を一度に読み込んでいます。もし大きなCSVファイルを扱うのであれば分割が必要と思われます。

(5) append操作用のボディデータを用意します。データを挿入する場所、挿入方向、そしてデータそのものを入れておきます。あとはappend関数を呼び出すことでセルにデータが追記されます

スクリプトには実行権限を付けておきます。

$ chmod +x gssappendcsv.py

また、先ほどダウンロードしたclient_secret.jsonは同じディレクトリに置いてください。

認証を通す

最初の実行で、認証が必要です。

$ ./gssappendcsv.py
/Users/takahashi.tomohiro/jobs-stat/VENV/lib/python2.7/site-packages/oauth2client/_helpers.py:255: UserWarning: Cannot access ./credential.json: No such file or directory
  warnings.warn(_MISSING_FILE_MESSAGE.format(filename))

Go to the following link in your browser:

    https://accounts.google.com/o/oauth2/auth?scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fspreadsheets&redirect_uri=urn%3Aietf%3Awg%3Aoauth%3A2.0%3Aoob&response_type=code&client_id=76363942614-68hasht87b2h92v8il3558ara0dsce0n.apps.googleusercontent.com&access_type=offline

Enter verification code:

表示されたURLをブラウザで開きます。もしGoogleアカウントにログイン済みであれば、アカウント選択画面が現れます。

gss-11-login1

許諾する権限(Authority)を確認します。

gss-12-authority1

OKをクリックすると、確認コードが表示されます。

gss-13-token1

確認コードをコピーして、ターミナルからVerification codeとして入力(ペースト)します。成功すると、Authentication successfulとなります。認証は最初の一度だけ行えばOKです。

Enter verification code: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Authentication successful.

CSVの入力待ちになるので、一度Ctrl-Cで止めておきます。

CSVをシートに送る

以上で準備が整いましたので、 CSVデータを送ってみましょう。適当なCSVファイルがあればそれを使ってもかまいません。

$ echo 1,2,3 >foo.csv
$ ./gssappendcsv.py foo.csv

そうすると、Google SpreadSheet側でデータが追加されていることが確認できると思います。ブラウザで開いておくと、自動的にスプレッドシートの画面が更新されます。ターミナルから操作した直後に小気味良く反映するので、なかなか気持ち良いです。

標準入力から読み込ませることも可能です。

$ echo 4,5,6 |./gssappendcsv.py
$ echo 7,8,9 |./gssappendcsv.py

何度か繰り返すと、次々と行が追加されていきます。

gss-22-sheet-result

以上の実行例では、一行づつ追加していますが、まとめて複数行追加ももちろんOKです。

まとめ

簡単なスクリプトを用意することで、CSVデータをGoogle SpreadSheetに送ることができました。追記は繰り返し行うことができますので、スプレッドシートがデータを蓄積する場所となります。ちょっとしたスクリプトに組み込んで自動化することも容易ですので、時系列データやサーバで観測したメトリクスなどを、手っ取り早く共有やグラフ化、もしくは分析するための方法として、なかなか便利なのではないかと思っています。スプレッドシートであれば非エンジニアにも扱いやすいメリットもあります。

上記で示したスクリプトでは、種々のパラメータをハードコード固定にしてしまいましたが、用途に合わせてスクリプトを鍛えることは容易でしょう(シートIDを指定できるようにしたり、スプレッドシートを新しく生成する機能を付ける等)。これは便利かも！と思ったら、ぜひ試してみてください。