PythonとSheets API v4でGoogleスプレッドシートを読み書きする

PythonからSheets APIを使ってGoogleスプレッドシートを操作してみました。Pythonのクイックスタートの内容を中心に紹介します。

Google Sheets API を使うための準備

まずはじめにAPIを使うためにいくつかの準備を行います。

Google API コンソールでSheets APIを有効化

Google API コンソールでSheets APIを有効化します。こちらのページにアクセスして、任意のプロジェクトを選択するとSheets APIを有効化することができます。

認証情報を作成

OAuth同意画面でサービス名を適当に設定します。その他の項目は記載しなくても大丈夫です。

次にOAuth クライアント IDの認証キーを作成します。APIキーではなくOAuth クライアント IDであることに注意します。アプリケーションの種類は「その他」を選びます。

認証キーが作成されたら、⬇を押してJSONをダウンロードします。ダウンロードしたファイルはclient_secret.jsonにリネームして、スクリプトを実行するフォルダに格納しておきます。

Pythonパッケージのインストール

pipでgoogle-api-python-clientをインストールします。

pip install --upgrade google-api-python-client

APIを使ったGoogleスプレッドシートの読み書き

OAuth認証

APIを使うときは、先程作成した認証キーを使って、認証を行う必要があります。クイックスタートにあるコードですが、get_credentials()関数を使うことで認証ができます。

実行するとブラウザが起動し、認証画面に飛ぶので、許可ボタンを押します。一度認証が成功すると、~/.credentialsに認証情報が保存されます。

from __future__ import print_function
import httplib2
import os

from apiclient import discovery
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage

try:
    import argparse
    flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args()
except ImportError:
    flags = None

SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly'
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'Google Sheets API Python Quickstart'


def get_credentials():
    home_dir = os.path.expanduser('~')
    credential_dir = os.path.join(home_dir, '.credentials')
    if not os.path.exists(credential_dir):
        os.makedirs(credential_dir)
    credential_path = os.path.join(credential_dir,
                                   'sheets.googleapis.com-python-quickstart.json')

    store = Storage(credential_path)
    credentials = store.get()
    if not credentials or credentials.invalid:
        flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
        flow.user_agent = APPLICATION_NAME
        if flags:
            credentials = tools.run_flow(flow, store, flags)
        else:
            credentials = tools.run(flow, store)
        print('Storing credentials to ' + credential_path)
    return credentials

スプレッドシートからデータを読む

get_credentials()関数で認証情報を取得してserviceオブジェクトを作成します。おまじないみたいなものです。

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

データの取得はspreadsheets.values.getメソッドを使うことでできます。spreadsheetIdとrangeが必須のパラメータです。

spreadsheetIdは、スプレッドシートのURLに記載されているIDになります。https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upmsの1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upmsの部分です。ちなみにこのIDのスプレッドシートはGoogleが用意しているサンプルです。

rangeは読み込む範囲です。Excelと同じ表記です。シート名を指定しない場合（例えばA2:Eのように）は最初のシートになります。

spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
rangeName = 'Class Data!A2:E'
result = service.spreadsheets().values().get(
         spreadsheetId=spreadsheetId, range=rangeName).execute()
values = result.get('values', [])

また、ドキュメントのページは、ブラウザ上でAPIを試すこともできます。

スプレッドシートにデータを書き込む

データの書き込みは、spreadsheets.values.updateメソッドでできますが、OAuth認証でのコードではSCOPESをhttps://www.googleapis.com/auth/spreadsheets.readonlyとreadonlyにしているため、書き込むことができません。

なので、SCOPESをhttps://www.googleapis.com/auth/spreadsheetsに変更します。変更したらもう一度認証画面に飛ぶように、古い認証情報が格納されている~/.credentialsを削除しておきます。

なお、必要なスコープは、メソッドのドキュメントのAuthorizationの項に書いてあります。

必須パラメータは、同じくspreadsheetIdとrangeですが、ValueInputOptionをUSER_ENTEREDに指定しておくと、Googleスプレッドシートに入力したのと同じように文字列が日付に変換されたりします。

rangeName = 'A1:C1'
ValueInputOption = 'USER_ENTERED'
body = {
    'values': [1, 2, 3]],
}
result = service.spreadsheets().values().update(
    spreadsheetId=spreadsheetId, range=rangeName,
    valueInputOption=ValueInputOption, body=body).execute()

spreadsheets.values.updateメソッドは上書きですが、spreadsheets.values.appendメソッドを使うと一番下の行に追記できます。使い方はまったく同じです。

rangeName = 'A:C'
ValueInputOption = 'USER_ENTERED'
body = {
    'values': [1, 2, 3]],
}
result = service.spreadsheets().values().append(
    spreadsheetId=spreadsheetId, range=rangeName,
    valueInputOption=ValueInputOption, body=body).execute()

このコードの場合、1行目になにか書かれていると、2行目に追記する形になります。

さいごに

PythonとSheets APIを使ってGoogleスプレッドシートの読み書きを操作してみました。認証まわりが少し面倒ですが、一度設定してしまえば、getメソッドやupdateメソッドを使って読み書きすることはとても簡単でした。