pythonからスプレッドシートを触るとき、gspread/gspread_dataframeなどのライブラリを利用することができます。

これらライブラリについて内部実装を見ながら、どのような使い方をするかまとめました。

前提・方針

スプレッドシートの操作はサービスアカウントを利用する
- Cloud FunctionsやCloud Runからスプレッドシートを自動更新できるようにするため
- ローカル環境では、サービスアカウントにimpersonateする
  - セキュリティの観点から、サービスアカウントの秘密鍵をローカルにダウンロードしないようにするため
なるべく、スプレッドシートへのアクセスは最小限にする
- Sheets APIではユーザごとに60リクエスト/分、プロジェクトごとに300リクエスト/分に制限されているため¹

本書では書かないこと

以下の内容については、他の技術記事等で掲載されているため記載を省略いたします。

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)

gspread.APIError: スプレッドシートが存在しない場合、またはSheets APIに対し4xx, 5xx系のHTTPステータスが返ったとき

例外(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())

    def to_values(data: DataModel) -> list:
        # dataの属性分だけ増やす
        return [
            data.id,
            data.hoge,
        ]

参考1: 列の番号をアルファベット表記に置き換えたい

列はA~ZZZ列²までなので、以下で表現できます。

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 ↩