gspreadでGoogleスプレッドシートを更新する際にセルの表示形式をきれいにしたい

概要

gspreadでスプレッドシートに値を張り付けるときに、表示形式も意識すると幸せになるので、その方法をまとめました。

結論

• ファイルから読み込んだものを張り付ける場合は値を適切に変換する（変換にやや考慮点がある）
• update_cells のオプションにUSER_ENTEREDを入れる

貼り付ける値の例

例えば以下を張り付けていくとします。
実際にはファイルだったり、BigQueryだったりAthenaだったりから読み込むと思います。

ケース1

特に何も考えず、縦に張り付けていきます。

case1.py

# 前略
wkb = gc.open_by_key("xxxxxxxxxxx")
wks = wkb.worksheet("data1")
wks.clear()

cell_list = wks.range('A1:A10')

vlist=[]
vlist.append("12039")
vlist.append("1111222233334444")
vlist.append("3.141592")
vlist.append("2021-01-14")


idx=0
for val in vlist :
    cell_list[idx].value = val
    idx+=1

wks.update_cells(cell_list)

ケース1の結果

一瞬、ん？いいんじゃない？と思いますが、すべて文字列なので計算などが行えません。
しかも=sum()を文字列に実行すると0になるようで、想像しただけで恐ろしいことになりそうです。

ケース2

gspreadはpythonの型を適切に変換して渡せば、セルもよい感じにしてくれるようです。
なので

case2.py

#前略
idx=0
for val in vlist :
    try :
        val = int(val)
    except :
        pass

    cell_list[idx].value = val
    idx+=1

wks.update_cells(cell_list)

としてみます。

ケース2の結果

やったか！？

はい、以下がダメです。

• 長い数値が壊れる
• 少数が文字列のまま

これらも考慮していきます。

ケース3

ロジックの組み方はほかにもあると思いますが、変換の順序だったりを考慮する必要があり、少し煩雑にはなるかと思います。今回は

• ドットが含まれていたらfloatへ変換
• 一定文字数以下の場合にintへ変換

といった判定をしています。

case3.py

#前略
vlist=[]
vlist.append("12039")
vlist.append("1111222233334444")
vlist.append("3.141592")
vlist.append("2021-01-14")
vlist.append("2021-01-15")

idx=0
for val in vlist :
    try :
        if len(val) <= 10:
            val = int(val)
    except :
        pass
    try :
        if "." in val:
            val = float(val)
    except :
        pass
    cell_list[idx].value = val
    idx+=1

wks.update_cells(cell_list)

ケース3の結果

良い感じです。
日付があるので、最新データを別シートに表示したいな、、、と=max(

ああっ
日付型が文字列のままになってしまいました。

ケース4

よしじゃあ日付型に変換して渡す、、、と途中でJSONを介すためか、エラーになってしまいます。
これは、文字列のままにして update_cellsのオプションとして USER_ENTERED を渡すと解決します。

case4.py

#前略
idx=0
for val in vlist :
    try :
        if len(val) <= 10:
            val = int(val)
    except :
        pass

    try :
        if "." in val:
            val = float(val)
    except :
        pass
    cell_list[idx].value = val
    idx+=1

wks.update_cells(cell_list,value_input_option="USER_ENTERED")

ケース4の結果

これでようやく良い感じになりました！

雑感

貼れるか貼れないかではなく「貼れたけどなんかおかしい」となってしまうのが厄介ですね。

なお適切に型が設定されたBigQueryのテーブルに、ライブラリで取得したデータをそのまま上記のようなことをする場合は、変換は不要となります。
が、ファイルを読み込んで、、、というのもRPAとしては結構あるケースなのではと思いまとめてみました。