pythonからスプレッドシートを触るとき、gspread/gspread_dataframeなどのライブラリを利用することができます。
これらライブラリについて内部実装を見ながら、どのような使い方をするかまとめました。
前提・方針
本書では書かないこと
以下の内容については、他の技術記事等で掲載されているため記載を省略いたします。
- Google Cloud PlatformでのSheets API, IAM Service Account Credentials API有効化
- IAMについて、ローカルで実行するユーザに"サービス アカウント トークン作成者"ロールを追加
- 詳しくは、本ブログの記事"GAE/Pythonでサービスアカウントキーファイルを使わないようにした"をご覧ください。
- サービスアカウントのスプレッドシートへの共有権限付与
注記
説明のため、以下の略記を使います。
- pd: pandasモジュール
- df: pd.DataFrameインスタンス
また、gspreadの各関数は内部的にSheets APIを利用しています。そのため、リクエストやサーバの状況によっては4xx, 5xxエラーが返ることがあります。そのため、各関数を利用するときはgspread.exceptions.APIError例外をハンドルすることが推奨されます。
動作確認したバージョン
- gspread: 5.2.0
- gspread_dataframe: 3.2.2
スプレッドシート・ワークシートを開くための準備
クライアント取得
実装例
ローカル環境用のimpersonate済みcredentialsがあれば引数から利用します。
もしなければ(=GCP上で動作していれば)、ADC(Application Default Credentials)によりサービスアカウントの認証情報を得ます。
from typing import Optional from google.auth.credentials import Credentials from gspread import authorize from gspread.client import Client def get_client(credentials: Optional[Credentials]) -> Client: if credentials is None: credentials, _ = auth.default( scopes=['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']) return authorize(credentials)
スプレッドシートを開く
gspread.Clientの以下いずれかの関数を使います。
- open: 引数にスプレッドシート名(str)を指定する
- open_by_key: 引数にスプレッドシートのIDの(str)を指定する
- open_by_url: 引数にスプレッドシートのURL(str)を指定する
以下、例外の一覧です。open_by_key, open_by_urlについては公式リファレンスに書かれていない例外があるのでご注意ください(以後の関数について、gspread.APIErrorの記載は省略します)。
例外(open)
- gspread.SpreadsheetNotFound: スプレッドシートが存在しないときの例外
- gspread.APIError: またはSheets APIに対し4xx, 5xx系のHTTPステータスが返ったとき
例外(open_by_key)
例外(open_by_url)
- gspread.NoValidUrlKeyFound: URLが不正の場合
- gspread.APIError: スプレッドシートが存在しない場合、またはSheets APIに対し4xx, 5xx系のHTTPステータスが返ったとき
実装例
ここではopen_by_keyのみ記載します。
from gspread import Spreadsheet from gspread.exceptions import APIError from gspread.client import Client def open_spreadsheet(client: Client, spreadsheet_id: str) -> Spreadsheet | None: try: return client.open_by_key(spreadsheet_id) except APIError: # 厳密にエラーハンドリングする場合、APIErrorのcodeパラメータ(HTTPステータスに相当)を見てハンドリングしてください print("Spreadsheet (id: {spreadsheet_id}) not found or service not available!") return None
ワークシートを取得する(ワークシートの有無を確認する)
gspread.Spreadsheetのworksheet関数を使います。
引数:
- title(str): ワークシート名
例外
- gspread.Spreadsheet.WorksheetNotFound: ワークシートが存在しない場合
実装例
以下の例でワークシートを取得できます。また、ワークシートのインスタンスを取得できるかどうかでワークシートの有無を確認できます。
from typing import Optional from gspread import Spreadsheet, Worksheet, WorksheetNotFound def get_worksheet(book: Spreadsheet, sheet_name: str) -> Optional[Worksheet]: try: return book.worksheet(sheet_name) except WorksheetNotFound: return None
ワークシートの操作
シートの値全てを取得
gspread.Worksheetのget_values関数を使います。本関数はSheets APIのspreadsheets.values.getに相当します。なお、get_all_valuesはdeprecatedとなっているので、利用は推奨されません。
引数:
- range_name(str, optional): どの範囲を取得するか。"A1:B5"などの形式。
- major_dimension("ROWS"か"COLUMNS", optional): 行ごとに取得するか、列ごとに取得するか。デフォルトはROWS
- value_render_option("FORMATTED_VALUE"か"UNFORMATTED_VALUE"か"FORMULA", optional): 数式・フォーマット設定の有無。"FORMATTED_VALUE"は数式計算+フォーマットあり、"FORMULA"は数式計算のみ、"UNFORMATTED_VALUE"はどちらもなし。デフォルトはFORMATTED_VALUE?
実装例
from dataclasses import dataclass from enum import Enum from gspread import Worksheet class RowNo(Enum) ID = 0 HOGE = 1 @dataclass class DataModel id: str hoge: int def fetch_values(worksheet: Worksheet) -> list[DataModel]: rows: list = worksheet.get_values() return [DataModel( id=row[RowNo.ID], hoge=int(row[RowNo.ID], 10) if (rows[RowNo.ID].strip) > 0 else None, ) for row in rows[1:]] # 1行目はヘッダと仮定して飛ばす
シートの特定領域にまとめて値を書き込み
gspread.Worksheetのvalues_update関数を使います。本関数はSheets APIのspreadsheets.values.update APIに相当します。
引数
- range(str): 更新する範囲。"A1:B3"など
- body(list[list], optional): 書き込む2次元のリスト。同上APIのbodyに相当する
- params(dict, optional): updateオプション。同上APIのクエリパラメータに相当する
実装例
def values_update(spreadsheet_id: str, sheet_name: str, data: list[DataModel]): worksheet = get_worksheet(spreadsheet_id, sheet_name) if worksheet is None: return worksheet_values = ([self.to_values(d) for d in data]) column_alphabet = chr(len(worksheet_values[0]) + 64) # 列数は26までと仮定 worksheet_range = \ f"{sheet_name}!A2:{column_alphabet}{len(worksheet_values)+1}" try: worksheet.spreadsheet.values_update( range=worksheet_range, body=worksheet_values, params={ 'valueInputOption': 'USER_ENTERED' } ) except Exception: print(f"failed to write to worksheet {sheet_name}") print(traceback.format_exc()) @staticmethod def to_values(data: DataModel) -> list: # dataの属性分だけ増やす return [ data.id, data.hoge, ]
参考1: 列の番号をアルファベット表記に置き換えたい
列はA~ZZZ列2までなので、以下で表現できます。
ALPHABET_NUM = 26 def to_row_alphabet(row_no: int) -> str: def to_alphabet(no: int) -> str: if no < 0: return "" return chr((no % ALPHABET_NUM) + 1 + 64) n1 = row_no n2 = ((row_no - ALPHABET_NUM) // ALPHABET_NUM) n3 = ((row_no - ALPHABET_NUM ** 2 - ALPHABET_NUM) // (ALPHABET_NUM ** 2)) return f"{to_alphabet(n3)}{to_alphabet(n2)}{to_alphabet(n1)}".strip(' ')
参考2: pandas.DataFrameを使ってシートを読み書きしたい
gspread_dataframeライブラリを使用します。このライブラリはgspreadライブラリをwrapした関数群を提供します。
参考ページ: pandas.Dataframeからスプレッドシートに簡単に反映するgspread-dataframeライブラリ
読み込み、書き込みは以下のSheets APIを1度のみリクエストしています。
- 読み込み: spreadsheets.values.get
- 書き込み: spreadsheets.values.update
ただし、ワークシートを新規追加する場合はさらにリクエスト数が増えます。
読み込み - gspread_dataframe.get_as_dataframe
指定されたワークシートの値を全て取得した上で、オプションに指定された条件を満たすpd.DataFrameを返します。
from gspread_dataframe import get_as_dataframe df = get_as_dataframe(worksheet)
引数:
- worksheet(gspread.Spreadsheet.Worksheet): ワークシートのインスタンス
- evaluate_formulas(bool, optional): 数式を評価し、値として読み込むか?
- その他、pd.read_csvで使えるオプション(headers, usecols, skiprows, parse_datesなど。いずれもoptional)
書き込み - gspread_dataframe.set_with_dataframe
データフレームの内容を、指定されたワークシートに書き込みます。
from gspread_dataframe import set_with_dataframe set_with_dataframe(worksheet, df)
引数:
- worksheet(gspread.spreadsheet.Worksheet): ワークシートのインスタンス
- dataframe(pd.DataFrame): pandasデータフレーム
- row(int): どの列から書き込むか。デフォルトは1(最初の列)
- col(int): どの行から書き込むか。デフォルトは1(最初の行)
- include_index(bool): indexも書き込むか。デフォルトはFalse
- include_column_header(bool): 列名も書き込むか。デフォルトはTrue
- resize(bool): ワークシートのサイズをデータフレームにピッタリ合うように変形させるか。デフォルトはFalse。Falseの場合、ワークシートの方がサイズが小さいときのみ変形する。
- allow_formulas(bool): 数式をそのまま数式として書き込むか。デフォルトはTrue
- string_escaping(string | Callable[str, bool]): 文字列のエスケープ方法をどうするか。"default"なら"'"から始まる値のみ、"off"なら何もしない、"full"なら全ての値、CallableならTrueを返したセル値をエスケープする
-
Usage limits: https://developers.google.com/sheets/api/limits↩
-
Google ドライブに保管可能なファイル(スプレッドシートの節): https://support.google.com/drive/answer/37603?hl=ja↩