PythonでGoogleSpreadSheetのセルにコメントを付加したり色や枠線をつける

はじめに

PythonからGoogleSpreadSheetを操作するには gspread というのが便利です。
また、セルに色を付けたり枠線を付けたりするのに、 gspread-formatting というのが更にあって、これも便利です。

ただ、gspread-formatting でも セルにコメントを付けることが標準だとできないんですが（たぶん）、色々調べているうちにやる方法がわかったのでご紹介します。

今回使った各Version

• Python3.7
• gspread==3.1.0
• gspread-formatting==0.0.6

色・枠線・コメント を付ける方法

色や枠線は、gspread-formatting の標準機能でできます。
色は公式サイトのサンプルに載っていますが、枠線はちょっと調べないとわからないので、ついでに紹介しておきます。

色の付け方

公式サンプルより

from gspread_formatting import *

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

format_cell_range(worksheet, 'A1:J1', fmt)

変数 worksheet は gspreadの Worksheet Objectです。
Colorの引数の範囲は 0~1 ですね。

枠線の付け方

枠線は、セルごと、「上」「下」「左」「右」ごと、に色や線種を指定することが可能っぽいです。
イメージとしては、

• 色や線種を Border というObjectで表現して、
• Borders(top, bottom, left, right) の引数としてBorderを渡し、
• CellFormat(borders=...) でセルごとにBordersを指定する

です。こんな感じになります。

b = Border("DOTTED", Color(0, 0, 0, 0))
fmt = CellFormat(borders=Borders(top=b))
format_cell_range(worksheet, 'A1:J1', fmt)

セルコメントの付け方

さて、ここからが本題ですがコメントの付け方です。

SpreadsheetAPI ドキュメント の RepeatCellRequest の cell の型を見てみると CellData となっていて、この CellData は note という属性を持っています。

なので、とにかく最終的にコメントを付けたい文字列をそこに与えてあげればいいわけです。

ということで、例えば以下のように使えるようにするために、

format_cell_ranges_with_note(worksheet, [("B2", CellFormatWithNote(note="Hello Note!!"))])

以下のようなコードを書いておけばOKです。

from gspread_formatting import _range_to_gridrange_object, CellFormat

def format_cell_ranges_with_note(worksheet, ranges):
    """Update a list of Cell object ranges of :class:`Cell` objects
    in the given ``Worksheet`` to have the accompanying ``CellFormat``.

    :param worksheet: The ``Worksheet`` object.
    :param ranges: An iterable whose elements are pairs of:
        a string with range value in A1 notation, e.g. 'A1:A5',
        and a ``CellFormat`` object).
    """

    body = {
        'requests': [
            _build_repeat_cell_request_with_note(worksheet, range, cell_format)
            for range, cell_format in ranges
        ]
    }
    return worksheet.spreadsheet.batch_update(body)


def _build_repeat_cell_request_with_note(worksheet, range, cell_format):
    from gspread_formatting import _range_to_gridrange_object

    # https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets/cells#CellData
    cell_data = {
        'userEnteredFormat': cell_format.to_props()
    }
    fields = cell_format.affected_fields('userEnteredFormat')

    if getattr(cell_format, "note") is not None:
        cell_data['note'] = str(getattr(cell_format, "note"))
        fields = fields + ["note"]

    return {
        'repeatCell': {
            'range': _range_to_gridrange_object(range, worksheet.id),
            'cell': cell_data,
            'fields': ",".join(fields),
        }
    }


class CellFormatWithNote(CellFormat):
    _FIELDS = CellFormat._FIELDS

    def __init__(self,
        numberFormat=None,
        backgroundColor=None,
        borders=None,
        padding=None,
        horizontalAlignment=None,
        verticalAlignment=None,
        wrapStrategy=None,
        textDirection=None,
        textFormat=None,
        hyperlinkDisplayType=None,
        textRotation=None,
        note=None):
        super().__init__(
            numberFormat=numberFormat,
            backgroundColor=backgroundColor,
            borders=borders,
            padding=padding,
            horizontalAlignment=horizontalAlignment,
            verticalAlignment=verticalAlignment,
            wrapStrategy=wrapStrategy,
            textDirection=textDirection,
            textFormat=textFormat,
            hyperlinkDisplayType=hyperlinkDisplayType,
            textRotation=textRotation,
        )
        self.note = note

さいごに

これでSpreadsheet芸の幅がひとつ増えました。