Google ColaboratoryでGoogleスプレッドシートを読み書きしてみる

Google ColaboratoryでGoogleスプレッドシートを読み書きしてみる

前回のエントリでは、GoogleColabで画像表示ができるようになりました。これでOpenCVを使った画像処理も安心して勉強ができるようになりました。

uepon.hatenadiary.com

では、そのほかのファイル、特にWebスクレイピングをした結果をGoogleスプレッドシートに格納といった用途はあるかなと思います。 具体的にはセルから値を取り出したり、格納したりとなりますが、それができるかを確認してみようと思います。

f:id:ueponx:20180408110634p:plain

必要になるパッケージ

GoogleColabというかpythonGoogleスプレッドシートのファイルを扱うには‘‘‘gspread‘‘‘というモジュールを用いると便利です。

github.com

特徴としては…

  • Google Sheets API v4.
  • Open a spreadsheet by its title or url.
  • Extract range, entire row or column values.
  • Python 3 support.

python3に対応しているので助かります。また、スプレッドシートを開く際にはファイル名(タイトル)かURLが指定できるので、共有設定の入っているようなGoogle Drive上のスプレッドシートに関してもデータの操作が行えるのが便利です。

gspreadパッケージのインストール

以下のコマンドでインストールすることができます。

!pip install gspread

既にインストール済みであれば-Uつけてインストールしますが今のところ不要の様です。

f:id:ueponx:20180406161141p:plain

このように表示されればインストールは完了です。

gspreadパッケージを使用することで以前のエントリーのような特別なGoogle Driveディレクトをマウントする作業なしで(というかこのパッケージとgoogle.colabパッケージ、oauth2clientパッケージを使用することで、直接Google Driveへアクセスする事ができます)

python側の標準的なファイルアクセス処理を行う場合にはマウントすることにもメリットはありますが、Googleスプレッドシートを単に読み込んだりするだけであればgspreadパッケージを使用するほうが楽かもしれません。(認証作業の回数も減りますし)

基本的な操作

基本的な操作としてはGithubのパッケージのドキュメントで抑えられると思いますが、念のため。

github.com

以下のテストの実行にあたっては、事前にマイドライブ上SpreadsheetSampleというスプレッドシートファイルを作成しておきます。存在しないとエラーが出ます!

Google Drive上は以下のような感じになっていて

f:id:ueponx:20180406162730p:plain

ファイルは存在していればよいので、ワークシートは空で大丈夫です。

f:id:ueponx:20180406183020p:plain

ではNotebookに以下のコードを張り付けて実行してみます。 内容としてはファイルをオープンして指定したセルに値を格納し、格納後さらに値を取得するという単純なものです。

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

# 認証処理
auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

# 'SpreadsheetSample'というスプレッドシートの先頭ワークシートをオープン
worksheet = gc.open('SpreadsheetSample').get_worksheet(0)

# A1セルに'foo'という値を上書き
worksheet.update_acell('A1', 'foo')

# A2からC3のセルエリアに'bar'を一括で上書き
cell_list = worksheet.range('A2:C3')
for cell in cell_list:
    cell.value = 'bar'
worksheet.update_cells(cell_list)

# A1セルの値を取得し、表示
val = worksheet.acell('A1').value
print(val)

# A1セルを0,0とするようなセル指定で
# 2,2(B2)の位置のセルを取得
val = worksheet.cell(2, 2).value
print(val)

このコードを【Shift】+【Enter】で実行します。

すると、認証処理が実行されます。(一度認証すればインスタンス実行中は認証処理は不要の様です)

f:id:ueponx:20180406183720j:plain

認証用のURLと認証キーのinputboxが表示されるので、URLのリンクをクリックします。 すると使用するDriveのアカウント選択に遷移します。

f:id:ueponx:20180406183813j:plain

使用するアカウントを選択すると、使用可能な機能の確認画面に遷移します。

f:id:ueponx:20180406183918j:plain

問題ないかを念のため確認して【許可】ボタンをクリックします。すると画面が遷移し、認証キーが発行されるのでこれを コピーして、Notebookのタブへ移動します。

f:id:ueponx:20180406184041j:plain

あとは認証キーをinputboxに張り付けて

f:id:ueponx:20180406184147j:plain

【Enter】キーを押せば認証が完了します。認証の完了後はPythonのコードが実行され結果表示に以下のような表示がされれば 正常に実行できました。

【実行結果】

foo
bar

f:id:ueponx:20180406163443p:plain

では、スプレッドシートの中身を確認してみます。

f:id:ueponx:20180406184524p:plain

A1のセルに'foo'が入っていて、A2-C2、A3-C3の領域に'bar'が格納されていれば正常に動作しています。 これでスプレッドシートの値の取得や格納もNotebookのコードから実行ができるようになりました。 意外と簡単にできました。

マイドライブ以外のディレクトリに存在する既存のスプレッドシートを開いてみる

先ほどはマイドライブ内にあるスプレッドシートのファイル名を指定して開きましたが、今度は任意のフォルダにあるスプレッドシートを開いてみます。 マイドライブの下にsheetディレクトリを作成し、その中にsampleというスプレッドシートファイルを作成します。

以下の画面のような状況になります。

f:id:ueponx:20180406193026p:plain

f:id:ueponx:20180406193114p:plain

ではこれを開いてみます。

エラーの例(読まなくても問題ないので飛ばしましょう)

単純に以下のようなコードでいいのかなと思うのですが…

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

# 認証処理
auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

# 'マイドライブ/sheet/sample'というスプレッドシートの先頭ワークシートをオープン
worksheet = gc.open('./sheet/sample').get_worksheet(0)```

ダメです。エラーがでます。

f:id:ueponx:20180406193657p:plain

エラーメッセージとしてはSpreadsheetNotFound:となっているのでパス指定に問題がありそうです。 何回か実験してみたのですが、python上でカレントディレクトリの変更をしたりしてみたのですが、パス指定を変えることが出来なさそうです。 Google Drive上のファイルは名前の方やパスを属性として扱い、基本的にはツリー構造ではなくフラットな構造になっているため、 基本はファイルの所在としてはマイドライブの下にあるのも、ある特定のディレクトリにあることも大きな差がないということの様です。

うまくいった例

前のエラーを踏まえて、ではどうするか?

ファイルをオープンする方法としてgspreadモジュールではkeyをしてする方法とURLから指定する方法があるようです。 ドキュメントには以下のような記述があります。

# If you want to be specific, use a key (which can be extracted from
# the spreadsheet's url)
sht1 = gc.open_by_key('0BmgG6nO_6dprdS1MN3d3MkdPa142WFRrdnRRUWl1UFE')

# Or, if you feel really lazy to extract that key, paste the entire url
sht2 = gc.open_by_url('https://docs.google.com/spreadsheet/ccc?key=0Bm...FE&hl')

Google Driveに格納されるファイルには固有のキーがあるのでそれを使用するようです。URLも基本的には固有キーを使用したURLになっているので同じような感じです。 つまりキーがわかっていれば問題ありません。

ファイルの固有キーをつかって任意のフォルダのスプレッドシートへアクセス

スプレッドシートをブラウザで開いて

f:id:ueponx:20180406201013p:plain

ブラウザのURL表示の中の https://docs.google.com/spreadsheets/d/14TU4**********************************************Hxt1Y/edit#gid=0(自分のファイルのものを使用してください)

/d/のあとから次の/までの間が固有キーになります。それを使用して以下のようなスクリプトを実行します。

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

# 認証処理
auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

# 'マイドライブ/sheet/sample'というスプレッドシートをオープン
sh = gc.open_by_key('14TU4**********************************************Hxt1Y')
worksheet = sh.get_worksheet(0)

# set value
worksheet.update_acell('A1', 'key')

f:id:ueponx:20180406201547p:plain

スプレッドシートにも反映されています。

f:id:ueponx:20180406201640p:plain

URLをつかって任意のフォルダのスプレッドシートへアクセス

スプレッドシートをブラウザで開いて

f:id:ueponx:20180406194727p:plain

ブラウザのURLボックスの中身がそのままURLになります。(URLの末尾に/edit#gid=0がついています。本来はないものが正しいURLですが、末尾に編集情報がついていても問題はないようです)

f:id:ueponx:20180406201055p:plain

このURLをつかってスプレッドシートへアクセスします。 以下のコードを実行します。

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

# 認証処理
auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

# 'マイドライブ/sheet/sample'というスプレッドシートをオープン
sh = gc.open_by_url('https://docs.google.com/spreadsheets/d/14TU4**********************************************Hxt1Y/edit#gid=0')
worksheet = sh.get_worksheet(0)

# set value
worksheet.update_acell('A1', 'URL')

実行結果・編集したスプレッドシートがこのように変化すれば正常に実行されています。

f:id:ueponx:20180406201917p:plain

f:id:ueponx:20180406202020p:plain

オープン処理はできました

これで既存にあるスプレッドシートのファイルに関してはおおよそ処理できるようになりました。 これでもいいのですが…新規にファイルを作成することだってありますよね?

スプレッドシートの作成

先ほどの例ではあらかじめあるスプレッドシートを読み込んでいましたが、今度は新規に作成してみようと思います。

新規にスプレッドシートを作成してみる

マニュアルをみると以下のような記述で大丈夫のようです。

sh = gc.create('test_sheet')

では新規作成してみましょう。新規作成するスプレッドシートの名前は'test_sheet'にしてみます。

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

# 認証処理
auth.authenticate_user()
gc = gspread.authorize(GoogleCredentials.get_application_default())

# 'SpreadsheetSample'というスプレッドシートの先頭ワークシートをオープン
worksheet = gc.create('test_sheet').get_worksheet(0)

# A1セルに'foo'という値を上書き
worksheet.update_acell('A1', 'Create')

# A1セルの値を取得し、表示
val = worksheet.acell('A1').value
print(val)

このコードを実行すると以下のようになります。

f:id:ueponx:20180406203817p:plain

作成されたのはマイドライブの直下に作成されています。

f:id:ueponx:20180406204016p:plain

スプレッドシートのセルも期待通り編集されています。

f:id:ueponx:20180406204119p:plain

これでめでたしめでたし…となるはずなんですが、やっぱりなんとなくしっくりきません。 時系列のデータをファイルを作りつつ保存するような処理をするほうが比較的一般的ではないかと思います。

と入っても、gspreadモジュールでできるのは以下の2つになります。

  • マイドライブ直下へのファイル作成(open()、open_by_url()、open_by_key())
  • マイドライブ・任意のディレクトリのスプレッドシートへのアクセス処理(create())

どうやらgspreadモジュールだけでは難しいようです。

終わりに

長くなったのでこのあとは別のエントリーにしようと思います。 次回はディレクトリを指定してスプレッドシートの読み書きを作成するにはという感じになると思います。 (途中まで書いていてあまりにも長く感じたので分けました)

これまで、あんまりネットワーク上のストレージを使ったプログラムを使ったことがなかったので結構戸惑ったような感じでしたが、 こういう考え方や扱いがむしろ普通になってきているんしょうね。

【関連エントリ】

uepon.hatenadiary.com

uepon.hatenadiary.com

uepon.hatenadiary.com

/* -----codeの行番号----- */