PythonのHerokuアプリケーションからGoogle Spread Sheetの読み書き

「Pythonのアプリケーションで、特定のGoogle Spread Sheetに対して読み書きをしたい！！！」と思っていろいろ調べてみると、以下のような記事にて紹介されている方法で、簡単にGoogle Spread Sheetの読み書きをすることはできた。

temcee.hatenablog.com

inokara.hateblo.jp

LocalでPythonプログラムを書いて動作させることはできたので、private keyなどが記載されたjsonファイルをHerokuにデプロイすることなく、HerokuのアプリケーションからGoogle Spread Sheetにアクセスして読み書きする方法を試すことに。ググっても方法が出てこなかったので、いろいろ試行錯誤することに。

jsonファイルをそのままHerokuに置こうとすると、gitで管理しなくてはならなくなり、Github上にprivate keyが載ったファイルをプッシュしてしまう危険もある。かといって、jsonファイルをどこかの任意の場所から読み込まないと認証ができずGoogle Spread Sheetにアクセスできない...。いろいろ考えた挙句、以下のような手順で OAuth認証とGoogle Spread Sheetへの読み書きを実現しました。

① 環境変数にprivate key やclient idなどを設定する。また、秘匿性の高いデータ以外を記述したtemplate.txtファイルを用意する。
② 環境変数からprivate keyやclient idを読み取って、template.txtファイルのテキストと統合して任意のjsonファイルに書き込み、そのjsonファイルのデータを使って認証し、Google Spread Sheetにアクセスして読み書きする。
③ Google Spread Sheetのアクセスが不要になったらjsonファイルを削除する。これで秘匿性の高いデータは環境変数に置いておき、Google Spread Sheetへの読み書きを行うことができました。

以下、コード載せておきます。

普通にファイルからreadしてwriteする → うまくいかず。

最初は、以下のように普通にtemplate.txtファイルから文字列を読み込んで、credential.jsonファイルに書き込むようにしてみました。

環境変数を定義した「.env」ファイル

.env

SHEET_GID=xxxxx
SHEET_PROJECT_ID=xxxxx
SHEET_PRIVATE_KEY_ID=xxxxx
SHEET_PRIVATE_KEY=xxxxx
SHEET_CLIENT_EMAIL=xxxxx
SHEET_CLIENT_ID=xxxxx
SHEET_CLIENT_X509_CERT_URL=xxxxx

スプレッドシートへの認証・読み込み書き込み処理を書いた「google_spread_sheet_test.py」ファイル

google_spread_sheet_test.py

# coding=utf-8
import gspread
from oauth2client.service_account import ServiceAccountCredentials
import os

SCOPE_URL = 'https://spreadsheets.google.com/feeds'
SHEET_GID = os.environ['SHEET_GID']
SHEET_PROJECT_ID = os.environ['SHEET_PROJECT_ID']
SHEET_PRIVATE_KEY_ID = os.environ['SHEET_PRIVATE_KEY_ID']
SHEET_PRIVATE_KEY = os.environ['SHEET_PRIVATE_KEY']
SHEET_CLIENT_EMAIL = os.environ['SHEET_CLIENT_EMAIL']
SHEET_CLIENT_ID = os.environ['SHEET_CLIENT_ID']
SHEET_CLIENT_X509_CERT_URL = os.environ['SHEET_CLIENT_X509_CERT_URL']

# 書き込み用のcredential.jsonファイル作成
credential_file_name = 'credential.json'
credential_file = open(credential_file_name, 'w')

# template.txtと環境変数から取得した各種データを統合してcredential.jsonに書き込む
template_file_name = 'template.txt'
template_file = open(template_file_name)
template_texts = template_file.read()
formatted_text = template_texts.format(SHEET_PROJECT_ID, SHEET_PRIVATE_KEY_ID, SHEET_PRIVATE_KEY,
                                       SHEET_CLIENT_EMAIL, SHEET_CLIENT_ID, SHEET_CLIENT_X509_CERT_URL)
credential_file.write(formatted_text)

# credential.jsonファイルの情報を使ってGoogle Spread Sheetに認証する
credentials = ServiceAccountCredentials.from_json_keyfile_name(credential_file_name, SCOPE_URL)
client = gspread.authorize(credentials)
worksheet = client.open_by_key(SHEET_GID).sheet1
# Google Spread Sheetからデータ取得
print(worksheet.cell(1, 1))
# Google Spread Sheetにデータ書き込み
worksheet.update_cell(3, 3, 'ファイル書き込みいけるか！')

template_file.close()
credential_file.close()

スプレッドシートへの認証をするために必要なjsonファイルを作成するためのテンプレートファイル

template.txt

{
  "type": "service_account",
  "project_id": "{}",
  "private_key_id": "{}",
  "private_key": "-----BEGIN PRIVATE KEY-----{}-----END PRIVATE KEY-----\n",
  "client_email": "{}",
  "client_id": "{}",
  "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": "{}"
}

これで普通にファイルの書き込みができて、Google Spread Sheetへのアクセスして読み書きできるかなと思い、実行すると以下のようなエラー。

$ forego run python google_spread_sheet_test.py

KeyError: '\n  "type"'

もちろん、Google Spread Sheetに書き込みもできていません。

f:id:virtual-surfer:20180619215936p:plain

一気に読み込むread()メソッドでは、行の改行箇所の読み込みでおかしくなるのかな？とにかくこの方法ではうまくいかず断念。

readLinesで一行ずつ読み込んでwriteLineで一行ずつ書き込み → うまくいった！

いろいろ悩んで試行錯誤した結果、一行ずつ読み込んで一行ずつ書き込むとうまくいきました。（google_spread_sheet_test.pyファイルだけ以下のように編集）

変更前

google_spread_sheet_test.py

# template.txtと環境変数から取得した各種データを統合してcredential.jsonに書き込む
template_file_name = 'template.txt'
template_file = open(template_file_name)
template_texts = template_file.read()
formatted_text = template_texts.format(SHEET_PROJECT_ID, SHEET_PRIVATE_KEY_ID, SHEET_PRIVATE_KEY,
                                       SHEET_CLIENT_EMAIL, SHEET_CLIENT_ID, SHEET_CLIENT_X509_CERT_URL)
credential_file.write(formatted_text)

変更前の↑の箇所を、以下のように一行ずつ読み込み・書き込みするように処理変更。

変更後

google_spread_sheet_test.py

# template.txtと環境変数から取得した各種データを統合してcredential.jsonに書き込む
template_file_name = 'template.txt'
template_file = open(template_file_name)
template_file_lines = template_file.readlines()
line_num = 0
for line in template_file_lines:
    line_num += 1
    if line_num == 3:
        line = line.format(SHEET_PROJECT_ID)
    if line_num == 4:
        line = line.format(SHEET_PRIVATE_KEY_ID)
    if line_num == 5:
        line = line.format(SHEET_PRIVATE_KEY)
    if line_num == 6:
        line = line.format(SHEET_CLIENT_EMAIL)
    if line_num == 7:
        line = line.format(SHEET_CLIENT_ID)
    if line_num == 11:
        line = line.format(SHEET_CLIENT_X509_CERT_URL)
    credential_file.writelines(line)

これで再度実行してみます。

$ forego run python google_spread_sheet_test.py

チワッス！

今度はうまくいったっぽい！

f:id:virtual-surfer:20180619220002p:plain

シートを確認してみると、期待通り書き込みができています！

これで、あとはHerokuの環境変数（Config Vars）に値を設定しておけば、Heorku経由でもGoogle Spread Sheetにアクセスできます！やったね😆

では！

仮想サーファーの日常

プログラミング・エンジニアのスキルアップ・ブログ運営などに関してよく書く雑記ブログ

PythonのHerokuアプリケーションからGoogle Spread Sheetの読み書き

普通にファイルからreadしてwriteする → うまくいかず。

readLinesで一行ずつ読み込んでwriteLineで一行ずつ書き込み → うまくいった！

変更前

変更後