どうも、nippa です。
python で便利な操作を勉強中です。
本日は、python で Google スプレッドシート(SpeadSheets)から情報を取得してみたいと思います。
Google の公式のクイックスタートを参考にして、python で GoogleSheetAPI を利用してみたいと思います。
GoogleSheetsAPI は Google アカウントがあれば無料で使えます。
GoogleSheetsAPI の詳細については、こちらを参考にしてください。
環境
Google Sheets API を有効化
新しくプロジェクトを作成
既存のプロジェクトを利用する場合は、作成不要です。
複数 Google アカウントログインしている場合は、ユーザを選んでから行なってください。
Cloud Console のダッシュボードにアクセスします。
プロジェクトを選択 -> 「新しいプロジェクトの作成」 -> プロジェクトを作成
Google Sheet API を有効化
利用するプロジェクトを選択後に、
で無効化することもできます。
認証情報の作成と保存
有効可したあとに、認証情報を作成します。
認証情報には 2 種類あります。
- OAuth クライアント ID: ユーザが Google スプレッドシートにアクセスする認証情報
- サービスアカウント: サービス、アプリ用の認証情報
必要に応じて、使い分けてください。
「認証情報」 -> 「認証情報を作成」 -> 認証情報を選択して作成
作成した認証情報を保存します。
この認証情報を使って python で google スプレッドシートにアクセスします。
認証情報は、重要な情報なので取り扱いに注意してください。
Git でサーバ上にアップロードなどしないように。
必要なライブラリのインストール
pip でインストールする場合は、以下です。
pip install google-api-python-client google-auth-httplib2 google-auth-oauthlib
conda でインストールする場合は、以下です。
conda install google-api-python-client google-auth-httplib2 google-auth-oauthlib
Python で Google スプレッドシートのセルの情報を取得
Google のクイックスタートのサンプルコード（読み取りのみ）
Python の Google Sheets API のサンプルコードは以下から取得できます。
Github python-samples/sheets/quickstart/quickstart.py
内容は以下の通りです。
from __future__ import print_function import pickle import os.path from googleapiclient.discovery import build from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request # If modifying these scopes, delete the file token.pickle. SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'] # The ID and range of a sample spreadsheet. SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms' SAMPLE_RANGE_NAME = 'Class Data!A2:E' def main(): """Shows basic usage of the Sheets API. Prints values from a sample spreadsheet. """ creds = None # The file token.pickle stores the user's access and refresh tokens, and is # created automatically when the authorization flow completes for the first # time. if os.path.exists('token.pickle'): with open('token.pickle', 'rb') as token: creds = pickle.load(token) # If there are no (valid) credentials available, let the user log in. if not creds or not creds.valid: if creds and creds.expired and creds.refresh_token: creds.refresh(Request()) else: flow = InstalledAppFlow.from_client_secrets_file( 'credentials.json', SCOPES) creds = flow.run_local_server(port=0) # Save the credentials for the next run with open('token.pickle', 'wb') as token: pickle.dump(creds, token) service = build('sheets', 'v4', credentials=creds) # Call the Sheets API sheet = service.spreadsheets() result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID, range=SAMPLE_RANGE_NAME).execute() values = result.get('values', []) if not values: print('No data found.') else: print('Name, Major:') for row in values: # Print columns A and E, which correspond to indices 0 and 4. print('%s, %s' % (row[0], row[4])) if __name__ == '__main__': main()
前項で作成した認証情報と quickstart.py を同じフォルダに保存し、実行します。
python quickstart.py
#実行結果
Name, Major:
Alexandra, English
Andrew, Math
Anna, English
Becky, Art
Benjamin, English
Carl, Art
Carrie, English
Dorothy, Math
Dylan, Math
Edward, English
Ellen, Physics
Fiona, Art
John, Physics
Jonathan, Math
Joseph, English
Josephine, Math
Karen, English
Kevin, Physics
Lisa, Art
Mary, Physics
Maureen, Physics
Nick, Art
Olivia, Physics
Pamela, Math
Patrick, Art
Robert, English
Sean, Physics
Stacy, Math
Thomas, Art
Will, Math
初回の場合、認証を要求される場合があります。
python コードの中では、認証情報を使って、Token を作成してます。
2 回目からは認証情報ではなく、Token を利用してアクセスしています。
Google のサンプルのスプレッドシートにアクセスして情報を取得して、その情報が表示されます。
スプレッドシート全体の情報の取得
python からスプレッドシート全体の情報の取得する場合、以下のスクリプトになります（スプレッドシート、シート情報）。
こちらには、セルの情報は含まれません。
spreadsheet_idでスプレッドシートの ID を指定して下さい。
import json import pickle import os.path from google.auth.transport.requests import Request from google_auth_oauthlib.flow import InstalledAppFlow from google.auth.transport.requests import Request from googleapiclient.discovery import build from googleapiclient.errors import HttpError as googleHttpError scopes = ['https://www.googleapis.com/auth/spreadsheets.readonly'] spreadsheet_id = 'XXXXXXXXXXXXXXXX' creds = None if os.path.exists('token.pickle'): with open('token.pickle', 'rb') as token: creds = pickle.load(token) if not creds or not creds.valid: if creds and creds.expired and creds.refresh_token: creds.refresh(Request()) else: flow = InstalledAppFlow.from_client_secrets_file( 'credentials.json', scopes) creds = flow.run_local_server(port=0) with open('token.pickle', 'wb') as token: pickle.dump(creds, token) service = build('sheets', 'v4', credentials=creds) sheet = service.spreadsheets() try: result = sheet.get(spreadsheetId=spreadsheet_id).execute() except googleHttpError as e: print(e) print(json.dumps(result, indent=2))
スプレッドシートの情報が表示されます。
スプレッドシートの ID とアクセス権限について
サンプルコード quickstart.py では
SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
でスプレッドシートの ID を指定しています。
ID は スプレッドシートの URL を見ていただて、確認することもできます。
https://docs.google.com/spreadsheets/d/[スプレッドシートのID]/edit#gid=0
また、スプレッドシートの全情報を取得すれば、確認することが可能です。
quickstart.py では読み込み専用になっていますので、
# 変更前 SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly'] # 変更後 SCOPES = ['https://www.googleapis.com/auth/spreadsheets']
のように変更が必要になります。
感想
今回、python で Google スプレッドシート の情報を取得についてまとめてみました。
GoogleSheetAPI を利用すれば、スプレッドシートへの書き込みやシートの削除や編集、グラフの作成なども行うことができます。
python でスプレッドシートにレポートを自動生成することもできます。
少しずつ慣れて、ルーティン的な作業は自動化していきたいですね。
スプレッドシートの操作や書き込みについてもまとめていきます。
ではでは、また次回。
