Google ColabからGoogle スプレッドシートを扱うときのサンプルコードまとめ

最近よくGoogle Colab上からGoogle スプレッドシートを触っています。

(このような感じの動画にもしているので、よろしければ見てみてください！→ 【サファのColabでPython学習 - 9】Google ColabからGoogleスプレッドシートに値を書き込む方法(YouTube))

触っていると、だいたいお決まりのコードを打ち込むケースが多いので、よく使う記述をサンプルコードとしてQiitaにまとめて置いておくことにしました。

参照しているドキュメント

なお、基本的にgspreadの公式ドキュメントを参照しながら書いていきます。
gspread(Docs)

gspreadのインストール

Google スプレッドシートを扱う上で必須となるライブラリの gspreadです。
Google Colab上ではコマンドを叩く際は、頭に!をつけて記述します。

!pip install gspread

と、ここまで書いていて気づいたのですが、Google Colabでは最初から gspreadがインストールされているようでした。
ちなみにインストールされているバージョンは 3.0.1。

import gspread
print(gspread.__version__)
# => 3.0.1

ここで少し気をつけておきたいことが、最初からインストールされているこの 3.0.1はバージョンとしては最新ではないため、結構使えない機能があったりします。
burnash/gspread(Releases)

そのため、もし最新のgspread の機能を使いたい場合は、下記のコマンドを叩いて、最新のgspreadをインストールしておく必要があります。

!pip install --upgrade gspread

import gspread
print(gspread.__version__)
# => 3.6.0

ただ、今回はGoogle ColabからすぐにGoogle スプレッドシートを扱うことを念頭に置いて書いていこうと考えたため、デフォルトでインストールされている 3.0.1 を使用して書いていこうと思います。
(最新バージョンで追加されている機能も魅力的ではあるため、そちらも別ポストとして書いていけたらなどと考えています。)

Googleスプレッドシートをコードから扱うために必要な認証処理

Google ColabからGoogleスプレッドシートを扱っていく上で必須の処理。
このコードを実行すると、GoogleスプレッドシートをGoogle Colab上から扱うために必要な認証が行なえます。
(ほぼほぼ おまじない のごとく、Googleスプレッドシートを扱うコードを書く際は使用します。書くのが面倒な方はこちらの記述をコピペでColab上に貼り付けて実行するのでもOKです)

from google.colab import auth
from oauth2client.client import GoogleCredentials
import gspread

auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

この認証について、Google ColabとGoogle スプレッドシートという、どちらもGoogleプロダクトだからなのかはちょっと分かりませんが、実にスマートに認証処理が行えるので、全く手間がかからないでいつも素晴らしいなと感じています。

具体的には下記のステップとなります。

上のコードを実行すると、認証のためのURLが表示されるのでクリックする

Google側の認証画面に飛ぶので、認証を行う。認証が完了すると、verification code が表示されるのでそれをコピー

さきほどコピーしたcodeをColab側に貼り付ければ、それで認証は完了

簡単で素晴らしいですね。

Google スプレッドシートの取得方法

操作を行う対象となるGoogleスプレッドシートの取得方法ですが、gspread側では幾つかのやり方を提供していますので、それぞれ紹介していきます。
なお、ここからは上に書いた認証は完了した前提でコードは書いていきます。
(認証されていない状態で実行すると、認証エラーになると思います)

ファイル名で取得

ファイル名で直接開く方法です。

ss_name = "スプレッドシートのファイル名"
workbook = gc.open(ss_name)

スプレッドシートのIDで取得

スプレッドシートのURL内に含まれるIDで開く方法。
(余談ですが、日本語の情報を調べた限り、このやり方で開く方法が一番多く見受けられた印象でした)

# https://docs.google.com/spreadsheets/d/{ここがスプレッドシートのIDとなる}/edit#gid=0
ss_id = "スプレッドシートのID"
workbook = gc.open_by_key(ss_id)

URLで取得

スプレッドシートのURLをそのまま入力して開く方法です

ss_url = "スプレッドシートのURL"
workbook = gc.open_by_url(ss_url)

すべてのスプレッドシートを取得

Google Drive内にあるすべてのスプレッドシートを取得する方法も用意されていました。
https://gspread.readthedocs.io/en/latest/api.html#gspread.Client.openall

実行すると、スプレッドシートのリストが返されます。

workbook_list = gc.openall()
for workbook in workbook_list:
  print(workbook.title) # 取得したファイルのタイトルを表示

シートの取得方法

上で取得したスプレッドシートに対して、下記の方法で特定のシートを取得することができます。

worksheet()に対して、取得対象のシート名を渡して取得する方法

ss_name = "スプレッドシートのファイル名"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("シート1")

get_worksheet()に対して、取得対象のシートのindexを渡して取得する方法

ss_name = "スプレッドシートのファイル名"
workbook = gc.open(ss_name)
worksheet = workbook.get_worksheet(0)

シート内の値の取得方法

セルを指定して取得する方法(acell)

ss_name = "スプレッドシートのファイル名"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("シート1")

print(worksheet.acell("A1").value)
# => A1のセルの値

print(worksheet.acell("B2").value)
# => B2のセルの値

print(worksheet.cell(3,2).value)
# => B3のセルの値

セルを指定して取得する方法(range)

ss_name = "スプレッドシートのファイル名"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("シート1")
print(worksheet.range(3,2)[0].value)
# => rangeの場合、セルの配列が返る
# => この指定の場合、B3のセル情報が一つだけ配列に入るため、このように[0]で指定して値を取得している

# 指定したい範囲のセル情報を取得。配列で変えるので、ループですべての値を出力
cell_list = worksheet.range("A1:B3")
for cell in cell_list:
  print(cell.value)

Cell Objectについて

ちなみにここで取得したセルについては下記の3つのpropertyを持っています。

• value (セルの値)
• row
• col

例えば下記は指定した範囲("A1:B3")のセルの値 を取得して表示するサンプルコードになります。

ss_name = "スプレッドシートのファイル名"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("シート1")

# 指定したい範囲のセル情報を取得。配列で変えるので、ループですべての値を出力
cell_list = worksheet.range("A1:B3")
for cell in cell_list:
  print('%s 行目の %s 列目の値は %s です' % (cell.row, cell.col, cell.value))

シート内の値の取得方法(全取得)

指定したシート内の値を取得する方法には下記のようなやり方があります。

get_all_recordsで、dict(辞書)のリストを返す

get_all_recordsを使用すると、対象のシートの値をdict(辞書)のリストとして返してくれます。

ss_name = "スプレッドシートファイル名"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("取得したいシート名")

print(worksheet.get_all_records())
# => [{'item1': 2, 'item2': 4, 'item3': 31}, {'item1': 3, 'item2': 6, 'item3': 32}, ...

何もオプションを指定せずに呼び出した場合、1行目をheaderとして扱います。
オプションの指定方法などはこちらを参照してみてください。

例えばシート内の値をjsonに変換したい場合は、下記のようなコードになると思います。

# 指定したシートの値jsonに変換する場合
import json
ss_name = "スプレッドシートのファイル名"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("対象のシート名")

dict_list = worksheet.get_all_records()
print(json.dumps(dict_list))

get_all_valuesでシート内の値を配列で返す

他にget_all_valuesを使用することで、単純に値の配列として取得する方法もあります。

ss_name = "スプレッドシートファイル名"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("取得したいシート名")

print(worksheet.get_all_values())
# => [['item1', 'item2', 'item3'], ['2', '4', '31'], ['3', '6', '32'], ...

シートに値を追加する方法

シートに値を行とともに追加していく場合は append_row を使用します。
下記のサンプルは指定したシートに対して、値とともに100行追加するサンプルコードとなります。
(例えば、すでにシート内に記述がある場合は、新たにそこから行が追加されていく形となります)

import random

ss_name = "スプレッドシートのファイル名"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("シート2")

for i in range(100):
  worksheet.append_row([i, i * 2, random.randint(1, 100)])

実際に入力されたスプレッドシートのキャプチャは下記のとおりです。

シートの中身を全削除したい場合(非実用的)

指定したシートの中身を全削除したい場合は下記のように実行する必要がありそうでした。

ただ、これは非常に微妙なコードなので(存在する行数分、一行ずつ削除している)、下に書いたシート自体を削除する処理で一気に消してしまったほうが良さそうです。

# 指定したシート内の値をすべて削除したい場合
ss_name = "スプレッドシートのファイル名"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("シート名")

for i in range(1, len(worksheet.get_all_values())):
  worksheet.delete_row(1)

なお、最新のgspreadを使う場合はdelete_rowsが用意されているため、そちらを使えば指定した行の範囲分、まとめて削除というのが実現可能そうです。

シート自体を削除したい場合

対象のシート自体を削除したい場合は、削除対象のシート自体を del_worksheet に渡します。

ss_name = "スプレッドシートファイル名"
workbook = gc.open(ss_name)
worksheet = workbook.worksheet("削除したいシート名")

workbook.del_worksheet(worksheet)

あとがき

これにて以上となります。

まだまだgspreadを試しきれていない部分も多いので、少しずつこちらの投稿に書き足していけたらと思います。