[Python]gspread-formattingで行の固定＋一行おきに色付け

目的

自動でスプレッドシートを見易くするためにgspreadを用いて行の固定と色付けをしたい。

サンプルシート

こちらのシートを使用。

コード

import gspread
from gspread_formatting import *
from oauth2client.service_account import ServiceAccountCredentials
from pydrive.auth import GoogleAuth
from pydrive.drive import GoogleDrive
import os

# 数値からアルファベットを求める
def num2alpha(num):
    if num<=26:
        return chr(64+num)
    elif num%26==0:
        return num2alpha(num//26-1)+chr(90)
    else:
        return num2alpha(num//26)+chr(64+num%26)

"""
スプレッドシート内フォーマット設定
"""
# ヘッダー行
header_fmt = cellFormat(
    backgroundColor=color(1, 0.7, 0.3),
    textFormat=textFormat(bold=True, foregroundColor=color(0, 0, 0)),
    horizontalAlignment='CENTER'
)
# データ行
data_fmt = cellFormat(
    backgroundColor=color(1, 0.9, 0.7)
)
"""
スプレッドシート編集
"""
# 自身が格納されているパス
abs_path = f'{os.path.dirname(os.path.abspath(__file__))}\\'
# GoogleAPI
scope = ['https://spreadsheets.google.com/feeds',
         'https://www.googleapis.com/auth/drive']
# 認証用キー
json_keyfile_path = f'{abs_path}credentials.json'
# サービスアカウントキーを読み込む
credentials = ServiceAccountCredentials.from_json_keyfile_name(json_keyfile_path, scope)
# pydrive用にOAuth認証を行う
gauth = GoogleAuth()
gauth.credentials = credentials
drive = GoogleDrive(gauth)
# スプレッドシート格納フォルダ
folder_id = 'フォルダID'
# スプレッドシート格納フォルダ内のファイル一覧取得
file_list = drive.ListFile({'q': "'%s' in parents and trashed=false" % folder_id}).GetList()
# ファイル一覧からファイル名のみを抽出
title_list = [file['title'] for file in file_list]
# gspread用に認証
gc = gspread.authorize(credentials)
# スプレッドシートID
sheet_id = [file['id'] for file in file_list if file['title'] == 'test']
sheet_id = sheet_id[0]
# ワークブックを開く
workbook = gc.open_by_key(sheet_id)
# ワークシートを開く
worksheet = workbook.worksheet('シート1')
# 入力されている最終列を取得
last_column_num = len(list(worksheet.row_values(1)))
# 最終列を数値→アルファベットへ変換
last_column_alp = num2alpha(last_column_num)
# フィルタをかける
worksheet.set_basic_filter(name=(f'A:{last_column_alp}'))
# 一行目を固定する
worksheet.freeze(rows=1)
# ヘッダー行の色付け＋太字＋中央揃え
format_cell_range(worksheet, f'A1:{last_column_alp}1', header_fmt)
# 入力されているデータの最終行取得
last_row_num = len(list(worksheet.col_values(1)))
# データ行を一行おきに色付け
for row in range(2, last_row_num + 1, 2):
    format_cell_range(worksheet, f'A{row}:{last_column_alp}{row}', data_fmt)

実行結果

コードの説明

順を追って説明します。

フォーマット設定

"""
スプレッドシート内フォーマット設定
"""
# ヘッダー行
header_fmt = cellFormat(
    backgroundColor=color(1, 0.7, 0.3),
    textFormat=textFormat(bold=True, foregroundColor=color(0, 0, 0)),
    horizontalAlignment='CENTER'
)
# データ行
data_fmt = cellFormat(
    backgroundColor=color(1, 0.9, 0.7)
)

フォーマット設定部分。

backgroundColorで色を設定。
（R,G,Bを0～1の数値で任意に設定する）
textFormatで太文字、文字色を設定。
horizontalAlignmentで文字位置を設定。

参考リンクは下記。
PythonでGoogleSpreadSheetのセルにコメントを付加したり色や枠線をつける

Googleドライブへアクセス～フィルタをかける

筆者が投稿した過去記事を参照してください。
[Python]gspreadでスプレッドシートにフィルタをかける

一行目を固定

# 一行目を固定する
worksheet.freeze(rows=1)

rowsで任意の行数を指定してあげれば好きな箇所で固定出来ます。

ヘッダー行の色付け＋太字＋中央揃え

# ヘッダー行の色付け＋太字＋中央揃え
format_cell_range(worksheet, f'A1:{last_column_alp}1', header_fmt)

引数は（編集したいワークシート、セルの範囲（A1～C1）、フォーマット）です。

データ行を一行おきに色付け

# 入力されているデータの最終行取得
last_row_num = len(list(worksheet.col_values(1)))
# データ行を一行おきに色付け
for row in range(2, last_row_num + 1, 2):
    format_cell_range(worksheet, f'A{row}:{last_column_alp}{row}', data_fmt)

データ入力行の最終行を取得
↓
二行目から一行おきに最終行までループで色付け

という処理の流れ。

これで一行おきに色付けが出来ました。

リクエスト制限を考慮する

先程のサンプルでは行数が少ないので上記コードで問題ないですが、行数が増えるとスプレッドシートのリクエスト制限に引っ掛かってエラーで落ちます。
（100 秒あたりのリクエスト数100件等の制限があります）

この制限に引っ掛からないように改修します。

改修前のコードはこのように一行ずつ選択→色付け→一行選択...という処理。

改修後はこのように一括選択→色付けという処理に変更。
これでリクエスト回数を減らすというわけです。

コード

#リストへデータ部分の色付け範囲とフォーマットをタプル型で格納
ranges = [(f'A{row}:{last_column_alp}{row}', data_fmt) for row in range(2, last_row_num + 1, 2)]
print(ranges)
#作成したリストの先頭にヘッダー部分のフォーマットを格納
ranges.insert(0, (f'A1:{last_column_alp}1', header_fmt))
print(ranges)
#一括で色付け
format_cell_ranges(worksheet, ranges)

実行結果

[('A2:C2', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>), ('A4:C4', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>)]
[('A1:C1', <CellFormat backgroundColor=(red=1;green=0.7;blue=0.3);horizontalAlignment=CENTER;textFormat=(foregroundColor=(red=0;green=0;blue=0);bold=True)>), ('A2:C2', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>), ('A4:C4', <CellFormat backgroundColor=(red=1;green=0.9;blue=0.7)>)]

リストへ色付けしたい範囲を全て格納
↓
一括で色付け

という処理です。
（実行結果部分でrangesの中身を表示させているので参考にして下さい）

先程と違ってformat_cell_rangesというモジュールを使用しています。

公式サンプル

fmt = cellFormat(
    backgroundColor=color(1, 0.9, 0.9),
    textFormat=textFormat(bold=True, foregroundColor=color(1, 0, 1)),
    horizontalAlignment='CENTER'
    )

fmt2 = cellFormat(
    backgroundColor=color(0.9, 0.9, 0.9),
    horizontalAlignment='RIGHT'
    )

format_cell_ranges(worksheet, [('A1:J1', fmt), ('K1:K200', fmt2)])

このようにリスト内にタプルで範囲とフォーマットを格納することによって一括処理が出来るようになっています。

これで制限に引っ掛かる事無く行固定＋一行おきに色付けが可能！

公式リンク

gspread-formatting 0.3.0