gspreadを使ってスプレッドシートに表を作ってみた

この記事は

Python(gspread)でGoogleスプレッドシートに表を作成する機会があったので、その時の手順を振り返ってみます。

目指すのは

こんな感じ

• GoogleDriveの任意フォルダにスプレッドシートを新規作成
• 作成したスプレッドシートに表を作成
• 「算数」と「国語」の合計を数式を使って「合計」に表示
• 「合否」にはドロップダウンリストを設定(gspread-formattingを使用)
• 諸々の書式設定
• スプレッドシートを他の人と共有

用意するもの

Google Cloudアカウント

Google Cloud設定

gspread公式の認証ページを参考にGoogle Cloudの設定をしていきます。

1. 適当なプロジェクトを作成
2. 「Google Drive API」と「Google Sheets API」を有効にします。
3. 今回は
4. 今回は[OAuthクライアントIDによる認証](https://docs.gspread.org/en/latest/oauth2.html#for-end-users-using-oauth-client-id)を使用します。

手順に従い、ダウンロードしたJSONファイルを~/.config/gspread/credentials.jsonとして保存すればOKです。

動作確認

---

認証が問題なく通るかテストします。

マイドライブ配下にtest-spreadsheetフォルダを作成し、そこにexampleというスプレッドシートを作成しておきます。

exampleのA1には確認用に取得する値を入れておきます。

requirements.txt

gspread
gspread-formatting

example.py

import gspread

gc = gspread.oauth()

folder_id = 'xxxxxxxxxxxxxxxx'

sh = gc.open('example', folder_id)

print(sh.sheet1.get('A1'))

gspread.oauth()を呼び出し認証を行います。
folder_idには作成したフォルダ(test-spreadsheet)のIDを設定します。実際にフォルダに移動してURLを見ると分かります。
gc.open()で指定したフォルダのexampleという名前のシートを開きます。
sh.sheet1.get()で最初のワークシートの指定したセルの値を取得します。

example.pyを実行するとブラウザで認証を求められると思います。
これを許可してコンソールにA1の値が表示されれば確認OKです。

$ python example.py
[['hello!']]

スプレッドシート作成＆編集

では、目標である表を作成していきます。

任意のフォルダに新規スプレッドシートを作成する

---

create.py

import gspread

folder_id = 'xxxxxxxxxxxxxxxx'
file_name = 'test_gspread'
share_email = ['otto@example.com', 'doe@example.com']

OAuthのクレデンシャルを使用して認証
gc = gspread.oauth()

# スプレッドシートの新規作成
sh = gc.create(file_name, folder_id)

# シート共有設定
for email in share_email:
    sh.share(email, perm_type='user', role='writer')

gc.create():指定したフォルダに任意の名前(今回はtest_gspread)のシートを作成します
sh.share():作成したシートをメールアドレスを使って他の人と共有します（権限は編集者にしています）
共有された相手にはメールで通知がいきます。

作成したシートに表を作成する

---

• 数式の設定
  

# 計算元データ
worksheet.update([[1, 1]], 'A1:B1')

# 数式設定 第3引数は値を文字として扱うかどうか
worksheet.update([['=A1+B1']], 'C1', raw=False)

セルの更新はupdate()を使用します。
第一引数に更新する値、第二引数に更新するセルの範囲を設定します。
数式の場合、そのままだと入力した値は解析されず、文字列として扱われてしまうため、第三引数としてraw=Falseを設定します。

---

• セルの書式設定

基本的な書式設定方法についてです。

# 色定義
RGB_COLOR_LIGHT_BLUE = {"red": 0.678, "green": 0.847, "blue": 0.902}
# 枠線定義
BORDER_STYLE_SOLID = {"style":"SOLID"}
BORDER_STYLE_DOUBlE = {"style":"DOUBLE"}
BORDER_FOR_COLUMNS = {"top": BORDER_STYLE_SOLID, "bottom": BORDER_STYLE_DOUBlE, "left": BORDER_STYLE_SOLID, "right": BORDER_STYLE_SOLID}
# 書式設定
worksheet.format("A1:F1", {
    "backgroundColorStyle": {
        "rgbColor": RGB_COLOR_LIGHT_BLUE
    },
    "horizontalAlignment": "CENTER",
    "borders": BORDER_FOR_DATA,
    "textFormat": {
    "fontSize": 12,
    }
})

背景色、文字色、文字配置、枠線などセルの書式設定はformat()で設定できます。
第一引数に書式設定を行うセルの範囲を指定します。
色はRGBカラーモデルでの指定するので使用する色は事前に定義しておくと良いと思います。
枠線も事前定義しておきます。スタイル定義はGoogle公式ページで確認できます。

---

• ドロップダウンリストの設定

ドロップダウンリストの設定はformat()では行えないようなので、gspread公式ページに記載されていたgspread-formattingを使用しました。

from gspread_formatting import *

# ドロップダウンリスト用ルール定義
validation_rule = DataValidationRule(
    BooleanCondition('ONE_OF_LIST', ['合格', '不合格', '保留']),
    showCustomUi=True
)
# ドロップダウンリスト設定
set_data_validation_for_cell_range(worksheet, 'A1', validation_rule)

DataValidationRule()でセルの入力規則を定義し、
set_data_validation_for_cell_range()で定義した入力規則を適用するセルを指定します。

入力規則の詳細についてはGoogle公式ページを参考にしてください。

---

以下完成したコードです。

edit.py

import gspread
from gspread_formatting import *

folder_id = 'xxxxxxxxxxxxxxxx'
file_name = 'test_gspread'

# カラム名
column_name = ['名前', '算数', '国語', '合計', '合否', '備考']
# テストデータ
test_data = [['hoge1', 80, 60], ['hoge2', 70, 60]]

# format定義
RGB_COLOR_LIGHT_BLUE = {"red": 0.678, "green": 0.847, "blue": 0.902}
RGB_COLOR_WHITE = {"red": 1.0, "green": 1.0, "blue": 1.0}
BORDER_STYLE_SOLID = {"style":"SOLID"}
BORDER_STYLE_DOUBlE = {"style":"DOUBLE"}
BORDER_FOR_COLUMNS = {"top": BORDER_STYLE_SOLID, "bottom": BORDER_STYLE_DOUBlE, "left": BORDER_STYLE_SOLID, "right": BORDER_STYLE_SOLID}
BORDER_FOR_DATA = {"top": BORDER_STYLE_SOLID, "bottom": BORDER_STYLE_SOLID, "left": BORDER_STYLE_SOLID, "right": BORDER_STYLE_SOLID}

# OAuthクライアントIDを使用して認証
gc = gspread.oauth()

# スプレッドシートを開く
sh = gc.open(file_name, folder_id)

# 表題
# セルをマージ
worksheet.merge_cells('A1:F1')
worksheet.update([['テスト結果']], 'A1')
worksheet.format("A1:F1", {
    "backgroundColorStyle": {
        "rgbColor": RGB_COLOR_LIGHT_BLUE
    },
    "horizontalAlignment": "CENTER",
    "borders": BORDER_FOR_DATA,
    "textFormat": {
    "fontSize": 12,
    }
})

# 表のカラム部分書き込み
worksheet.update([column_name], 'A2')
# カラム部分書式設定
worksheet.format("A2:F2", {
    "backgroundColorStyle": {
        "rgbColor": RGB_COLOR_LIGHT_BLUE
    },
    "horizontalAlignment": "CENTER",
    "borders": BORDER_FOR_COLUMNS,
    "textFormat": {
        "fontSize": 12,
    "bold": True
    }
})

DROPDOWN_DICT = {1:'合格', 2:'不合格', 3:'保留'}
# リストに設定した値を持つドロップダウンリストをセルに設定
validation_rule = DataValidationRule(
    # BooleanCondition('ONE_OF_LIST', ['合格', '不合格', '保留']),
    BooleanCondition('ONE_OF_LIST', list(DROPDOWN_DICT.values())),
    showCustomUi=True
)

# テストデータ領域書き込み
START_ROW = 3
for i, data in enumerate(test_data):
    # テストデータを1行ずつ書き込み
    worksheet.update([data], f'A{i+START_ROW}')
    # 数式設定 第3引数は値を文字として扱うかどうか
    worksheet.update([[f'=B{i+START_ROW}+C{i+START_ROW}']], f'D{i+START_ROW}', raw=False)
    # ドロップダウンリスト設定
    set_data_validation_for_cell_range(worksheet, f'E{i+START_ROW}', validation_rule)
    # 書式設定
    worksheet.format(f"A{i+START_ROW}:F{i+START_ROW}", {
        "backgroundColorStyle": {
            "rgbColor": RGB_COLOR_WHITE
        },
        "horizontalAlignment": "CENTER",
        "borders": BORDER_FOR_DATA,
    })
    # ドロップダウンリスト初期値設定（保留）
    worksheet.update([[DROPDOWN_DICT[3]]], f'E{i+START_ROW}')

おわりに

公式のAPIリファレンスで大体のことは分かると思いますが、ちょっと複雑なことをしようとすると、初見だと中々把握しづらかったので誰かのお役に立てればうれしいです。

あとAPIのオプションの理解などはGoogle公式の開発者ドキュメントが役に立ちました。（こっちが基本）

参考資料

gspread公式
gspread-formatting公式
Google開発者ドキュメント