この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。
他のチーム・メンバーとのインターフェースがGoogleスプレッドシートな皆様にとって、いかに楽にGoogleスプレッドシートを更新できるか、というのは頑張りどころです。
今回は Python を使って MySQL へのクエリー結果をGoogleスプレッドシートに反映する方法を紹介します。 カスタマイズすることで、SQL を投げて多種多様なレポートを定期的に作成しているようなエンジニアの生産性向上が期待されます。
環境
- Linux/Mac
- Python: 2.7
- MySQL(ドライバーはMySQL Connectorを利用)
担当システムの制約から Python2.7 を利用しました。Python3 系でも軽微な修正で動作するはずです。
事前準備
必要なライブラリを先にインストールしておきます
Googleスプレッドシート関連
$ pip install -U oauth2client google-api-python-clientMySQL 関連
次の記事を参考に MySQL Connector/Python をインストールしてください。
Google Sheets API を使い、スプレッドシートの追加、シートの更新
レポート用の Google スプレッドシートがすでに存在している前提で、新規シートを作成し、そこに、新しいレポートを追加するシナリオを考えます。
Google Sheets API を使い
- シートの追加
- シートの更新
- シートタイトルの変更
で対応します。
また、シートの更新では、クライアントで CSV データを インメモリ に作成し、その内容を反映します。
データベースに SQL を投げたり、S3 のようなオブジェクトストレージからデータを読み込んで、インメモリで CSV データを作成し、そこからスプレッドシートを作成するようなケースで有用です。
プロジェクトの作成
Google Sheets API を利用するために、Google APIキーコンソールに、アプリケーションを登録し、シークレットキーをダウンロードします。
手順は次のブログ記事の 「OAuthを使用する」を参照してください。
スクリプト
MySQL に SQL を投げて、クエリー結果をGoogleスプレッドシートに反映するスクリプトは以下です。
add_new_report_to_google_spredsheet.py
#!/usr/bin/env python
# coding:utf-8
# CSV
import cStringIO
import csv
from pprint import pprint
# MySQL
import mysql.connector
# Google Spreadsheet
import argparse
from apiclient import discovery
import oauth2client
import httplib2
def sql_to_csv():
"""MySQLへのクエリー結果をもとにCSVデータを作成
"""
# クエリー結果の出力先ファイルオブジェクト
csvfile = cStringIO.StringIO()
writer = csv.writer(csvfile, dialect='excel')
# MySQL に接続
config = {
'host': 'HOST',
'password': 'PASSWORD',
'user': 'USER_NAME',
'database': 'DATABASE'
}
cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()
# SQL を投げる
cursor.execute("""
SELECT id, name, enabled
FROM projects
ORDER BY id
LIMIT 5
""")
# ヘッダー行の出力
writer.writerow(cursor.column_names)
for row in cursor:
writer.writerow(row)
# MySQL への接続を切る
cursor.close()
cnx.close()
csv_body = csvfile.getvalue()
csvfile.close()
return csv_body
def get_sheets_service():
CLIENT_SECRET_FILE = 'client_secret.json'
CREDENTIAL_FILE = 'credential.json'
APPLICATION_NAME = 'XXX'
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)
return service
def upload_csv_to_spreadsheet(csv_body):
"""CSVデータをもとにシートを更新
"""
service = get_sheets_service()
SPREADSHEET_ID = 'XXX'
# シートの追加:AddSheetRequest
requests = {
'addSheet': {
}
}
body = {
'requests': requests
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=SPREADSHEET_ID,
body=body).execute()
# 作成されたシートIDを取得
added_sheet_id = response['replies'][0]['addSheet']['properties']['sheetId']
requests = [
# CSVデータでシートを更新:PasteDataRequest
{
'pasteData': {
'coordinate':{
'sheetId': added_sheet_id,
'rowIndex': 0,
'columnIndex': 0
},
'data':csv_body,
'type':'PASTE_VALUES',
'delimiter': ',',
}
},
# シート名の変更:UpdateSheetPropertiesRequest
{
'updateSheetProperties': {
'properties': {
'title': 'foo-bar-baz',
'sheetId' : added_sheet_id,
},
'fields': 'title'
}
}
]
body = {
'requests': requests
}
response = service.spreadsheets().batchUpdate(
spreadsheetId=SPREADSHEET_ID,
body=body).execute()
def main():
csv_body = sql_to_csv()
upload_csv_to_spreadsheet(csv_body)
if __name__ == '__main__':
main()設定の変更
28−33 行目のデータベース接続情報を適宜修正してください。
83 行目のスプレッドシートIDを適宜修正してください。 ブラウザで捜査対象のスプレッドシートで開いたときの URL からスプレッドシートIDを特定できます。
https://docs.google.com/spreadsheets/d/スプレッドシートID/edit#gid=シートID
MySQL への問い合わせでCSVデータを作成
sql_to_csv 関数を実装しています。
インメモリでの書き込みには StringIO ライブラリを利用しています。 Python3系の場合は io ライブラリで代替可能です。
ヘッダー行の出力が不要な場合は、46行目
writer.writerow(cursor.column_names)をコメントアウトしてください。
Google Sheets API 処理の流れ
Google Sheets API は batchUpdate メソッドで複数の API をまとめて投げられます。
response = service.spreadsheets().batchUpdate(
spreadsheetId=SPREADSHEET_ID,
body={'requests' :
[
{'API_NAME' : params},
{'API_NAME' : params},
...
]}).execute()今回の処理では
- シートの追加:AddSheetRequest API
- シートの更新:PasteDataRequest API
- シートタイトルの変更:UpdateSheetPropertiesRequest API
の3つの API を投げています。
後ろ2つのAPIでは、シートIDを特定する必要があるため、「シートの追加」APIをまず実行し、シートIDを取得した上で、後ろ2つのAPIをまとめて投げています。
batchUpdate で API コールするときにの API の命名規則にご注意ください。
API リファレンスでの API 名が "FooBarRequest" だった場合、"fooBar" というように
- API 名を小文字で始め
- 最後の Request を除去
する必要があります。
API 名が間違っていると、 "Invalid JSON payload received. Unknown name "update_sheet_properties" at 'requests': Cannot find field." というようなエラーが発生します。
https://stackoverflow.com/a/41133506
シートの追加
AddSheetRequest API を利用します。
{
'addSheet': {
}
}レスポンスをゴニョゴニョして、シートIDを取得します。
# 作成されたシートIDを取得
added_sheet_id = response['replies'][0]['addSheet']['properties']['sheetId']以降の API でこのシート ID を利用します。
シートの更新
PasteDataRequest API を利用します。
{
'pasteData': {
'coordinate':{
'sheetId': added_sheet_id,
'rowIndex': 0,
'columnIndex': 0
},
'data':csv_body,
'type':'PASTE_VALUES',
'delimiter': ',',
}
},sheetIdに、先程追加したシートのIDを指定しています。dataに、シートに反映するCSVデータを指定しています。typeがPASTE_VALUESのため、書式を指定せず、値だけがセルに反映されます。フォーマットの詳細は次のドキュメントを参照ください。 https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request#PasteType
シートタイトルの変更
UpdateSheetPropertiesRequest API を利用します。
{
'updateSheetProperties': {
'properties': {
'title': 'foo-bar-baz',
'sheetId' : added_sheet_id,
},
'fields': 'title'
}
}sheetIdに、先程追加したシートのIDを指定しています。titleに、変更するシート名を指定しています。
事前準備
スクリプトの実行に関して、最初の実行で、認証が必要です。
$ python add_new_report_to_google_spredsheet.py
Go to the following link in your browser:
https://accounts.google.com/o/oauth2/auth?scope=https...
Enter verification code: <ここにブラウザに表示されたコードを入力>具体的な手順は次のブログ記事の 「OAuthを使用する」と「認証を通す」を参照してください。
Google Sheets API 利用に関する処理は get_sheets_service 関数で行っています。
MySQLへのクエリ結果をGoogleスプレッドシートに反映
以上で準備が整いましたので、 スプレッドシートを更新してみましょう。
$ python add_new_report_to_google_spredsheet.pyGoogleドライブに移動すると、指定したGoogle スプレッドシートに「foo-bar-baz」という名前のシートが追加されており、シートは MySQL へのクエリ結果で反映されているはずです。
まとめ
MySQL へのクエリー結果をもとに、Googleスプレッドシートをプログラマブルに更新する方法を紹介しました。 この例をもとに、MySQL へのクエリー箇所を S3 からの CSV ファイルの取得など、カスタマイズ可能です。
lazy で impatient なプログラマーのお役に立てれば幸いです。
類似実装
参照
- https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/request
- OAuth スコープ https://developers.google.com/sheets/api/guides/authorizing
1
